SQL Server – коротко о главном

NULL – специальное «неопределенное» значение, предусмотренное стандартом SQL. Определение NULL/NOT NULL служит для указания, что данный тип поля допускает/запрещает ввод NULL-значений.

IDENTITY начальное_значение, приращение – определение, указывающее, что данное поле представляет собой счетчик. Это означает, что значения в данное поле вставляются сервером с нарастанием автоматически при вставке строки. Если «начальное_значение» и «приращение» пропущены, они полагаются равными 1.

DEFAULT умолчание – определение, указывающее значение по умолчанию, т.е., значение, которое присваивается данному полю, если при вставке новой строки этому полю не было явно присвоено некоторое значение.

CHECK (условие)- ограничение, содержащее условие на поле, которое будет проверяться при вводе новых строк и при изменении значений в уже существующих строках. Если при добавлении или изменении строки условие оказывается ложным, то строка не добавляется/ не изменяется.

PRIMARY KEY – ограничение, указывающее, что в данной таблице данное поле представляет собой первичный ключ. (Составной первичный ключ таким образом объявлять нельзя!) При использовании этого ограничения создается первичный индекс.

UNIQUE – ограничение, указывающее, что в данном поле могут храниться только уникальные значения. При использовании этого ограничения создается уникальный индекс.

Например, в таблице «Предприятия» номер предприятия будет первичным ключом и счетчиком, название фирмы не допускает значений NULL:

CREATE TABLE k_firm

(firm_num NUMERIC(6) IDENTITY PRIMARY KEY,

firm_name VARCHAR(100) NOT NULL,

firm_addr VARCHAR(100)

);

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

CREATE TABLE k_contract

contract_num NUMERIC(6) IDENTITY PRIMARY KEY,

contract_date DATETIME DEFAULT GETDATE(),

contract_type CHAR(1)

CHECK (contract_type IN ('A','B','C')),

firm_num NUMERIC(6) NOT NULL,

staff_num NUMERIC(6)

);

REFERENCES имя_таблицы(имя_поля) - ограничение внешнего ключа, или декларативной ссылочной целостности.

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

CREATE TABLE k_staff

(staff_num NUMERIC(6) IDENTITY,

staff_name VARCHAR(30) NOT NULL,

staff_post VARCHAR(30),

dept_num NUMERIC(6)

REFERENCES k_dept (dept_num),

staff_hiredate DATETIME NOT NULL,

staff_termdate DATETIME

);

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

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

CREATE TABLE k_bill

(bill_num NUMERIC(6) IDENTITY PRIMARY KEY,

bill_date DATETIME DEFAULT GETDATE(),

bill_term DATETIME DEFAULT GETDATE()+30,

contract_num NUMERIC(6),

CONSTRAINT ch_bill_date CHECK (bill_term-bill_date<91)

);

т.е., срок действия счета не может превышать 91 день.

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

CONSTRAINT имя_ограничения FOREIGN KEY (список_полей)

REFERENCES родительская_таблица(внешний ключ)

например,

CREATE TABLE k_contract

(contract_num NUMERIC(6) IDENTITY PRIMARY KEY,

contract_date DATETIME DEFAULT GETDATE(),

contract_type CHAR(1)

CHECK (contract_type IN ('A','B','C')),

firm_num NUMERIC(6) NOT NULL,

staff_num NUMERIC(6),

CONSTRAINT fk_contract_firm_num FOREIGN KEY (firm_num)

REFERENCES k_firm (firm_num),

CONSTRAINT fk_contract_staff_num FOREIGN KEY (staff_num)

REFERENCES k_staff (staff_num)

);

т.е, для таблицы договоров есть два различных внешних ключа: номер предприятия и номер сотрудника, ссылающиеся на таблицы «Предприятия» и «Сотрудники».

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

CONSTRAINT имя_ограничения PRIMARY KEY (список_полей)

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

CREATE TABLE k_protokol

price_num NUMERIC(6) NOT NULL,

bill_num NUMERIC(6) NOT NULL,

kolvo NUMERIC(6) NOT NULL,

price_sum NUMERIC(9,2) NOT NULL,

CONSTRAINT pk_protokol_num

PRIMARY KEY (price_num, bill_num),

CONSTRAINT fk_protokol_price_num FOREIGN KEY (price_num)

REFERENCES k_price (price_num),

CONSTRAINT fk_protokol_bill_num FOREIGN KEY (bill_num)

REFERENCES k_bill (bill_num)

);

Кроме команды CREATE TABLE, к секции DDL относятся также команды ALTER TABLE (изменение описания таблицы) и DROP TABLE (удаление таблицы). Так, например, с помощью команды ALTER TABLE можно добавлять или удалять столбцы или ограничения для уже созданной таблицы. Подробнее об этих командах можно прочитать в MSDN, а примеры их использования приведены в следующем параграфе.

Пример сценария создания БД "РОГА И КОПЫТА"

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

CREATE DATABASE kontora

USE kontora

Таблица "Предприятия"

CREATE TABLE k_firm

(firm_num NUMERIC(6) IDENTITY PRIMARY KEY,

firm_name VARCHAR(100) NOT NULL,

firm_addr VARCHAR(100),

firm_phone NUMERIC(7)

)

Таблица "Отделы"

Мы не можем пока определить внешний ключ для поля staff_num, так как таблица "Сотрудники" еще не определена.

CREATE TABLE k_dept

(dept_num NUMERIC(6) IDENTITY PRIMARY KEY,

dept_short_name VARCHAR(10) NOT NULL,

dept_full_name VARCHAR(100),

staff_num NUMERIC(6)

)

Таблица "Сотрудники"

После создания этой таблицы сразу же можем определить внешний ключ для поля staff_num таблицы k_dept. Это можно сделать с помощью команды ALTER TABLE.

CREATE TABLE k_staff

(staff_num NUMERIC(6) IDENTITY,

staff_name VARCHAR(30) NOT NULL,

staff_post VARCHAR(30),

dept_num NUMERIC(6),

staff_hiredate DATETIME NOT NULL,

staff_termdate DATETIME,

CONSTRAINT pk_staff_num PRIMARY KEY (staff_num),

CONSTRAINT fk_staff_dept_num FOREIGN KEY (dept_num)

REFERENCES k_dept (dept_num)

)

ALTER TABLE k_dept ADD CONSTRAINT fk_staff_num

FOREIGN KEY (staff_num)

REFERENCES k_staff(staff_num)

Таблица "Договоры"

CREATE TABLE k_contract

(contract_num NUMERIC(6) IDENTITY PRIMARY KEY,

contract_date DATETIME DEFAULT GETDATE(),

contract_type CHAR(1)

CHECK (contract_type IN ('A','B','C')),

firm_num NUMERIC(6) NOT NULL,

staff_num NUMERIC(6),

CONSTRAINT fk_contract_firm_num FOREIGN KEY (firm_num)

REFERENCES k_firm (firm_num),

CONSTRAINT fk_contract_staff_num FOREIGN KEY (staff_num)

REFERENCES k_staff (staff_num)

)

Таблица "Счета"

ALTER TABLE здесь приводится просто для иллюстрации, как можно добавлять столбцы в уже созданную таблицу.

CREATE TABLE k_bill

(bill_num NUMERIC(6) IDENTITY PRIMARY KEY,

bill_date DATETIME DEFAULT GETDATE(),

bill_term DATETIME DEFAULT GETDATE()+30,

bill_peni NUMERIC(6) DEFAULT 0,

contract_num NUMERIC(6),

CONSTRAINT fk_bill_contract_num

FOREIGN KEY (contract_num)

REFERENCES k_contract (contract_num),

CONSTRAINT ch_bill_date CHECK (bill_term-bill_date<91)

)

ALTER TABLE k_bill ADD bill_sum NUMERIC(6) DEFAULT 0 NOT NULL

Таблица "Платежи"

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

CREATE TABLE k_payment

(payment_num NUMERIC(2) DEFAULT 0,

bill_num NUMERIC(6),

payment_date DATETIME DEFAULT GETDATE(),

payment_sum NUMERIC(9,2),

CONSTRAINT pk_payment_num

PRIMARY KEY (payment_num, bill_num),

CONSTRAINT fk_payment_bill_num FOREIGN KEY (bill_num)

REFERENCES k_bill (bill_num)

)

Таблица "Товары/услуги" (или "Прайс-лист")

CREATE TABLE k_price

(price_num NUMERIC(6) IDENTITY PRIMARY KEY,

price_name VARCHAR(100) NOT NULL,

price_sum NUMERIC(9,2),

type_num NUMERIC(6)

)

Таблица "Протоколы счетов"

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

CREATE TABLE k_protokol

(price_num NUMERIC(6) NOT NULL,

bill_num NUMERIC(6) NOT NULL,

kolvo NUMERIC(6) NOT NULL,

price_sum NUMERIC(9,2),

CONSTRAINT pk_protokol_num

PRIMARY KEY (price_num, bill_num),

CONSTRAINT fk_protokol_price_num FOREIGN KEY (price_num)

REFERENCES k_price (price_num),

CONSTRAINT fk_protokol_bill_num FOREIGN KEY (bill_num)

REFERENCES k_bill (bill_num)

)

В процессе отладки сценария создания базы данных вам наверняка не раз придется удалять таблицы и создавать их заново. Поэтому для удаления таблиц удобно написать отдельный сценарий.

Перед удалением каждой таблицы выполняется проверка – существует ли эта таблица. Информацию обо всех объектах БД можно получить из системной таблицы sysobjects. Тип объекта базы данных ‘U’ означает ‘user table’, т.е., пользовательская таблица, ‘F’ – ‘foreigh key’, т.е., внешний ключ. Для нашей базы данных сценарий может выглядеть следующим образом:

IF EXISTS(SELECT name FROM sysobjects

WHERE name='k_payment' AND type='U')

DROP TABLE k_payment

IF EXISTS(SELECT name FROM sysobjects

WHERE name='k_protokol' AND type='U')

DROP TABLE k_protokol

IF EXISTS(SELECT name FROM sysobjects

WHERE name='k_bill' AND type='U')

DROP TABLE k_bill

IF EXISTS(SELECT name FROM sysobjects

WHERE name='k_price' AND type='U')

DROP TABLE k_price

IF EXISTS(SELECT name FROM sysobjects

WHERE name='k_contract' AND type='U')

DROP TABLE k_contract

IF EXISTS(SELECT name FROM sysobjects

WHERE name='fk_staff_num' AND type='F')

ALTER TABLE k_dept DROP CONSTRAINT fk_staff_num

IF EXISTS(SELECT name FROM sysobjects

WHERE name='k_staff' AND type='U')

DROP TABLE k_staff

IF EXISTS(SELECT name FROM sysobjects

WHERE name='k_dept' AND type='U')

DROP TABLE k_dept

IF EXISTS(SELECT name FROM sysobjects

WHERE name='k_firm' AND type='U')

DROP TABLE k_firm

IF EXISTS(SELECT name FROM sysobjects

WHERE name='k_bill_list' AND type='U')

DROP TABLE k_bill_list

Удаляются сначала дочерние таблицы, затем родительские.

Таблицы "Отделы" и "Сотрудники" взаимно ссылаются друг на друга по внешним ключам, поэтому сначала приходится удалить одно из ограничений внешнего ключа, и только потом удалять таблицы. Можно ли в данном случае поступить наоборот, т.е. удалить ограничение из таблицы k_staff? Что еще нужно будет изменить в сценарии?