Основы проектирования баз данных
Содержание:
Первичный и уникальный ключи
Скрыть рекламу в статье
Первичный и уникальный ключи
Первичные ключи являются одним из основных видов ограничений в базе данных. Они применяются для однозначной идентификации записей в таблице. Допустим, мы храним в базе данных список людей. Вполне вероятно, что могут появиться два (или больше) человека с одинаковыми фамилией, именем и отчеством Как же гарантированно отличить одного человека от другого (конечно. речь идет о том, чтобы отличить одного человека от другого на основании информации, хранящейся в базе данных)?
В данном случае «человек» представлен одной записью в таблице, поэтому можно задаться более общим вопросом — как отличить одну запись в (любой) таблице от другой записи в этой же таблице. Для этого используются ограничения — первичные кпочи. Первичный ключ представляет собой одно или несколько полей в таблице, сочетание которых уникально для каждой записи. Для одной таблицы не существует повторяющихся значений первичного ключа.
Уникальные кчочи несут аналогичную нагрузку — они также служат для однозначной идентификации записей в таблице. Отличие первичных ключей от уникальных состоит в том, что первичный ключ может быть в таблице только один, а уникатьных ключей — несколько. Надо отметить, что и первичный и уникальный ключ могут быть использованы в качестве ссылочной основы для внешних ключей (см. далее).
Синтаксис создания первичного и уникального ключа на основе единственного поля следующий:
<pkukconstraint> = {PRIMARY KEY |
UNIQUE}
Примеры первичных и уникальных ключей:
CREATE TABLE pkuk(
pk NUMERIC(15,0) NOT NULL PRIMARY KEY, /*первичный ключ*/
ukl VARCHAR(SO) NOT NULL UNIQUE,/*уникальный ключ */
uk2 INTEGER NOT NULL UNIQUE /* еще уникальный ключ */);
Синтаксис создания первичного и уникального ключей на основе нескольких полей:
<pkuktconstraint> = {PRIMARY KEY |
UNIQUE) ( col )
Такой синтаксис позволяет создавать ключи на основе комбинации полей. Вот примеры создания первичных и уникальных ключей из нескольких полей:
CREATE TABLE pkuk2(
Number1 INTEGER NOT NULL,
Namel VARCHAR(SO) NOT NULL,
Kol INTEGER NOT NULL,
Stoim NUMERIC(15,4) NOT NULL,
CONSTRAINT pkt PRIMARY KEY (Numberl, Namel), /*первичный ключ pkt на
основе двух полей*/
CONSTRAINT uktl UNIQUE (kol, Stoim) ); /*уникальный ключ uktl на основе
двух полей*/
Обратите внимание, что все поля, входящие в состав первичного и уникального ключей, должны быть объявлены как NOT NULL, так как эти ключи не могутиметь неопределенного значения. Помимо создания ограничения первичных и уникальных ключей в момент создания таблицы имеется возможность добавлять ограничения в уже существующую таблицу
Для этого используется предложение DDL: ALTER TABLE. Синтаксис добавтения ограничений первичного или уникального ключа в существующую таблицу аналогичен описанному выше:
Помимо создания ограничения первичных и уникальных ключей в момент создания таблицы имеется возможность добавлять ограничения в уже существующую таблицу. Для этого используется предложение DDL: ALTER TABLE. Синтаксис добавтения ограничений первичного или уникального ключа в существующую таблицу аналогичен описанному выше:
ALTER TABLE tablename
ADD {PRIMARY KEY | UNIQUE) ( col )
Давайте рассмотрим пример создания первичного и уникального ключа с помощью ALTER TABLE. Сначала создаем таблицу:
CREATE TABLE pkalter(
ID1 INTEGER NOT NULL,
ID2 INTEGER NOT NULL,
UID VARCHAR(24));
Затем добавляем ключи. Сначала первичный:
ALTER TABLE pkalter
ADD CONSTRAINT pkall PRIMARY KEY (idl, id2);
Затем уникальный ключ:
ALTER TABLE pkalter
ADD CONSTRAINT ukal UNIQUE (uid) ;
Важно отметить, что добавление (а также удаление) ограничений первичных и уникальных ключей к таблице может производить только владелец этой таблицы или системный администратор SYSDBA (подробнее о владельцах и пользователе SYSDBA см. главу «Безопасность в InterBase: пользователи, роли и права») (ч
4).
Оглавление книги
Create Primary Key (CREATE TABLE statement)
A primary key can be created when you execute a CREATE TABLE statement in SQL.
Syntax
The syntax to create a primary key using the CREATE TABLE statement in SQL is:
CREATE TABLE table_name ( column1 datatype , column2 datatype , ... CONSTRAINT constraint_name PRIMARY KEY (pk_col1, pk_col2, ... pk_col_n) );
OR
CREATE TABLE table_name ( column1 datatype CONSTRAINT constraint_name PRIMARY KEY, column2 datatype , ... );
- table_name
- The name of the table that you wish to create.
- column1, column2
- The columns that you wish to create in the table.
- constraint_name
- The name of the primary key.
- pk_col1, pk_col2, … pk_col_n
- The columns that make up the primary key.
Example
Let’s look at an example of how to create a primary key using the CREATE TABLE statement in SQL. We will start with a very simple one where our primary key consists of just one column.
For example:
CREATE TABLE suppliers ( supplier_id int NOT NULL, supplier_name char(50) NOT NULL, contact_name char(50), CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id) );
In this example, we’ve created a primary key on the suppliers table called suppliers_pk. It consists of only one column — the supplier_id column.
We could have used the alternate syntax and created this same primary key as follows:
CREATE TABLE suppliers ( supplier_id int CONSTRAINT suppliers_pk PRIMARY KEY, supplier_name char(50) NOT NULL, contact_name char(50) );
Both of these syntaxes are valid when creating a primary key with only one field.
If you create a primary key that is made up of 2 or more columns, you are limited to using only the first syntax where the primary key is defined at the end of the CREATE TABLE statement.
For example:
CREATE TABLE contacts ( last_name VARCHAR(30) NOT NULL, first_name VARCHAR(25) NOT NULL, birthday DATE, CONSTRAINT contacts_pk PRIMARY KEY (last_name, first_name) );
Create Primary Key (ALTER TABLE statement)
If your table already exists and you wish to add a primary key later, you can use the ALTER TABLE statement to create a primary key.
Syntax
The syntax to create a primary key using the ALTER TABLE statement in SQL is:
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);
- table_name
- The name of the table to modify. This is the table that you wish to add a primary key to.
- constraint_name
- The name of the primary key.
- column1, column2, … column_n
- The columns that make up the primary key.
Example
Let’s look at an example of how to create a primary key using the ALTER TABLE statement in SQL. So say, we already have a suppliers table created in our database. We could add a primary to the suppliers table with the following ALTER TABLE statement:
ALTER TABLE suppliers ADD CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id);
In this example, we’ve created a primary key on the existing suppliers table called suppliers_pk. It consists of the supplier_id column.
We could also create a primary key with more than one field as in the example below:
ALTER TABLE suppliers ADD CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id, supplier_name);
Пример
Предположим, что в базе данных имеется две таблицы: City (города) и Street (улицы), которые определяются следующим образом:
CREATE TABLE City ( id INTEGER NOT NULL PRIMARY KEY, name CHAR(40) ) CREATE TABLE Street ( id INTEGER NOT NULL PRIMARY KEY, name CHAR(40), id_city INTEGER NOT NULL FOREIGN KEY REFERENCES City(id) )
Содержимое этих таблиц следующее:
CITY
ID | NAME |
---|---|
1 | Москва |
2 | Санкт-Петербург |
3 | Владивосток |
STREET
ID | NAME | ID_CITY |
---|---|---|
181 | Малая Бронная | 1 |
182 | Тверской бульвар | 1 |
183 | Невский проспект | 2 |
184 | Пушкинская | 2 |
185 | Светланская | 3 |
186 | Пушкинская | 3 |
Таблица STREET имеет поле ID_CITY, которое является внешним ключом и ссылается на таблицу CITY. Значение в этом поле соответствует первичному ключу в таблице CITY для того города, где расположена улица. Так, Невский проспект имеет ID_CITY=2, что соответствует Санкт-Петербургу (ID=2 в таблице CITY).
В таблице STREET находятся две улицы с одинаковым названием Пушкинская, которые отличаются значением поля ID_CITY. Одна из них находится в Санкт-Петербурге (ID_CITY=2), другая — во Владивостоке (ID_CITY=3).
Попытка внести в таблицу STREET улицу «Дерибасовская» с ID_CITY=4 вызовет ошибку нарушения ссылочной целостности, поскольку в таблице CITY нет города с ID=4. Однако после внесения в таблицу CITY города «Одесса» с ID=4, повторное внесение улицы «Дерибасовская» с ID_CITY=4 пройдёт успешно.
При удалении из таблицы CITY города Владивостока результат зависит от свойств внешнего ключа:
- Если для внешнего ключа разрешено удаление по цепочке, то вместе с удалением Владивостока будут удалены улицы Светланская и Пушкинская с ID=3.
- Если для внешнего ключа запрещено удаление по цепочке, то операция вызовет ошибку нарушения ссылочной целостности, так как в таблице STREET будут находиться улицы с кодом ID_CITY=3, который отсутствует в таблице CITY.
При изменении в таблице CITY кода города Санкт-Петербурга с 2 на 48 результат зависит от свойств внешнего ключа:
- Если для внешнего ключа разрешено изменение по цепочке, то вместе с изменением кода Санкт-Петербурга будут изменены значения ID_CITY для соответствующих улиц.
- Если для внешнего ключа запрещено изменение по цепочке, то операция вызовет ошибку нарушения ссылочной целостности, так как в таблице STREET будут находиться улицы с кодом ID_CITY=2, который отсутствует в таблице CITY.
Ключи и ссылочная целостность в MySQL и Oracle
Oracle поддерживает первичные, уникальные, внешние ключи в полном объеме. Oracle поддерживает следующие правила ссылочной целостности:
- NO ACTION (устанавливается по умолчанию) в более жестком, чем по стандарту SQL 92, варианте: запрещается изменение и удаление строк родительской таблицы, для которых имеются связанные строки в дочерних таблицах.
- ON DELETE CASCADE.
Более сложные правила ссылочной целостности в Oracle можно реализовать через механизм триггеров.
MySQL версии 4.1 (последняя на момент написания статьи стабильная версия) позволяет в командах CREATE / ALTER TABLE задавать фразы REFERENCES / FOREIGN KEY, но в работе никак их не учитывает и реально внешние ключи не создает. Соответственно правила ссылочной целостности, реализуемые через внешние ключи, в MySQL не поддерживаются. И все заботы по обеспечению целостности и непротиворечивости информации в базе MySQL ложатся на плечи разработчиков клиентских приложений.
Резюме основных ключей
Ключи играют решающую роль в существовании схемы базы данных для установления связей между таблицами и внутри таблицы. Ключи устанавливают отношения и применяют различные типы целостности, особенно целостность на уровне таблицы и уровня отношений. Во-первых, они считают, что таблица содержит уникальные записи, а поля, которые вы используете для установления отношений между таблицами, должны содержать соответствующие значения. Первичный ключ и внешний ключ являются двумя наиболее важными и распространенными типами ключей, используемых в реляционных базах данных. Первичный ключ — это специальный ключ, используемый для уникальной идентификации записей в таблице, тогда как внешний ключ используется для установления отношения между двумя таблицами. Оба они идентичны по структуре, но играют разные роли в схеме реляционной базы данных.