Реализация ограничений, обеспечивающих целостность данных

Ограничение — это свойство, назначенное таблице или столбцу таблицы, которое запрещает ввод в указанный столбец (или столбцы) недопустимых значений. Например, ограничения UNIQUE или PRIMARY KEY предотвращают ввод значений, дублирующих уже существующие; ограничение CHECK запрещает ввод значения, не совпадающего с условием поиска; а ограничение FOREIGN KEY обеспечивает связь между данными двух таблиц. Обычно возможность ввода пустых значений и определение DEFAULT обсуждаются в разделах документации, посвященных ограничениям. В Transact-SQL определение DEFAULT в действительности является одним из типов ограничений. Однако, поскольку возможность ввода пустых значений и определение DEFAULT обсуждались в одном из предыдущих разделов, здесь мы уделим внимание только ограничениям PRIMARY KEY, UNIQUE, FOREIGN KEY и CHECK.

Ограничения позволяют определять способы, посредством которых SQL Server будет автоматически обеспечивать целостность базы данных. Ограничения определяют правила, имеющие отношение к допустимым значениям полей, и являются стандартным механизмом, обеспечивающим целостность.

Предпочтительней применять ограничения, чем триггеры, правила или умолчания.

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

Ограничения возможны как для столбцов, так и для таблиц:

• ограничение для столбца задается как часть определения столбца и применяется только к этому столбцу;

• ограничение для таблицы объявляется независимо от определения столбцов и применяется к нескольким столбцам таблицы.

Ограничения для таблиц следует использовать в тех случаях, когда ограничение действует для нескольких столбцов. Например, если первичный ключ таблицы состоит из двух и более столбцов, следует использовать ограничение для таблицы, в которое входят все столбцы первичного ключа. Рассмотрим таблицу, в которой регистрируются события, происходящие в компьютере. Допустим, что одновременно возможны события нескольких различных типов, но никакие два события, произошедшие одновременно, не могут относиться к одному и тому же типу. Чтобы реализовать это правило в таблице, надо включить в первичный ключ два столбца, один из которых хранит тип, а другой — время событий, как показано на примере следующего оператора CREATE TABLE:

CREATE TABLE Process

EventType INT,

EventTime DATETIME,

EventSite CHAR (50),

EventDesc CHAR (1024),

CONSTRAINT evenr_key PRIMARY KEY (EventType, EventTime)

)

GO

SQL Server поддерживает четыре главных класса ограничений: PRIMARY KEY, UNIQUE, FOREIGN KEY и CHECK.

Ограничения PRIMARY KEY

В таблице обычно есть столбец (или комбинация столбцов), значения которых уникально идентифицируют каждую строку таблицы. Этот столбец (или столбцы) называются первичным ключом таблицы и обеспечивает сущностную целостность таблицы. Можно создать первичный ключ, определив ограничение PRIMARY KEY при создании или изменении таблицы.

В таблице может быть только одно ограничение PRIMARY KEY, причем столбец, который участвует в ограничении, не должен допускать пустых значений. Поскольку ограничения PRIMARY KEY гарантируют уникальность данных, они часто определяются как столбцы с идентификатором. Когда в таблице определено ограничение PRIMARY KEY, SQL Server обеспечивает уникальность данных, создавая уникальный индекс для столбцов первичного ключа. Этот индекс также обеспечивает быстрый доступ к данным при использовании первичного ключа в запросах.

Если ограничение PRIMARY KEY определено для нескольких столбцов, то в одном столбце значения могут повторяться, но каждая комбинация значений всех столбцов, которые входят в определение ограничения PRIMARY KEY, должна быть уникальной.

Ограничение PRIMARY KEY создают одним из следующих способов:

• во время создания таблицы (в составе определения таблицы);

• добавляя ограничение к существующей таблице, если нет другого ограничения PRIMARY KEY.

Существующее ограничение PRIMARY KEY можно модифицировать или удалить.

Например, в ограничении PRIMARY KEY при желании можно изменить столбцы, на которые ссылается ограничение, порядок столбцов, имя индекса, признак кластеризации или коэффициент заполнения. Изменить длину столбца, для которого определено ограничение PRIMARY KEY, нельзя.

Чтобы модифицировать ограничение PRIMARY KEY с помощью TransactSQL, сначала следует удалить существующее определение ограничения PRIMARY KEY, а затем создать новое определение.

Следующий оператор CREATE TABLE создает таблицу Table1 и определяет в качестве ее первичного ключа столбец Col1:

CREATE TABLE Table1

(

Col1 INT PRIMARY KEY,

Col2 VARCHAR (30)

)

GO

To же самое ограничение определяют с помощью определения PRIMARY KEY на уровне таблицы:

CREATE TABLE Table1

(

Col1 INT,

Соl2 VARCHAR (30),

CONSTRAINT table_pk PRIMARY KEY (Col1)

)

GO

Еще один способ — добавить ограничение PRIMARY KEY к существующей таблице с помощью оператора ALTER TABLE:

ALTER TABLE Tablel

ADD CONSTRAINT table_pk PRIMARY KEY (Col1)

GO

Когда к сушествуюшему столбцу (или столбцам) таблицы добавляется ограничение PRIMARY KEY, SQL Server проверяет имеющиеся в столбцах данные, чтобы гарантировать их соответствие правилам, определенным для первичных ключей:

• отсутствие пустых значений;

• отсутствие повторяющихся значений.

Если ограничение PRIMARY KEY добавляется к столбцу, в котором есть повторяющиеся или пустые значения, SQL Server возвращает ошибку и не добавляет ограничение. Невозможно добавить ограничение PRIMARY KEY с нарушением вышеуказанных правил.

SQL Server автоматически создает уникальный индекс, чтобы обеспечить соответствие требованию уникальности, которое выдвигает ограничение PRIMARY KEY. Если в таблице еще нет кластерного индекса (или не задан явно некластерный индекс), тогда создается уникальный кластерный индекс, реализующий ограничение PRIMARY KEY.

Невозможно удалить ограничение PRIMARY KEY, если на него ссылается ограничение FOREIGN KEY из другой таблицы. Сначала необходимо удалить ограничение FOREIGN KEY.

Ограничения UNIQUE

Ограничение UNIQUE позволяет запретить ввод повторяющихся значений в некоторые столбцы, не участвующие в формировании первичного ключа. Несмотря на то, что уникальность обеспечивают оба ограничения — и UNIQUE, и PRIMARY KEY, в некоторых ситуациях вместо ограничения PRIMARY KEY следует использовать ограничение UNIQUE.

Так поступают тогда, когда:

• столбец (или комбинация столбцов) не является первичным ключом. Для таблицы можно определить несколько ограничений UNIQUE и лишь одно ограничение PRIMARY KEY;

• столбец допускает пустые значения. Ограничения UNIQUE разрешается определить для столбцов, допускающих пустые значения, тогда как ограничения PRIMARY KEY можно определить только для столбцов, не допускающих пустые значения.

Ограничение FOREIGN KEY также может ссылаться на ограничение UNIQUE.

Ограничение UNIQUE создается так же, как PRIMARY KEY:

• при создании таблицы в составе ее определения таблицы;

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

Для создания ограничения UNIQUE предназначены те же операторы Transact-SQL, что и для создания ограничения PRIMARY KEY. Нужно лишь заменить ключевые слова PRIMARY KEY словом UNIQUE. Как и в случае PRIMARY KEY, определение ограничения UNIQUE разрешается модифицировать или удалять.

Когда ограничение UNIQUE добавляется к существующему столбцу (или столбцам), в котором есть повторяющиеся или пустые значения, SQL Server проверяет (по умолчанию) имеющиеся в столбцах данные, чтобы гарантировать уникальность всех значений, кроме пустых. Если ограничение UNIQUE добавляется к столбцу, в котором есть повторяющиеся значения, SQL Server возвращает ошибку и не добавляет ограничение.

SQL Server автоматически создает индекс UNIQUE, чтобы обеспечить соответствие требованию уникальности, которое выдвигает ограничение UNIQUE. Поэтому при попытке вставить повторяющуюся строку SQL Server возвращает сообщение об ошибке, в котором говорится, что нарушено ограничение UNIQUE, и не добавляет строку в таблицу. Если отсутствует явно заданный кластерный индекс, по умолчанию создается уникальный некластерный индекс, реализующий ограничение UNIQUE.

Ограничения FOREIGN KEY

Внешний ключ — это столбец или комбинация столбцов, которая используется для установления и обеспечения связи между данными двух таблиц. Связь между двумя таблицами устанавливается путем добавления к одной из таблиц столбца (столбцов) и определения для него ограничения FOREIGN KEY. Этот столбец содержит значения первичного ключа из второй таблицы. В таблице может быть несколько ограничений FOREIGN KEY.

Например, у таблицы Persons из некоей базы данных есть связь с таблицей Countries, поскольку существует логическая связь между людьми и странами, в которых они проживают. Столбец Country_ID в таблице Persons соответствует столбцу с первичным ключом таблицы Countries. Следовательно, столбец Country_ID в таблице Persons — это внешний ключ для таблицы Countries.

Можно создать внешний ключ, определил ограничение FOREIGN KEY во время создания или изменения таблицы. В дополнение к PRIMARY KEY, ограничение FOREIGN KEY также может ссылаться на столбцы ограничения UNIQUE в других таблицах.

Ограничение FOREIGN KEY может содержать пустые значения. Однако если любой из столбцов составного ограничения FOREIGN KEY содержит пустые значения, проверка этого ограничения не выполняется.

Ограничение FOREIGN KEY может ссылаться на столбцы других таблиц этой базы данных или на другие столбцы этой же таблицы (таблицы, ссылающиеся сами на себя).

Хотя основное назначение ограничения FOREIGN KEY — контроль данных, которые могут быть сохранены в таблице с внешним ключом, оно также выявляет изменение данных в таблице с первичным ключом. Например, если из таблицы Countries удалить строку со сведениями о стране, в то время как ее идентификатор используется для людей из таблицы Persons, ссылочная целостность между этими двумя таблицами нарушится. Если можно так выразиться, люди, проживающие в удаленной стране, «зависнут» в таблице Persons без ссылки на данные таблицы Countries. Ограничение FOREIGN KEY не допускает подобной ситуации. Это ограничение обеспечивает ссылочную целостность, гарантируя, что в таблицу с первичным ключом невозможно внести такие изменения, которые сделают недействительной ссылку на данные таблицы с внешним ключом. Попытка удаления строки из таблицы с первичным ключом или изменения значения первичного ключа закончится неудачей, если удаляемое или изменяемое значение первичного ключа соответствует значению ограничения FOREIGN KEY другой таблицы.

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

Ограничение FOREIGN KEY создают одним из следующих методов:

• во время создания таблицы (в составе определения таблицы);

• добавлением к существующей таблице при условии, что ограничение FOREIGN KEY связано с существующим ограничением PRIMARY KEY или UNIQUE из другой (или этой же) таблицы.

Созданные ограничения FOREIGN KEY разрешается модифицировать и удалять. Например, при желании можно заменить столбцы, на которые ссылается ограничение FOREIGN KEY. Длину столбца, для которого определено ограничение FOREIGN KEY, изменить нельзя.

Чтобы модифицировать ограничение FOREIGN KEY c помощью Transact-SQL, сначала следует удалить существующее определение ограничения FOREIGN KEY, а затем создать новое определение.

Следующий оператор CREATE TABLE создает таблицу Table1 и определяет для столбца Со12 ограничение FOREIGN KEY, которое ссылается на столбец Employee_ID, являющийся в таблице Employees столбцом с первичным ключом:

CREATE TABLE Tablel

(

Col1 INT PRIMARY KEY,

Col2 INT REFERENCES Employees (Employee_ID)

)

GO

Это же ограничение можно создать, определив его на уровне таблицы:

CREATE TABLE Tablel

(

Col1 INT PRIMARY KEY,

Col2 INT,

CONSTRAINT col2_fk FOREIGN KEY (Col2)

REFERENCES Employees (Employee_ID)

)

GO

Ограничение FOREIGN KEY к существующей таблице добавляют с помощью оператора ALTER TABLE:

ALTER TABLE Table1

ADD CONSTRAINT col2_fk FOREIGN KEY (Col2)

REFERENCES Employees (Employee_ID)

GO

Когда ограничение FOREIGN KEY добавляется к существующему столбцу (или столбцам), SQL Server проверяет (по умолчанию) имеющиеся в столбцах данные, чтобы гарантировать, что все значения, кроме пустых, существуют в столбцах с ограничением PRIMARY KEY или UNIQUE, на которые ссылается FOREIGN KEY. Однако можно сделать так, чтобы SQL Server не сверял данные столбца с новым ограничением, и заставить SQL Server добавить новое ограничение независимо от данных столбца. Эта возможность полезна в тех случаях, когда существующие данные уже соответствуют новому ограничению FOREIGN KEY или когда правило бизнес-логики требует, чтобы ограничение действовало, начиная с этого момента.

Однако следует соблюдать осторожность при добавлении ограничения без проверки существующих данных, поскольку это действие совершается в обход контроля SQL Server, обеспечивающего целостность данных таблицы.

Следует отключать существующие ограничения при выполнении следующих действий:

• на время выполнения операторов INSERT и UPDATE, если известно, что новые данные нарушат эти ограничения, или если ограничения должны применяться только к тем данным, которые уже находятся в базе данных. Отключение этих ограничений позволяет модифицировать данные без проверки этими ограничениями;

• при репликации, если она относится к исходной базе данных. Во время репликации таблицы ее определение и данные копируются из исходной базы данных в целевую. Эти две базы данных обычно (хотя и не обязательно) располагаются на отдельных серверах. Если во время репликации не отключить ограничения FOREIGN KEY в исходной базе данных, возможно появление излишних запретов на ввод новых данных в целевую базу данных.

Ограничения CHECK

Ограничения CHECK, обеспечивают доменную целостность путем ограничения значений, которые разрешено ввести в столбец. Этим они похожи на ограничения FOREIGN KEY.

Отличие между ними заключается в способе определения допустимых значений. Ограничения FOREIGN KEY получают список допустимых значений из другой таблицы, а ограничения CHECK определяют их на основе логического выражения, в которое не входят данные из другого столбца. Например, можно ограничить диапазон значений столбца со сведениями о зарплате, создав ограничение CHECK, которое допускает только значения от 1500 до 10000. Эта функция предотвращает ввод значений, которые выходят за пределы нормального диапазона зарплаты.

Можно создать ограничение CHECK с помощью любого логического выражения, которое возвращает TRUE или FALSE в зависимости от используемого логического оператора. Вот как выглядит логическое выражение, допускающее только значения из интервала 1 500 - 10 000:

salary >= 1500 AND salary <= 10000

К одному столбцу разрешается применить несколько ограничений CHECK, которые проверяются в порядке их создания. Кроме того, можно применить одно ограничение CHECK к нескольким столбцам, если создать его на уровне таблицы. Например, средствами ограничения CHECK, определенного для нескольких столбцов, удается подтвердить, что в любой строке, где значение поля «страна» равно «USA», в поле «штат» находится значение, состоящее из двух символов. Эта функция позволяет проверить в одном месте несколько условий.

Ограничение CHECK создают одним из следующих способов:

• во время создания таблицы (в составе определения таблицы);

• добавляют к существующей таблице.

Созданные ограничения CHECK разрешено модифицировать и удалять. Например, можно модифицировать выражение, которое использовано в ограничении CHECK, определенном для столбца или таблицы.

Чтобы модифицировать ограничение CHECK с помощью Transact-SQL, сначала следует удалить существующее определение ограничения CHECK, а затем создать новое определение.

В этом примере создается таблица Table1 и определяется для столбца Соl2 ограничение CHECK, которое ограничивает вводимые значения диапазоном 0 — 100:

CREATE TABLE Table1

(

Col1 INT PRIMARY KEY,

Col2 INT

CONSTRAINT limit_anount CHECK (Col2 BETWEEN 0 AND 100),

Col3 VARCHAR(60)

)

GO

Это ограничение также можно определить на уровне таблицы:

CREATE TABLE Table1

(

Col1 INT PRIMARY KEY,

Соl2 INT,

Соl3 VARCHAR(60),

CONSTRAINT limit_amount CHECK (Col2 BETWEEN 0 AND 100)

)

GO

Чтобы добавить ограничение СНЕСК к существующей таблице, можно воспользоваться оператором ALTER TABLE:

ALTER TABLE Table1

ADD CONSTRAINT limit_amount CHECK (Col2 BETWEEN 0 AND 100)

GO

При добавлении к существующей таблице ограничение CHECK применяется только к новым данным или ко всем данным таблицы, включая существующие. По умолчанию ограничение CHECK применяется как к существующим, так и ко всем новым данным. Возможность применить ограничение только к новым данным полезна, если существующие данные уже соответствуют новому ограничению CHECK или если правило бизнес-логики требует, чтобы ограничение применялось, только начиная с этого момента.

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

Однако нужно быть осторожным при добавлении ограничения без проверки существующих данных, поскольку это действие совершается в обход контроля SQL Server, обеспечивающего целостность данных таблицы.

Следует отключать существующие ограничения CHECK при выполнении следующих действий:

• на время выполнения операторов INSERT и UPDATE, если известно, что новые данные нарушат это ограничение, или если ограничение следует применять только к тем данным, которые уже находятся в базе данных. Отключение этого ограничения позволит модифицировать данные без проверки этими ограничениями;

• на время репликации, если она относится к исходной базе данных. Во врем репликации таблицы ее определение и данные копируются из исходной базы данных в целевую. Эти две базы данных обычно (хотя и не обязательно) располагаются на отдельных серверах. Если во время репликации не отключить ограничения СНЕСК в исходной базе данных, возможно появление излишних запретов на ввод новых данных в целевую базу данных.

Дата добавления: 2014-01-11