Категорная целостность или целостность сущностей

Категорная целостность означает, что каждый объект, определяемый строкой в таблице, должен быть отличим от любого другого объекта. Иными словами, должен быть такой набор атрибутов, уникальная комбинация значений которого позволит нам отличить один объект от другого. Такой набор атрибутов, однозначно идентифицирующий объект, называется потенциальным ключом. Он не может содержать NULL-значения, поскольку это не даст нам возможности идентифицировать объект (как, скажем, книгу с неизвестным названием).

Потенциальных ключей у таблицы может быть несколько. Например, человека можно идентифицировать по номеру паспорта, номеру страхового свидетельства, ИНН, номеру водительских прав и т.д.

Для обеспечения категорной целостности в языке SQL существуют спецификации PRIMARY KEY (первичный ключ) и UNIQUE (уникальный ключ). Первичный ключ может быть только один в таблице, уникальных же ключей может быть несколько. Т.е. у нас есть возможность для одного из потенциальных ключей задать спецификацию PRIMARY KEY, а для остальных – UNIQUE.

Что в нашем случае может послужить первичным ключом? Поскольку у одного производителя может быть много моделей, и он, соответственно, неоднократно может присутствовать в данных таблицы Product, то столбец maker не может являться кандидатом на роль первичного ключа. Аналогично свойством уникальности не обладает и атрибут type.

Уникальным же является номер модели. Это единственный кандидат на роль первичного ключа. Других потенциальных ключей в таблице нет. Чтобы доказать это, можно рассмотреть все остальные комбинации столбцов и показать, что они не гарантируют нам идентификации объекта. В частности, комбинация значений из трех столбцов в приведенном выше примере данных является уникальной, но, тем не менее, не идентифицирует модель 1232.

Давайте создадим первичный ключ. В языке SQL есть возможность изменить структуру существующей таблицы при помощи команды ALTER TABLE. Однако давайте будем дозировать новую информацию, поэтому сейчас будет проще пересоздать таблицу, т.е. удалить ее и создать заново с первичным ключом. Удалить таблицу просто (как говорится, ломать – не строить), для этого достаточно выполнить оператор DROP TABLE <имя таблицы>. Итак,

DROP TABLE Product;
create table Product (
    maker varchar(10),
    model varchar(50) PRIMARY KEY,
    type varchar(50)
);

Мы включили спецификацию первичного ключа в определение столбца. Но можно было это сделать отдельным ограничением:

CONSTRAINT <имя ограничения> PRIMARY KEY (<список столбцов, являющихся первичным ключом>)

При этом наш код будет выглядеть так (опять предварительно удаляем ранее созданную таблицу):

DROP TABLE Product;
create table Product (
    maker varchar(10),
    model varchar(50),
    type varchar(50),
    CONSTRAINT product_PK PRIMARY KEY (model)
);

Теперь сама СУБД будет следить за тем, чтобы значения первичного ключа не повторялись и не содержали NULL. Если мы выполним вставку при помощи ранее приведенного оператора INSERT, то получим такое сообщение об ошибке:

Violation of PRIMARY KEY constraint 'product_PK'. Cannot insert duplicate key in object 'Product'. The duplicate key value is (1232).

(Нарушение ограничения первичного ключа ‘product_PK’. Нельзя вставить дубликат ключа в объект ‘Product’. Дублирующееся значение ключа (1232).)

Если мы исправим ошибку ввода и укажем правильный номер модели принтера:

insert into product values
    ('A', '1232', 'PC'),
    ('A', '3001', 'Printer'),
    (NULL, NULL, NULL)
;

то получим другую ошибку, связанную с неопределенностью значения первичного ключа:

Cannot insert the value NULL into column 'model', table 'Product'; column does not allow nulls. INSERT fails.

(Невозможно вставить NULL в столбец ‘model’, table ‘Product’; столбец не допускает NULL-значения. INSERT не выполнен.)

Оператор

insert into product values
    ('A', '1232', 'PC'),
    ('A', '3001', 'Printer'),
    (NULL, '2000', NULL)
;

отработает без ошибок.

Как вы должно быть заметили, в сообщении об ошибке нарушения ограничения первичного ключа фигурирует имя ограничения (‘product_PK’). А что будет в случае, если имя не задано? Так было у нас, когда спецификация PRIMARY KEY была включена в определение столбца model. Кстати, во втором варианте мы тоже можем не указывать имя (тогда и ключевое слово CONSTRAINT также опускается):

DROP TABLE Product;
create table Product (
    maker varchar(10),
    model varchar(50),
    type varchar(50),
    PRIMARY KEY (model)
);

И так ли важно нам знать имя ограничения? Помимо того, что оно может помочь нам понять причину ошибки, имя требуется при удалении ограничения, когда мы меняем структуру существующей таблицы. Если мы не задаем имя ограничения, СУБД сама присвоит его. И это имя, которое должно быть уникальным в пределах базы данных, мы можем узнать из информационной схемы – стандартного представления метаданных. Например, чтобы узнать имя ограничения первичного ключа, созданного в последнем скрипте, можно выполнить обычный запрос на выборку из таблицы (представления) информационной схемы:

select CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where TABLE_NAME = 'Product' 
    and CONSTRAINT_TYPE ='primary key';

У меня это имя оказалось таким: PK__Product__0B7E269E30F848ED. У вас, вероятно, будет другим, поскольку его генерирует СУБД.

Примером составного ключа может послужить первичный ключ в таблице Outcomes (база данных «Корабли»). Здесь только пара {корабль, сражение} может быть уникальной, поскольку корабль может принять участие в нескольких сражениях, а в одном сражении участвует несколько кораблей. Корабль же в отдельной битве не может быть упомянут дважды, что и запретит сделать первичный ключ. Создать таблицу Outcomes с упомянутым первичным ключом можно следующим образом:

create table Outcomes (
    ship varchar(50),
    battle varchar(20),
    result varchar(10),
    primary key(ship, battle)
);

Следует заметить, что мы не можем написать так:

create table Outcomes (
    ship varchar(50) primary key,
    battle varchar(20) primary key,
    result varchar(10)
);

поскольку спецификация PRIMARY KEY может быть только одна.

Если первичный ключ может быть только один в таблице, то как быть в том случае, если в нашей модели должны быть уникальны разные комбинации атрибутов? Другими словами, как создать альтернативные ключи, например, если нам потребуется добавить уникальный столбец out_id в таблицу Outcomes?

Для этой цели в языке SQL имеется спецификация UNIQUE. Вот так мог бы выглядеть запрос на создание таблицы Outcomes с дополнительным столбцом out_id:

create table Outcomes (
    ship varchar(50),
    battle varchar(20),
    result varchar(10),
    out_id int,
    primary key(ship, battle),
    UNIQUE (out_id)
);

Как уже упоминалось выше, ограничений UNIQUE может быть создано несколько для одной таблицы.

Есть еще одно отличие этого ограничения от ограничения PRIMARY KEY. Столбец, на котором создано ограничение UNIQUE, может содержать NULL-значение, но только одно. Вы можете спросить, - как же в этом случае быть с идентификацией объекта? Вспомним пример с книгами, когда мы ищем книгу по уникальному названию. Пусть мы имеем ограничение UNIQUE на столбце названия книги. Поскольку NULL-значение может быть только одно, то найти книгу с неизвестным названием мы можем, исключив все книги с известными названиями.

Если же нам потребуется альтернативный ключ, не допускающий NULL-значений, то совместно с UNIQUE мы можем наложить ограничение NOT NULL. К этому мы сейчас и переходим.

#PRIMARY KEY #Категорная Целостность #UNIQUE #DROP TABLE #CONSTRAINT #INFORMATION_SCHEMA

Создание базовых таблиц Проверочные ограничения