Атрибуты и ограничения столбцов и таблиц в T-SQL

При создании столбцов в T-SQL мы можем использовать ряд атрибутов, ряд которых являются ограничениями. Рассмотрим эти атрибуты.

PRIMARY KEY

С помощью выражения PRIMARY KEY столбец можно сделать первичным ключом.

1 2 3 4 5 6 7 8 9

CREATE TABLE Customers (     Id INT PRIMARY KEY,     Age INT,     FirstName NVARCHAR(20),     LastName NVARCHAR(20),     Email VARCHAR(30),     Phone VARCHAR(20) )

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

Установка первичного ключа на уровне таблицы:

1 2 3 4 5 6 7 8 9 10

CREATE TABLE Customers (     Id INT,     Age INT,     FirstName NVARCHAR(20),     LastName NVARCHAR(20),     Email VARCHAR(30),     Phone VARCHAR(20),     PRIMARY KEY(Id) )

Первичный ключ может быть составным (compound key). Такой ключ может потребоваться, если у нас сразу два столбца должны уникально идентифицировать строку в таблице. Например:

1 2 3 4 5 6 7 8

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 будет инкрементировать на единицу значение этого столбца у последней записи. Как правило, в роли идентификатора выступает тот же столбец, который является первичным ключом, хотя в принципе это необязательно.

1 2 3 4 5 6 7 8 9

CREATE TABLE Customers (     Id INT PRIMARY KEY IDENTITY,     Age INT,     FirstName NVARCHAR(20),     LastName NVARCHAR(20),     Email VARCHAR(30),     Phone VARCHAR(20) )

Также можно использовать полную форму атрибута:

1	IDENTITY(seed, increment)

Здесь параметр seed указывает на начальное значение, с которого будет начинаться отсчет. А параметр increment определяет, насколько будет увеличиваться следующее значение. По умолчанию атрибут использует следующие значения:

1	IDENTITY(1, 1)

То есть отсчет начинается с 1. А последующие значения увеличиваются на единицу. Но мы можем это поведение переопределить. Например:

1	Id INT IDENTITY (2, 3)

В данном случае отсчет начнется с 2, а значение каждой последующей записи будет увеличиваться на 3. То есть первая строка будет иметь значение 2, вторая - 5, третья - 8 и т.д.

Также следует учитывать, что в таблице только один столбец должен иметь такой атрибут.

UNIQUE

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

1 2 3 4 5 6 7 8 9

CREATE TABLE Customers (     Id INT PRIMARY KEY IDENTITY,     Age INT,     FirstName NVARCHAR(20),     LastName NVARCHAR(20),     Email VARCHAR(30) UNIQUE,     Phone VARCHAR(20) UNIQUE )

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

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

1 2 3 4 5 6 7 8 9 10

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.

1 2 3 4 5 6 7 8 9

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 определяет значение по умолчанию для столбца. Если при добавлении данных для столбца не будет предусмотрено значение, то для него будет использоваться значение по умолчанию.

1 2 3 4 5 6 7 8 9

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:

1 2 3 4 5 6 7 8 9

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 можно создать ограничение в целом для таблицы:

1 2 3 4 5 6 7 8 9 10

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 перед атрибутами:

1 2 3 4 5 6 7 8 9 10 11

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 автоматически определяет их имена. Но, зная имя ограничения, мы можем к нему обращаться, например, для его удаления.

И также можно задать все имена ограничений через атрибуты таблицы:

1 2 3 4 5 6 7 8 9 10 11 12 13

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) )