Constraint sql что это
При создании столбцов в T-SQL мы можем использовать ряд атрибутов, ряд которых являются ограничениями. Рассмотрим эти атрибуты.
PRIMARY KEY
С помощью выражения PRIMARY KEY столбец можно сделать первичным ключом.
CREATE TABLE Customers ( Id INT PRIMARY KEY, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20) )
Первичный ключ уникально идентифицирует строку в таблице. В качестве первичного ключа необязательно должны выступать столбцы с типом int, они могут представлять любой другой тип.
Установка первичного ключа на уровне таблицы:
CREATE TABLE Customers ( Id INT, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20), PRIMARY KEY(Id) )
Первичный ключ может быть составным (compound key). Такой ключ может потребоваться, если у нас сразу два столбца должны уникально идентифицировать строку в таблице. Например:
CREATE TABLE OrderLines ( OrderId INT, ProductId INT, Quantity INT, Price MONEY, PRIMARY KEY(OrderId, ProductId) )
Здесь поля OrderId и ProductId вместе выступают как составной первичный ключ. То есть в таблице OrderLines не может быть двух строк, где для обоих из этих полей одновременно были бы одни и те же значения.
IDENTITY
Атрибут IDENTITY позволяет сделать столбец идентификатором. Этот атрибут может назначаться для столбцов числовых типов INT, SMALLINT, BIGINT, TYNIINT, DECIMAL и NUMERIC. При добавлении новых данных в таблицу SQL Server будет инкрементировать на единицу значение этого столбца у последней записи. Как правило, в роли идентификатора выступает тот же столбец, который является первичным ключом, хотя в принципе это необязательно.
CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20) )
Также можно использовать полную форму атрибута:
IDENTITY(seed, increment)
Здесь параметр seed указывает на начальное значение, с которого будет начинаться отсчет. А параметр increment определяет, насколько будет увеличиваться следующее значение. По умолчанию атрибут использует следующие значения:
IDENTITY(1, 1)
То есть отсчет начинается с 1. А последующие значения увеличиваются на единицу. Но мы можем это поведение переопределить. Например:
Id INT IDENTITY (2, 3)
В данном случае отсчет начнется с 2, а значение каждой последующей записи будет увеличиваться на 3. То есть первая строка будет иметь значение 2, вторая — 5, третья — 8 и т.д.
Также следует учитывать, что в таблице только один столбец должен иметь такой атрибут.
UNIQUE
Если мы хотим, чтобы столбец имел только уникальные значения, то для него можно определить атрибут UNIQUE .
CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE )
В данном случае столбцы, которые представляют электронный адрес и телефон, будут иметь уникальные значения. И мы не сможем добавить в таблицу две строки, у которых значения для этих столбцов будет совпадать.
Также мы можем определить этот атрибут на уровне таблицы:
CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20), UNIQUE(Email, Phone) )
NULL и NOT NULL
Чтобы указать, может ли столбец принимать значение NULL, при определении столбца ему можно задать атрибут NULL или NOT NULL . Если этот атрибут явным образом не будет использован, то по умолчанию столбец будет допускать значение NULL. Исключением является тот случай, когда столбец выступает в роли первичного ключа — в этом случае по умолчанию столбец имеет значение NOT NULL.
CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE )
DEFAULT
Атрибут DEFAULT определяет значение по умолчанию для столбца. Если при добавлении данных для столбца не будет предусмотрено значение, то для него будет использоваться значение по умолчанию.
CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE );
Здесь для столбца Age предусмотрено значение по умолчанию 18.
CHECK
Ключевое слово CHECK задает ограничение для диапазона значений, которые могут храниться в столбце. Для этого после слова CHECK указывается в скобках условие, которому должен соответствовать столбец или несколько столбцов. Например, возраст клиентов не может быть меньше 0 или больше 100:
CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18 CHECK(Age >0 AND Age < 100), FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE CHECK(Email !=''), Phone VARCHAR(20) UNIQUE CHECK(Phone !='') );
Здесь также указывается, что столбцы Email и Phone не могут иметь пустую строку в качестве значения (пустая строка не эквивалентна значению NULL).
Для соединения условий используется ключевое слово AND . Условия можно задать в виде операций сравнения больше (>), меньше (<), не равно (!=).
Также с помощью CHECK можно создать ограничение в целом для таблицы:
CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE, CHECK((Age >0 AND Age<100) AND (Email !='') AND (Phone !='')) )
Оператор CONSTRAINT. Установка имени ограничений.
С помощью ключевого слова CONSTRAINT можно задать имя для ограничений. В качестве ограничений могут использоваться PRIMARY KEY, UNIQUE, DEFAULT, CHECK.
Имена ограничений можно задать на уровне столбцов. Они указываются после CONSTRAINT перед атрибутами:
CREATE TABLE Customers ( Id INT CONSTRAINT PK_Customer_Id PRIMARY KEY IDENTITY, Age INT CONSTRAINT DF_Customer_Age DEFAULT 18 CONSTRAINT CK_Customer_Age CHECK(Age >0 AND Age < 100), FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) CONSTRAINT UQ_Customer_Email UNIQUE, Phone VARCHAR(20) CONSTRAINT UQ_Customer_Phone UNIQUE )
Ограничения могут носить произвольные названия, но, как правило, для применяются следующие префиксы:
- "PK_" - для PRIMARY KEY
- "FK_" - для FOREIGN KEY
- "CK_" - для CHECK
- "UQ_" - для UNIQUE
- "DF_" - для DEFAULT
В принципе необязательно задавать имена ограничений, при установке соответствующих атрибутов SQL Server автоматически определяет их имена. Но, зная имя ограничения, мы можем к нему обращаться, например, для его удаления.
И также можно задать все имена ограничений через атрибуты таблицы:
CREATE TABLE Customers ( Id INT IDENTITY, Age INT CONSTRAINT DF_Customer_Age DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30), Phone VARCHAR(20), CONSTRAINT PK_Customer_Id PRIMARY KEY (Id), CONSTRAINT CK_Customer_Age CHECK(Age >0 AND Age < 100), CONSTRAINT UQ_Customer_Email UNIQUE (Email), CONSTRAINT UQ_Customer_Phone UNIQUE (Phone) )
Предложение CONSTRAINT
Таблицы ограничение аналогичны индекс, однако их также можно использовать для создания связи отношение другой таблицей.
Предложение CONSTRAINT используется в инструкциях ALTER TABLE и CREATE TABLE для создания и удаления ограничений. Существует два типа предложений CONSTRAINT: для создания ограничения на одно поле и на несколько полей.
Примечание: Ядро СУБД Microsoft Access не поддерживает использование CONSTRAINT или любых других инструкций DDL с базами данных, которые не основаны на Microsoft Access. Вместо этого применяйте методы Create DAO.
Синтаксис
Ограничение на одно поле:
CONSTRAINT имя REFERENCES внешняя_таблица [(внешнее_поле1, внешнее_поле2)]
[ON UPDATE CASCADE | SET NULL]
[ON DELETE CASCADE | SET NULL]>
Ограничение на несколько полей:
CONSTRAINT имя
первичный_ключ1[, первичный_ключ2 [, . ]]) |
UNIQUE (уникальный_ключ1[, уникальный_ключ2 [, . ]]) |
NOT NULL (непустое1[, непустое2 [, . ]]) |
FOREIGN KEY [NO INDEX] (ссылка1[, ссылка2 [, . ]]) REFERENCES внешняя_таблица [(внешнее_поле1 [, внешнее_поле2 [, . ]])]
[ON UPDATE CASCADE | SET NULL]
[ON DELETE CASCADE | SET NULL]>
Предложение CONSTRAINT включает в себя следующие элементы:
Имя создаваемого ограничения.
первичный_ключ1, первичный_ключ2
Имена полей, определяемых как первичный ключ.
уникальный_ключ1, уникальный_ключ2
Имена полей, определяемых как уникальный ключ.
непустое1, непустое2
Имена полей, в которых не может быть значений NULL.
ссылка1, ссылка2
Имя поля или полей внешнего ключа, которые ссылаются на поля в другой таблице.
внешняя_таблица
Имя поля внешняя таблица которое содержит поле или поля, заданные полем внешнего поля.
внешнее_поле1, внешнее_поле2
Имена полей во внешней_таблице, заданных аргументами ссылка1, ссылка2. Если ссылка указывает на поле первичного ключа внешней_таблицы, данный аргумент можно опустить.
Замечания
Выражение для ограничения на одно поле указывается в предложении определения поля инструкции ALTER TABLE или CREATE TABLE непосредственно за спецификацией типа данных поля.
Выражение для ограничения на несколько полей указывается тогда, когда зарезервированное слово CONSTRAINT используется вне предложения определения поля в инструкции ALTER TABLE или CREATE TABLE.
В предложении CONSTRAINT можно задать для поля один из следующих типов ограничений:
- Зарезервированное слово UNIQUE используется для назначения поля в качестве уникального ключа. Это значит, что две записи в таблице не могут иметь одно и то же значение в этом поле. Любое поле (или список полей) можно ограничить как уникальное. Если ограничение на несколько полей назначено уникальным ключом, объединенные значения всех полей в индексе должны быть уникальными, даже если несколько записей имеют одинаковое значение в одном из полей.
- Зарезервированные слова PRIMARY KEY используются для назначения одного поля или совокупности полей таблицы в качестве первичного ключа. Все значения в первичном ключе должны быть уникальными и отличными от NULL, причем в таблице может быть только один первичный ключ.
Примечание: Ограничение PRIMARY KEY не следует устанавливать в таблице, уже имеющей первичный ключ: это приведет к ошибке.
CREATE TABLE Customers (CustId INTEGER PRIMARY KEY, CLstNm NCHAR VARYING (50))
Пример определения для таблицы Orders, в котором задается отношение внешнего ключа, ссылающееся на первичный ключ таблицы Customers:
CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE CASCADE ON DELETE CASCADE
Для внешнего ключа определены предложения ON UPDATE CASCADE и ON DELETE CASCADE. Предложение ON UPDATE CASCADE означает, что при обновлении в таблице Customers идентификатора клиента (CustId) будет выполнено каскадное обновление в таблице Orders. В каждый заказ, содержащий соответствующее значение идентификатора клиента, будет автоматически внесено новое значение. Предложение ON DELETE CASCADE означает, что при удалении клиента из таблицы Customers все строки таблицы Orders, содержащие его идентификатор, также будут удалены.
Другое определение для таблицы Orders, в котором вместо действия CASCADE используется SET NULL:
CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE SET NULL ON DELETE SET NULL
Предложение ON UPDATE SET NULL означает, что при обновлении в таблице Customers идентификатора клиента (CustId) соответствующие значения внешнего ключа в таблице Orders автоматически изменятся на NULL. Аналогично предложение ON DELETE SET NULL означает, что при удалении клиента из таблицы Customers все соответствующие внешние ключи в таблице Orders принимают значение NULL.
Чтобы предотвратить автоматическое создание индексов для внешних ключей, можно использовать модификатор NO INDEX. Определение внешнего ключа в такой форме должно применяться только в том случае, если получающиеся в результате значения индексов будут часто повторяться. Если значения в индексе внешнего ключа часто повторяются, использование индекса может быть менее эффективно, чем простое сканирование таблицы. Сохранение такого индекса при вставке и удалении строк таблицы снижает производительность и не приносит никакой пользы.
SQL — Ограничения
От автора: ограничения SQL — это правила, применяемые к столбцам данных таблицы. Они используются, чтобы ограничить типы данных, которые могут храниться в таблице. Это обеспечивает точность и надежность данных в базе данных.
Ограничения могут применяться либо на уровне столбцов, либо на уровне таблицы. Ограничения на уровне столбца применяются только к одному столбцу, тогда как ограничения уровне таблицы применяются ко всей таблице.
Ниже приведены некоторые из наиболее часто используемых ограничений, доступных в SQL. Эти ограничения уже рассматривались в главе «Концепции SQL-RDBMS» , но сейчас мы вернемся к ним еще раз.
NOT NULL Constraint — столбец не может иметь значение NULL.
DEFAULT Constraint — задает значение по умолчанию для столбца, если оно не указано.
UNIQUE Constraint — все значения в столбце должны быть разными.
PRIMARY Key — уникальная идентификация каждой строки/записи в таблице базы данных.
FOREIGN Key — уникально идентифицирует строку/запись в любой другой таблице базы данных.
CHECK Constraint — ограничение CHECK обеспечивает, чтобы все значения в столбце удовлетворяли определенным условиям.
INDEX — используется для быстрого создания данных базы данных.
Ограничения могут указываться при создании таблицы с помощью оператора CREATE TABLE или вы можете использовать оператор ALTER TABLE для создания ограничений уже после создания таблицы.
Удаление ограничений
Любое ограничение, которое вы определили, можно удалить с помощью команды ALTER TABLE с параметром DROP CONSTRAINT.
Например, чтобы удалить ограничение первичного ключа в таблице EMPLOYEES, вы можете использовать следующую команду.
Ограничения в SQL
Ограничение (constraints) — это ограничение типа значений, которое накладывается на один или несколько столбцов таблицы. Это позволяет поддерживать точность и целостность данных в таблице БД.
В SQL существует несколько различных типов ограничений, в том числе:
- NOT NULL
- PRIMARY KEY
- UNIQUE
- DEFAULT
- FOREIGN KEY
- CHECK
Давайте обсудим каждое из этих ограничений попродробнее.
Ограничение NOT NULL
Ограничение NOT NULL указывает, что столбец не может принимать значения NULL .
Если к столбцу применено ограничение NOT NULL , вы не сможете вставить новую строку в таблицу без добавления не-NULL-значения в этот столбец.
Пример
Следующая SQL-инструкция создает таблицу persons с четырьмя столбцами, из которых три столбца — id , name и phone — не могут иметь значение NULL .
CREATE TABLE persons ( id INT NOT NULL, name VARCHAR(30) NOT NULL, birth_date DATE, phone VARCHAR(15) NOT NULL );
Примечание. Нулевое значение ( NULL ) — это не ноль(0) и не строка символов нулевой длины (''). NULL означает, что записи нет.
Ограниение PRIMARY KEY
Ограничение PRIMARY KEY определяет столбец или набор столбцов, значения которых однозначно идентифицируют строку в таблице. То есть никакие две строки в таблице не могут иметь одинаковое значение первичного ключа. Также нельзя вводить значение NULL в столбец первичного ключа.
Пример
Следующая SQL-инструкция создает таблицу persons и указывает столбец id в качестве первичного ключа. Это означает, что в этом поле не допускаются значения NULL или дубликаты.
CREATE TABLE persons ( id INT NOT NULL PRIMARY KEY, name VARCHAR(30) NOT NULL, birth_date DATE, phone VARCHAR(15) NOT NULL );
Примечание. Первичный ключ обычно состоит из одного столбца в таблице, однако несколько столбцов могут составлять первичный ключ. Например, адрес электронной почты сотрудника или присвоенный ID является логическим первичным ключом для таблицы сотрудников.
Ограничение UNIQUE
Ограничение UNIQUE означает, что в указанных столбцах обязательно должны быть уникальные значения.
Хотя и ограничение UNIQUE, и ограничение PRIMARY KEY обеспечивают уникальность значений, есть различия.
UNIQUE лучше PRIMARY KEY, когда вы хотите обеспечить уникальность столбца или комбинации столбцов, которые не являются первичным ключом.
Пример
Следующая SQL-инструкция создает таблицу persons и определяет столбец phone как уникальный. Это означает, что это поле не допускает дублирования значений.
CREATE TABLE persons ( id INT NOT NULL PRIMARY KEY, name VARCHAR(30) NOT NULL, birth_date DATE, phone VARCHAR(15) NOT NULL UNIQUE );
Примечание. В одной таблице может быть задано ограничений UNIQUE , но только одно ограничение PRIMARY KEY . Кроме того, в отличие от ограничений PRIMARY KEY , ограничения UNIQUE допускают значения NULL .
Ограничение DEFAULT
Ограничение DEFAULT определяет значение по умолчанию для столбцов.
Значение столбца по умолчанию — это некоторое значение, которое будет вставлено в столбец базой данных, если оператор INSERT явно не назначит конкретное значение.
Пример
В следующей SQL-инструкции мы задаем значение по умолчанию для столбца country .
CREATE TABLE persons ( id INT NOT NULL PRIMARY KEY, name VARCHAR(30) NOT NULL, birth_date DATE, phone VARCHAR(15) NOT NULL UNIQUE, country VARCHAR(30) NOT NULL DEFAULT 'Россия' );
Примечание. Если вы определили столбец таблицы как NOT NULL , но присвоили ему значение по умолчанию, то в операторе INSERT вам не нужно явно присваивать значение этому столбцу, чтобы вставить новую строку в таблицу.
Ограничение FOREIGN KEY
Внешний ключ (foreign key) — это столбец или комбинация столбцов, которые используются для установления и обеспечения взаимосвязи между данными в двух таблицах.
Ниже — диаграмма, которая показывает связь между таблицами сотрудников ( employees ) и отделов ( departments ). Если вы внимательно посмотрите на нее, то заметите, что столбец dept_id таблицы сотрудников совпадает со столбцом первичного ключа таблицы отделов. Поэтому столбец dept_id таблицы сотрудников является внешним ключом для таблицы отделов.
В MySQL можно создать внешний ключ, задав ограничение FOREIGN KEY при создании таблицы следующим образом.
Пример
В следующей SQL-инструкции мы определяем столбец dept_id таблицы employees как внешний ключ, который ссылается на столбец dept_id таблицы departments .
CREATE TABLE employees ( emp_id INT NOT NULL PRIMARY KEY, emp_name VARCHAR(55) NOT NULL, hire_date DATE NOT NULL, salary INT, dept_id INT, FOREIGN KEY (dept_id) REFERENCES departments(dept_id) );
Ограничение CHECK
Ограничение CHECK используется для ограничения значений, которые могут быть помещены в столбец.
Например, диапазон значений для столбца зарплаты salary можно ограничить, создав ограничение CHECK , которое допускает значения только от 30 000 до 100 000. Это предотвратит ввод зарплат за пределами обычного (условного) диапазона.
Пример
CREATE TABLE employees ( emp_id INT NOT NULL PRIMARY KEY, emp_name VARCHAR(55) NOT NULL, hire_date DATE NOT NULL, salary INT NOT NULL CHECK (salary >= 3000 AND salary , dept_id INT, FOREIGN KEY (dept_id) REFERENCES departments(dept_id) );
Примечание. MySQL не поддерживает ограничение CHECK.
СodeСhick.io - простой и эффективный способ изучения программирования.
2023 © ООО "Алгоритмы и практика"