DDL (Data Definition Language) – язык описания данных, составная часть SQL. Рассмотрим команды создания базы данных и таблиц.
Для создания базы данных служит команда
CREATE DATABASE имя_БД
Для активизации базы данных служит команда
USE имя_БД
Выполняйте команду активизации базы данных при каждом запуске SQL Management Studio, поскольку по умолчанию в качестве активной установлена БД master.
Для создания таблиц используется команда CREATE TABLE.
Краткий формат этой команды (квадратные скобки означают необязательные элементы):
CREATE TABLE имя_таблицы(
Список_определений_полей,
[Список_ограничений_таблицы]);
Более подробно смотрите в MSDN.
Определение поля имеет формат:
Имя_поля тип_поля[(размер)]
[NULL] [NOT NULL]
[IDENTITY]
[DEFAULT умолчание]
[Список_ограничений_поля]
Ограничение поля имеет формат
[CHECK (условие)]
[PRIMARY KEY]
[UNIQUE]
[REFERENCES имя_таблицы(имя_поля)]
Чаще всего используются типы полей:
VARCHAR – строковый тип переменной длины;
NUMERIC – числовой тип;
DATETIME – тип дата/время.
Какие еще типы полей есть в SQL server? – обращайтесь кMSDN.
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, к секции 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.
Первичный ключ здесь также состоит из нескольких полей, поэтому ограничение PRIMARY KEY можно создавать только на уровне таблицы, а не на уровне поля. Для каждого из этих полей здесь также создается ограничение внешнего ключа.
В процессе отладки сценария создания базы данных вам наверняка не раз придется удалять таблицы и создавать их заново. Поэтому для удаления таблиц удобно написать отдельный сценарий.
Перед удалением каждой таблицы выполняется проверка – существует ли эта таблица. Информацию обо всех объектах БД можно получить из системной таблицы 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? Что еще нужно будет изменить в сценарии?
