Создание и использование умолчаний, ограничений и правил
Ограничения
Ограничения автоматически обеспечивают целостность данных. Ограничения задают правила, которые определяют значения данных, допустимые для какой-либо колонки. Они позволяют ограничивать значения данных, которые вводятся в колонку, чтобы в этой колонке не оказались неверные значения. Например, с помощью ограничения вы можете ограничить значения колонки целого типа диапазоном от 1 до 100. В результате любые значения вне этого диапазона нельзя будет ввести в данную колонку. (Для создания этого диапазона вы можете использовать ограничение CHECK, как будет показано ниже.) Ограничение только по одной колонке называется ограничением колонки ; оно ограничивает значения только этой колонки. Ограничение, которое влияет на несколько колонок, называется ограничением таблицы, или табличным ограничением; в этом случае комбинация значений для колонок, указанных в данном ограничении, должна отвечать требованиям этого ограничения. Имеется пять типов ограничений: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY и CHECK.
Создание и модифицирование ограничений с помощью T-SQL
В этом разделе вы узнаете об использовании каждого типа ограничений и реализации ограничений с помощью T-SQL. В следующем разделе вы узнаете, как реализовывать ограничения с помощью Enterprise Manager.
Ограничение NOT NULL
Ограничение NOT NULL является довольно простым, и мы уже видели, как оно использовалось в примерах этой лекции и предыдущих лекций. Ограничение NOT NULL задается в описании колонки, чтобы воспрепятствовать вставке null -значений в эту колонку (в противоположность ограничению NULL, которое разрешает присваивать null -значения). При использовании T-SQL вы можете указать NOT NULL при создании таблицы или позже, когда будете модифицировать какую-либо колонку. (О модифицировании таблиц базы данных см. "Создание таблиц баз данных" .) Вам следует использовать NOT NULL вместо NULL там, где это возможно, поскольку операции с null -значениями, такие как сравнения, сопряжены с большей дополнительной нагрузкой при обработке. Как уже говорилось выше в этой лекции, лучше использовать значение по умолчанию там, где это возможно, чем разрешать вставку null -значений.
Ограничение UNIQUE
Ограничение UNIQUE обеспечивает, что в колонке или наборе колонок не будут допускаться дублированные значения; иными словами, обеспечивается уникальность значений в этой колонке или наборе колонок. Для поддержки этой уникальности SQL Server создает по умолчанию уникальный некластеризованный индекс по колонке или колонкам, указанным в ограничении UNIQUE. Вы можете, однако, указывать, каким должен быть этот индекс – кластеризованным или некластеризованным. Напомним, что таблица может иметь только один кластеризованный индекс.
Примечание. Вы будете часто встречать в этой лекции термины "кластеризованный индекс" и "некластеризованный индекс". Не беспокойтесь, если вы не совсем понимаете, что означают эти термины, – в
"Создание и использование индексов"
дается подробное описание этих типов индексов.Ограничение UNIQUE можно использовать для любой колонки, которая не является частью ограничения PRIMARY KEY (описывается в следующем разделе), которое также обеспечивает уникальность значений. Ограничение UNIQUE можно использовать для колонок, в которых разрешены null -значения, в то время как ограничения PRIMARY KEY нельзя использовать для таких колонок. Null -значения не затрагиваются ограничениями UNIQUE. На колонку с ограничением UNIQUE может ссылаться ограничение FOREIGN KEY (см. раздел "Ограничение FOREIGN KEY" далее). По одной таблице можно задавать несколько ограничений UNIQUE, пока общее число индексов для этой таблицы не превышает 249 некластеризованных и одного кластеризованного индекса.
Чтобы создать ограничение UNIQUE по таблице с помощью T-SQL, используйте оператор CREATE TABLE или ALTER TABLE. Например, следующий оператор создает таблицу customer (покупатель) с ограничением UNIQUE по колонке SSN в виде кластеризованного индекса:
CREATE TABLE customer ( first_name char(20) NOT NULL, mid_init char(1) NULL, last_name char(20) NOT NULL, SSN char(11) NOT NULL UNIQUE CLUSTERED, cust_phone char(10) NULL ) GO
В этом операторе CREATE использовано ограничение по колонке. В следующем примере снова создается таблица, но на этот раз с добавлением табличного ограничения UNIQUE с именем UQ_full_name по колонкам first_name, mid_init и last_name:
CREATE TABLE customer
(
first_name char(20) NOT NULL,
mid_init char(1) NULL,
last_name char(20) NOT NULL,
SSN char(11) NOT NULL UNIQUE CLUSTERED,
cust_phone char(10) NULL,
CONSTRAINT UQ_full_name UNIQUE NONCLUSTERED (first_name,
mid_init, last_name)
)
GOТабличное ограничение UNIQUE (ограничение по более чем одной колонке) обеспечивает уникальность комбинаций значений по соответствующим колонкам. В данном случае в базу данных нельзя ввести двух покупателей, имеющих одинаковое сочетание имени (first_name), фамилии (last_name) и инициала отчества (mid_init). В одной или двух колонках может встретиться одинаковая комбинация, но не во всех трех колонках. Отметим, что в данном случае табличное ограничение UNIQUE является некластеризованным индексом, поскольку у нас уже имеется уникальный кластеризованный индекс по колонке SSN.
Чтобы добавить ограничение UNIQUE к существующей таблице, используйте оператор ALTER TABLE. Если вы пытаетесь модифицировать существующую колонку или колонки, добавляя ограничение UNIQUE, все существующие строки данной таблицы должны содержать уникальные значения или null -значения в этой колонке или колонках, иначе вы получите сообщение об ошибке и ограничение UNIQUE не будет добавлено. Предположим, что мы создали таблицу customer без каких-либо ограничений. Ниже приводятся два оператора для добавления ограничений по колонке и по таблице:
ALTER TABLE customer ADD CONSTRAINT UQ_ssn UNIQUE CLUSTERED (SSN) GO ALTER TABLE customer ADD CONSTRAINT UQ_full_name UNIQUE NONCLUSTERED (first_name, mid_init, last_name) GO
И снова повторим, что вы можете добавить ограничения к таблице только в том случае, если существующие данные уже отвечают требованиям ограничений.
Чтобы изменить существующее ограничение UNIQUE по какой-либо колонке или таблице с помощью T-SQL, вы должны сначала удалить соответствующее ограничение и затем снова создать его. Как и для умолчаний, вам следует использовать для ваших ограничений описательные имена, чтобы вы могли легко находить и удалять их без необходимости выяснения имен, присвоенных им системой SQL Server. При изменении ограничения с помощью Enterprise Manager система SQL Server автоматически удаляет старое ограничение и вновь создает его, когда вы сохраняете выполненные изменения (см. раздел "Создание и модифицирование ограничений с помощью Enterprise Manager" далее).
Ограничение PRIMARY KEY
Ограничение PRIMARY KEY используется, чтобы задать первичный ключ таблицы, представляемый колонкой или набором колонок, уникальным образом идентифицирующих строку таблицы. Поскольку первичный ключ идентифицирует строку, соответствующая колонка никогда не содержит значения NULL. В этом состоит отличие ограничения PRIMARY KEY от ограничения UNIQUE, которое допускает null-значения. Если вы определяете ограничение PRIMARY KEY по набору колонок, это ограничение указывает, что комбинация значений этих колонок должна быть уникальной для каждой строки, что аналогично ограничению UNIQUE по набору колонок. И, подобно ограничению UNIQUE, ограничение PRIMARY KEY не допускает дублированных значений. Если ограничение PRIMARY KEY присваивается колонке или набору колонок, то по этой колонке или колонкам первичного ключа автоматически создается уникальный индекс. Вы можете также задать для первичного ключа кластеризованный или некластеризованный индекс; если ничего не задано, по умолчанию создается кластеризованный индекс, если таблица еще не имеет кластеризованного индекса.
Таблица может иметь только одно ограничение PRIMARY KEY. Колонка с атрибутом IDENTITY хорошо подходит для первичного ключа, как и любая другая колонка или набор колонок, являющиеся уникальными для каждой строки. Например, в примере нашей таблицы customer мы могли бы создать колонку SSN как первичный ключ вместо создания для нее ограничения UNIQUE. Ограничение PRIMARY KEY не допускало бы null -значений и обеспечивало бы уникальность значений в колонке SSN, а по этой колонке первичного ключа был бы автоматически создан кластеризованный индекс. Следующий оператор T-SQL представляет один из способов задания колонки SSN как первичного ключа, когда вы определяете таблицу. При этом способе имя ограничению PRIMARY KEY присваивает SQL Server, поэтому он не является предпочтительным методом, так как в дальнейшем вам может потребоваться удаление данного ключа по имени.
CREATE TABLE customer ( first_name char(20) NOT NULL, mid_init char(1) NULL, last_name char(20) NOT NULL, SSN char(11) PRIMARY KEY, cust_phone char(10) NULL ) GO
Используя альтернативный способ, вы можете присвоить имя этому ограничению, добавив ключевое слово CONSTRAINT. Чтобы присвоить имя PK_SSN вашему ограничению PRIMARY KEY, используйте следующий оператор:
CREATE TABLE customer ( first_name char(20) NOT NULL, mid_init char(1) NULL, last_name char(20) NOT NULL, SSN char(11) CONSTRAINT PK_SSN PRIMARY KEY, cust_phone char(10) NULL ) GO
Вы можете также задать ограничение PRIMARY KEY после того, как определены все колонки таблицы. При использовании этого синтаксиса имя колонки должно быть заключено в круглые скобки и указано после предложения CONSTRAINT, как это показано в следующем операторе:
CREATE TABLE customer ( first_name char(20) NOT NULL, mid_init char(1) NULL, last_name char(20) NOT NULL, SSN char(11), cust_phone char(10) NULL, CONSTRAINT PK_SSN PRIMARY KEY (SSN) ) GO
Чтобы добавить ограничение PRIMARY KEY к таблице, не имеющей ограничения PRIMARY KEY, используйте оператор ALTER TABLE. Следующий оператор добавляет ограничение PRIMARY KEY к таблице customer:
ALTER TABLE customer ADD CONSTRAINT PK_SSN PRIMARY KEY CLUSTERED (SSN) GO
Здесь мы включили необязательное ключевое слово CLUSTERED, чтобы просто разъяснить, что по колонке первичного ключа будет создан кластеризованный индекс, хотя это происходит по умолчанию.
Чтобы удалить ограничение PRIMARY KEY, используйте оператор ALTER TABLE с предложением DROP CONSTRAINT. Ниже мы удаляем ограничение по колонке SSN:
ALTER TABLE customer DROP CONSTRAINT PK_SSN GO
Отметим, что для предложения DROP CONSTRAINT требуется только имя данного ограничения. Чтобы изменить существующее ограничение PRIMARY KEY по таблице с помощью операторов T-SQL, вы должны сначала удалить существующее ограничение и затем выполнить изменение таблицы, добавив новое ограничение. Это выполняется с помощью операторов ALTER TABLE...DROP CONSTRAINT и ALTER TABLE...ADD CONSTRAINT.