SQL — что это, для чего необходим язык, а также базовые функции для новичков. Создание БД с помощью SQL

Язык SQL используется не только для обработки информации, но и предназначена для выполнения всех операций с базами данных и таблицами, включая также создание таблиц и работа с ними. Существует два способа создания таблиц: 1) большинство СУБД обладают визуальным интерфейсом для интерактивного создания таблиц и управление ими; 2) таблицами можно манипулировать, используя операторы SQL. Стоит отметить, что, когда вы используете интерактивный инструментарий СУБД, на самом деле вся работа выполняется операторами SQL, т.е. интерфейс сам создает эти команды незаметно для пользователя (это подобно на запись макроса в Excel, когда макрорекодер записывает ваши действия и преобразует их в команды VBA).

1. Создание таблиц

Для создания таблиц программным способом используют оператор CREATE TABLE . Для этого нужно указать следующие данные:

- имя таблицы, которое указывается после ключевого слова CREATE TABLE
- имена и определения столбцов таблицы, отделены запятыми
- в некоторых СУБД также требуется, чтобы было указано местоположение таблицы.

Давайте создадим новую таблицу и назовем ее Customers :

CREATE TABLE Customers (
ID CHAR(10) NOT NULL Primary key ,
Custom_name CHAR(25) NOT NULL,
Custom_address CHAR(25) NULL,
Custom_city CHAR(25) NULL,
Custom_Country CHAR(25) NULL,
ArcDate CHAR(25) NOT NULL, DEFAULT NOWO)

Так мы сначала указываем название новой таблицы, затем в скобках перечисляем столбцы, которие будем создавать, причем их названия не могут повторяться в пределах одной таблицы. После названий столбцов указывается тип данных для каждого поля (CHAR (10) ), затем отмечаем может ли поле содержать пустые значения (NULL или NOT NULL ), а также нужно указать поле, которое будет первичным ключом (Primary key ).

Язык SQL также позволяет определять для каждого поля значение по умолчанию, то есть, если пользователь не укажет значение определенного поля - оно будет автоматически проставлено СУБД. Значение по умолчанию определяется ключевым словом DEFAULT при определении столбцов оператором CREATE TABLE .

2. Обновление таблиц

Для того, чтобы изменить таблицу в SQL используется оператор ALTER TABLE . При использовании данного оператора необходимо ввести следующую информацию:

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

Для примера давайте добавим новую колонку в таблицу Sellers , в которой будем указывать телефон реализатора:

ALTER TABLE Sellers ADD Phone CHAR (20)

Кроме добавления столбцов, мы можем их удалять. Давайте теперь удалим поле Phone . Для этого пропишем следующий запрос:

ALTER TABLE Sellers DROP COLUMN Phone

3. Удаление таблиц

Удаление таблиц осуществляется с помощью оператора DROP TABLE . Чтобы удалить таблицу Sellers_new , мы можем прописать следующий запрос:

DROP TABLE Sellers_new

Во многих СУБД применяются правила, предотвращающие удаление таблиц, которые являются уже связаны с другими таблицами. Если эти правила действуют и вы удаляете такую таблицу, то СУБД блокирует операцию удаления до тех пор, пока не будет удалена связь. Такие меры предотвращают случайное удаление нужных таблиц.

Вступление

Продолжим изучение SQL запросов к базе данных. Сегодня, один из простых SQL запросов, SQL запрос для создания таблицы базы данных. Работает запрос на основе оператора CREATE TABLE.

Создание таблицы базы данных

Есть оператор sql, который используется для создания таблиц баз данных. Оператор: CREATE TABLE.

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

Уникальное имя;
Столбцы (атрибуты);
Первичный ключ.
Строки (записи);

Для начала, мы создаем, только таблицу и не заполняем её данными.

По-хорошему, общий вид таблицы с уникальными именами строк и столбцов задается при создании . Мы для примера создадим простенькую таблицу БД и обойдемся без концептуальной модели.

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

Чем идентифицируется покупатель? Решаем, что каждый покупатель идентифицируется следующими данными:

Фамилия;
Логин;
Пароль;
Email;
телефон.

Помним, что в таблице обязательно нужно задать .

Чтобы создать таблицу базы данных, используем оператор sql CREATE TABLE.

Синтаксис оператора такой:

CREATE TABLE имя таблицы (столбец 1, тип столбца (пробел) параметр столбца (пробел), столбец 2, тип столбца (пробел) параметр столбца (пробел), столбец 3, тип столбца (пробел) параметр столбца (пробел), и т.д.)

В нашем варианте, имя таблицы: clients. Даем данным покупателей обозначения столбцов:

Имя: пусть будет client_customer;
Фамилия: client_subclient;
Логин: client_login;
Пароль: client_password;
eMail: client_mail;
Телефон: client_telefon.

Думаем, какой тип данных будет храниться в этих столбцах, и выбираем типы данных CHAR(длина поля постоянно) или VARCHAR(длинна поля переменное). В нашем случае подходит тип данных VARCHAR.

Особое внимание уделяем первичному ключу. Делаем первичным ключом id покупателя. В параметрах ключа задаем, что это первичный ключ, он не может быть нулём и что для следующей записи увеличивается на единицу. Последнее свойство называется автоинкремент. Смотрим подсказку в справочнике (ссылка выше) и получаем столбец с первичным ключом:

Client_id integer not null auto_increment primary key

Пишем SQL запрос для создания таблицы базы данных

Суммируем все исходные данные и получаем такой SQL запрос:

/*Таблица пользователей clients*/ create table clients (/*client_id будет первичный ключ (обязательно целое число) с автоинкрементом (+1), который никогда не будет равен нулю*/ client_id integer not null auto_increment primary key, client_customer varchar(13), /*имя */ client_surclient varchar(22), /*фамилия */ client_login varchar(21), /*логин*/ client_passwd varchar(7), /*пароль*/ client_email varchar(44) /*email*/ client_telefon varchar(26) /*телефон*/);

Примечание: SQL запрос для создания таблицы создаем в текстовом редакторе типа Notepad++. В скобках ограничиваем длину поля, может быть от 1 до 255.

Итоги статьи

В этой статье мы познакомились с первым оператором SQL языка CREATE TABLE
Создали SQL запрос для создания одной (первой) таблицы базы данных с первичным ключом.

Разновидность языка, применяемая в конкретной СУБД, называется диалектом SQL . Например, диалект СУБДOracleназываетсяPL / SQL ; вMSSQLServerиDB2 применяется диалектTransact - SQL ; вInterbaseиFirebird–isql . Каждый диалектSQLсовместим до определенной степени со стандартомSQL, но может иметь отличия и специфические расширения языка, поэтому для выяснения синтаксиса того или иногоSQL-оператора следует в первую очередь смотретьHelp конкретной СУБД.

Для операций над базами данных и таблицами в стандарте sql предусмотрены операторы:

Ниже приводится синтаксис этих операторов по стандарту SQL92. Поскольку их синтаксис в СУБД может отличаться от стандарта, при выполнении лабораторной работы рекомендуется обращаться к справочной системе СУБД.

Имена объектов базы данных (таблиц, столбцов и др.) могут состоять из буквенно-цифровых символов и символа подчеркивания. Специальные символы (@$# и т.п.) обычно указывают на особый тип таблицы (системная, временная и др.). Не рекомендуется использовать в именах национальные (русские) символы, пробелы и зарезервированные слова, но если они всё же используются, то такие имена следует писать в кавычках ".." или в квадратных скобках [..].

Далее при описании конструкций операторов SQLбудут использоваться следующие обозначения: в квадратных скобках записываются необязательные части конструкции; альтернативные конструкции разделяются вертикальной чертой | ; фигурные скобки {} выделяют логические блоки конструкции; многоточие… указывает на то, что предшествующая часть конструкции может многократно повторяться. «Раскрываемые» конструкции записываются в угловых скобках < >.

Создание базы данных

CREATE DATABASE Имя_базы_данных

Удаление одной и более баз данных

DROP DATABASE Имя_базы_данных [,Имя_базы_данных …]

Объявление текущей базы данных

USE Имя_базы_данных –- в SQL Server и MySQL

SET DATABASE Имя _ базы _ данных – в Firebird

Создание таблицы

CREATE TABLE Имя_таблицы (

<описание_столбца> [, <описание_столбца> |

<ограничение_целостности_таблицы> …]

< описание_столбца > 

Имя_столбца ТИП

{NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

ТИП столбца может быть либо стандартным типом данных (см. таблицу 1), либо именем домена (см. п.6.2).

Некоторые СУБД позволяют создавать вычислимые столбцы (computed columns ). Это виртуальные столбцы, значение которых не хранится в физической памяти, а вычисляется сервером СУБД при всяком обращении к этому столбцу по формуле, заданной при объявлении этого столбца. В формулу могут входить значения других столбцов этой строки, константы, встроенные функции и глобальные переменные.

Описание вычислимого столбца в SQL Server имеет вид:

<описание_столбца>  Имя_столбца AS выражение

Описание вычислимого столбца в Firebird имеет вид:

<описание_столбца>  Имя_столбца COMPUTED BY <выражение>

СУБД MySQL 3.23 вычислимые столбцы не поддерживает.

< > 

CONSTRAINT Имя_ограничения_целостности

{UNIQUE|PRIMARY KEY}(список_столбцов_образующих_ключ )

|FOREIGN KEY (список _ столбцов _FK )

REFERENCES Имя_таблицы (список_столбцов_ PK )

{NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

|CHECK (условие_проверки )

Некоторые СУБД допускают объявление врéменных таблиц (существующих только во время сеанса). В SQL Server имена временных таблиц должны начинаться с символа # (локальные временные таблицы, видимые только создавшему их пользователю) или ## (глобальные таблицы, видимые всем пользователям); в MySQL для создания временных таблиц используется ключевое слово TEMPORARY, например:

CREATE TEMPORARY TABLE … (далее синтаксис см. CREATE TABLE).

Изменение структуры таблицы

Используется для изменения типа столбцов существующих таблиц, добавления и удаления столбцов и ограничений целостности.

ALTER TABLE Имя_таблицы

Изменение типа столбца (в SQLServerиFirebird)

ALTER COLUMN Имя_столбца новый_ТИП

Изменение типа, имени и ограничений столбца (в MySQL)

CHANGE COLUMN Имя_столбца <описание_столбца>

Добавление обычного или вычислимого столбца

|ADD <описание_столбца >

Добавление ограничения целостности

| ADD

<ограничение_целостности_таблицы >

Удаление столбца

|DROP COLUMN Имя_столбца

Удаление ограничения целостности

|DROP CONSTRAINT Имя_ограничения_целостности

Включение или отключение проверки ограничений целостности

ВMSSQLServer

|{CHECK|NO CHECK} CONSTRAINT

{Список_имен_ограничений_целостности |ALL}

Удаление таблицы

DROP TABLE Имя_таблицы



Далее рассмотрим, как при создании новых таблиц командой CREATETABLEили изменении структуры существующих таблиц командойALTERTABLEобъявить декларативные ограничения целостности (подробнее они описаны в п.4.2) .

1. Обязательное наличие данных (NULL–значения)

Объявляется словом NULL(столбец может иметь пустые ячейки) илиNOT NULL(столбец обязательный). По умолчанию принимаетсяNULL.

Пример создания таблицы 7:

CREATE TABLE Clients(

ClientName NVARCHAR (60) NOT NULL ,

DateOfBirth DATE NULL ,

Phone CHAR (12)); -- по умолчанию тоже NULL

2. Значение по умолчанию (DEFAULT)

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

Рассмотрим пример создания таблицы Orders (Заказы). Столбец OrderDate принимает по умолчанию значение текущей даты, а столбец Quantity (количество) по умолчанию равен 0.

CREATE TABLE Orders(

OrderNum INT NOT NULL , -- номер заказа

OrderDate DATETIME NOT NULL -- дата заказа

DEFAULT GetDate(),

Функция GetDate() возвращает текущую дату 8

Quantity SMALLINT NOT NULL -- кол-во товара, DEFAULT 0);

3. Объявление первичных ключей (PRIMARYKEY)

Простой первичный ключ объявляется словами PRIMARYKEYпри создании таблицы. Например,

CREATE TABLE Staff(-- таблица "Работники"

TabNum INT PRIMARY KEY , -- первичный ключ

WName NVARCHAR (40) NOT NULL , -- ФИО

... -- описание прочих столбцов );

Составной первичный ключ объявляется иначе:

-- способ 1 (объявление PK при создании таблицы)

CREATE TABLE Clients(

PasSeria NUMERIC (4,0)NOT NULL ,-- серия паспорта

PasNumber NUMERIC (6,0)NOT NULL ,-- номер паспорта

Name NVARCHAR (40)NOT NULL ,

Phone CHAR (12),

-- объявление составного первичного ключа

CONSTRAINT Clients_PK

PRIMARY KEY (PasSeria,PasNumber));

-- способ 2(PK объявляется после создания таблицы)

-- сначала создаем таблицу без PK

CREATE TABLE Clients(

PasSeria NUMERIC (4,0)NOT NULL ,--серия паспорта

PasNumber NUMERIC (6,0)NOT NULL ,--номер паспорта

ClientName NVARCHAR (40)NOT NULL ,

Phone CHAR (12));

-- модификация таблицы – добавляем РК

ALTER TABLE Clients

ADD CONSTRAINT Clients_PK

PRIMARY KEY (PasSeria,PasNumber);

4. Уникальность столбцов (UNIQUE)

Подобно Primary Key указывает, что столбец или группа столбцов не могут содержать повторяющихся значений, но не являютсяPK . Все столбцы, объявленныеUNIQUE, должны бытьNOTNULL. Пример объявления простого уникального столбца:

CREATE TABLE Students(

SCode INT PRIMARY KEY , -- суррогатный РК

FIO NVARCHAR (40) NOT NULL , -- ФИО

RecordBook CHAR (6) NOT NULL UNIQUE ); -- № зачетки

Пример объявления составного уникального поля:

CREATE TABLE Staff(-- таблица " Работники "

TabNum INT PRIMARY KEY , -- табельный номер

WName NVARCHAR (40) NOT NULL , -- ФИО

PasSeria NUMERIC (4,0) NOT NULL , -- серия паспорта

PasNumber NUMERIC (6,0) NOT NULL , -- номер паспорта

-- объявление составного уникального поля

CONSTRAINT Staff_UNQ UNIQUE (PasSeria,PasNumber));

5. Ограничения на значения столбца (CHECK)

Это ограничение позволяет указать диапазон, список или «маску» логически допустимых значений столбца.

Пример создания таблицы Workers (Работники) :

CREATE TABLE Workers(

-- табельные номера 4-значные

TabNum INT PRIMARY KEY

CHECK (TabNum BETWEEN 1000 AND 9999),

Name VARCHAR (60) NOT NULL , -- ФИО сотрудника

-- пол – буква " м " или " ж "

Gentry CHAR (1) NOT NULL

CHECK (Gentry IN ("м","ж")),

Возраст не менее 14 лет

Age SMALLINT NOT NULL CHECK (Age>=14),

--№ свидет-ва пенсионного страхования (по маске)

PensionCert CHAR (14)

CHECK (PensionSert LIKE ""));

В этом примере показаны разные типы проверок. Диапазон допустимых значений указывается конструкцией BETWEEN…AND; обычные условия (как для столбцаAge ) используют знаки сравнений =, <>, >, >=, <, <=, связанные при необходимости логическими операциямиAND,OR,NOT(например,Age >=14ANDAge <=70); для указания списка допустимых значений используется предикатINи его отрицаниеNOTIN; конструкция

LIKEмаска_допустимых_значений EXCEPTсписок_исключений

используется для задания маски допустимых значений строковых столбцов. В маске применяются два спецсимвола: «%» – произвольная подстрока, и «_» – любой единичный символ. Конструкция EXCEPTявляется необязательной.

В условии отбора CHECKмогут сравниваться значения двух столбцов одной таблицы и столбцы разных таблиц.

Параметр table_name - имя создаваемой базовой таблицы. Максимальное количество таблиц, которое может содержать одна база данных, ограничивается количеством объектов базы данных, число которых не может быть более 2 миллиардов, включая таблицы, представления, хранимые процедуры, триггеры и ограничения. В параметрах col_name1, col_name2, ... указываются имена столбцов таблицы, а в параметрах type1, type2, ... - типы данных соответствующих столбцов.

Имя объекта базы данных может обычно состоять из четырех частей, в форме:

]]object_name

Здесь object_name - это имя объекта базы данных, schema_name - имя схемы, к которой принадлежит объект, а server_name и db_name - имена сервера и базы данных, к которым принадлежит объект. Имена таблиц, сгруппированные с именем схемы, должны быть однозначными в рамках базы данных. Подобным образом имена столбцов должны быть однозначными в рамках таблицы.

Рассмотрим теперь ограничение, связанное с присутствием или отсутствием значений NULL в столбце. Если для столбца не указано, что значения NULL разрешены (NOT NULL), то данный столбец не может содержать значения NULL, и при попытке вставить такое значение система возвратит сообщение об ошибке.

Как уже упоминалось, объект базы данных (в данном случае таблица) всегда создается в схеме базы данных. Пользователь может создавать таблицы только в такой схеме, для которой у него есть полномочия на выполнение инструкции ALTER. Любой пользователь с ролью sysadmin, db_ddladmin или db_owner может создавать таблицы в любой схеме.

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

Схема, к которой принадлежит таблица, может иметь два возможных имени по умолчанию. Если таблица указывается без явного имени схемы, то система выполняет поиск имени таблицы в соответствующей схеме по умолчанию. Если имя объекта найти в схеме по умолчанию не удается, то система выполняет поиск в схеме dbo. Имена таблиц всегда следует указывать вместе с именем соответствующей схемы. Это позволит избежать возможных неопределенностей.

В примере ниже показано создание всех таблиц базы данных SampleDb. (База данных SampleDb должна быть установлена в качестве текущей базы данных.)

USE SampleDb; CREATE TABLE Department (Number CHAR (4) NOT NULL, DepartmentName NCHAR (40) NOT NULL, Location NCHAR (40) NULL); CREATE TABLE . ( CHAR (4) NOT NULL, NCHAR (15) NOT NULL, FLOAT (53) NULL); CREATE TABLE dbo.Employee (Id INT NOT NULL, FirstName NCHAR (20) NOT NULL, LastName NCHAR (20) NOT NULL, DepartamentNumber CHAR (4) NULL); CREATE TABLE dbo.Works_on (EmpId INT NOT NULL, ProjectNumber CHAR (4) NOT NULL, Job NCHAR (15) NULL, EnterDate DATE NULL);

Кроме типа данных и наличия значения NULL, в спецификации столбца можно указать следующие параметры:

предложение DEFAULT;

свойство IDENTITY.

Предложение DEFAULT в спецификации столбца указывает значение столбца по умолчанию, т.е. когда в таблицу вставляется новая строка, ячейка этого столбца будет содержать указанное значение, которое останется в ячейке, если в нее не будет введено другое значение. В качестве значения по умолчанию можно использовать константу, например одну из системных функций, таких как, USER, CURRENT_USER, SESSION_USER, SYSTEM_USER, CURRENT_TIMESTAMP и NULL.

Столбец идентификаторов, создаваемый указанием свойства IDENTITY , может иметь только целочисленные значения, которые системой присваиваются обычно неявно. Каждое следующее значение, вставляемое в такой столбец, вычисляется, увеличивая последнее, вставленное в этот столбец, значение. Поэтому определение столбца со свойством IDENTITY содержит (явно или неявно) начальное значение и шаг инкремента (такой столбец еще называют столбцом с автоинкрементом).

Ниже показан пример использования этих инструкций:

USE SampleDb; CREATE TABLE UserInfo (-- Для столбца Id будет использоваться автоинкремент IDENTITY(10,5), -- т.е. при вставке данных первому элементу будет присвоено -- значение 10, второму 15, третьему 20 и т.д. Id INT NOT NULL PRIMARY KEY IDENTITY (10,5), Login VARCHAR(40) NOT NULL, -- Для поля BirthDate будет указана дата по умолчанию -- (если это поле не задано явно при вставке данных) BirthDate DATETIME DEFAULT (-- По умолчанию -30 лет от текущей даты DATEADD(year, -30, GETDATE())))

Инструкция CREATE TABLE и ограничения декларативной целостности

Одной из самых важных особенностей, которую должна предоставлять СУБД, является способ обеспечения целостности данных. Ограничения, которые используются для проверки данных при их модификации или вставке, называются ограничениями для обеспечения целостности (integrity CONSTRAINTs) . Обеспечение целостности данных может осуществляться пользователем в прикладной программе или же системой управления базами данных. Наиболее важными преимуществами предоставления ограничений целостности системой управления базами данных являются следующие:

повышается надежность данных;

сокращается время на программирование;

упрощается техническое обслуживание.

Определение ограничений для обеспечения целостности посредством СУБД повышает надежность данных, поскольку устраняется возможность, что программист прикладного приложения может забыть реализовать их. Если ограничения целостности предоставляются прикладными программами, то все приложения, затрагиваемые этими ограничениями, должны содержать соответствующий код. Если код отсутствует хоть в одном приложении, то целостность данных будет поставлена под сомнение.

Если ограничения для обеспечения целостности не предоставляются системой управления базами данных, то их необходимо определить в каждой программе приложения, которая использует данные, включенные в это ограничение. В противоположность этому, если ограничения для обеспечения целостности предоставляются системой управления базами данных, то их требуется определить только один раз. Кроме этого, код для ограничений, предоставляемых приложениями, обычно более сложный, чем в случае таких же ограничений, предоставляемых СУБД.

Если ограничения для обеспечения целостности предоставляются СУБД, то в случае изменений ограничений, соответствующие изменения в коде необходимо реализовать только один раз - в системе управления базами данных. А если ограничения предоставляются приложениями, то модификацию для отражения изменений в ограничениях необходимо выполнить в каждом из этих приложений.

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

декларативные ограничения для обеспечения целостности;

процедурные ограничения для обеспечения целостности, реализуемые посредством триггеров.

Декларативные ограничения определяются с помощью инструкций языка DDL CREATE TABLE и ALTER TABLE. Эти ограничения могут быть уровня столбцов или уровня таблицы. Ограничения уровня столбцов определяются наряду с типом данных и другими свойствами столбца в объявлении столбца, тогда как ограничения уровня таблицы всегда определяются в конце инструкции CREATE TABLE или ALTER TABLE после определения всех столбцов.

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

Каждому декларативному ограничению присваивается имя. Это имя может быть присвоено явно посредством использования опции CONSTRAINT в инструкции CREATE TABLE или ALTER TABLE. Если опция CONSTRAINT не указывается, то имя ограничению присваивается неявно компонентом Database Engine. Настоятельно рекомендуется использовать явные имена ограничений, поскольку это может значительно улучшить поиск этих ограничений.

Декларативные ограничения можно сгруппировать в следующие категории:

предложение DEFAULT;

предложение UNIQUE;

предложение PRIMARY KEY;

предложение CHECK;

ссылочная целостность и предложение FOREIGN KEY.

Использование предложения DEFAULT для определения ограничения по умолчанию было показано ранее. Все другие ограничения рассматриваются в последующих разделах.

Предложение UNIQUE

Иногда несколько столбцов или группа столбцов таблицы имеет уникальные значения, что позволяет использовать их в качестве первичного ключа. Столбцы или группы столбцов, которые можно использовать в качестве первичного ключа, называются потенциальными ключами (candidate key) . Каждый потенциальный ключ определяется, используя предложение UNIQUE в инструкции CREATE TABLE или ALTER TABLE. Синтаксис предложения UNIQUE следующий:

Опция CONSTRAINT в предложении UNIQUE присваивает явное имя потенциальному ключу. Опция CLUSTERED или NONCLUSTERED связана с тем обстоятельством, что компонент Database Engine создает индекс для каждого потенциального ключа таблицы. Этот индекс может быть кластеризованным, когда физический порядок строк определяется посредством индексированного порядка значений столбца. Если порядок строк не указывается, индекс является некластеризованным. По умолчанию применяется опция NONCLUSTERED. Параметр col_name1 обозначает имя столбца, который создает потенциальный ключ. (Потенциальный ключ может иметь до 16 столбцов.)

Применение предложения UNIQUE показано в примере ниже. (Прежде чем выполнять этот пример, в базе данных SampleDb нужно удалить таблицу Projects, используя для этого инструкцию DROP TABLE Projects.)

USE SampleDb; CREATE TABLE Projects (Number CHAR(4) DEFAULT "p1", ProjectName NCHAR (15) NOT NULL, Budget FLOAT (53) NULL, CONSTRAINT unique_number UNIQUE (Number));

Каждое значение столбца Number таблицы Projects является уникальным, включая значение NULL. (Точно так же, как и для любого другого значения с ограничением UNIQUE, если значения NULL разрешены для соответствующего столбца, этот столбец может содержать не более одной строки со значением NULL.) Попытка вставить в столбец Number уже имеющееся в нем значение будет неуспешной, т.к. система не примет его. Явное имя ограничения, определяемого в примере - unique_number.

Предложение PRIMARY KEY

Первичным ключом таблицы является столбец или группа столбцов, значения которого разные в каждой строке. Каждый первичный ключ определяется, используя предложение PRIMARY KEY в инструкции CREATE TABLE или ALTER TABLE. Синтаксис предложения PRIMARY KEY следующий:

Все параметры предложения PRIMARY KEY имеют такие же значения, как и соответствующие одноименные параметры предложения UNIQUE. Но в отличие от столбца UNIQUE, столбец PRIMARY KEY не разрешает значений NULL и имеет значение по умолчанию CLUSTERED.

В примере ниже показано объявление первичного ключа для таблицы Employee базы данных SampleDb. Прежде чем выполнять этот пример, в базе данных SampleDb нужно удалить таблицу Employee, используя для этого инструкцию DROP TABLE Employee.

USE SampleDb; CREATE TABLE Employee (Id INT NOT NULL, FirstName NCHAR (20) NOT NULL, LastName NCHAR (20) NOT NULL, DepartamentNumber CHAR (4) NULL, CONSTRAINT primary_id PRIMARY KEY (Id));

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

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

USE SampleDb; DROP TABLE Employee; CREATE TABLE Employee (Id INT NOT NULL CONSTRAINT primary_id PRIMARY KEY, FirstName NCHAR (20) NOT NULL, LastName NCHAR (20) NOT NULL, DepartamentNumber CHAR (4) NULL);

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

Предложение CHECK

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

Параметр expression должен иметь логическое значение (true или false) и может ссылаться на любые столбцы в текущей таблице (или только на текущий столбец, если определен как ограничение уровня столбца), но не на другие таблицы. Предложение CHECK не применяется принудительно при репликации данных, если присутствует параметр NOT FOR REPLICATION. (При репликации база данных, или ее часть, хранится в нескольких местах. С помощью репликации можно повысить уровень доступности данных.)

В примере ниже показано применение предложения CHECK:

USE SampleDb; CREATE TABLE Customer (CustomerId INTEGER NOT NULL, CustomerRole VARCHAR(100) NULL, CHECK (CustomerRole IN ("admin", "moderator", "user")));

Создаваемая в примере таблица Customer включает столбец CustomerRole, содержащий соответствующее проверочное ограничение. При вставке нового значения, отличающегося от значений в наборе ("admin", "moderator", "user"), или при попытке изменения существующего значения на значение, отличающегося от этих значений, система управления базой данных возвращает сообщение об ошибке.

Предложение FOREIGN KEY

Внешний ключ (foreign key) - это столбец (или группа столбцов таблицы), содержащий значения, совпадающие со значениями первичного ключа в этой же или другой таблице. Внешний ключ определяется с помощью предложения FOREIGN KEY в комбинации с предложением REFERENCES . Синтаксис предложения FOREIGN KEY следующий:

[ ({col_name1} ,...)] REFERENCES table_name ({col_name2},...) Соглашения по синтаксису

Предложение FOREIGN KEY явно определяет все столбцы, входящие во внешний ключ. В предложении REFERENCES указывается имя таблицы, содержащей столбцы, создающие соответствующий первичный ключ. Количество столбцов и их тип данных в предложении FOREIGN KEY должны совпадать с количеством соответствующих столбцов и их типом данных в предложении REFERENCES (и, конечно же, они должны совпадать с количеством столбцов и типами данных в первичном ключе таблицы, на которую они ссылаются).

USE SampleDb; CREATE TABLE Works_on (EmpId INT NOT NULL, ProjectNumber CHAR (4) NOT NULL, Job NCHAR (15) NULL, EnterDate DATE NULL, CONSTRAINT primary_works PRIMARY KEY (EmpId, ProjectNumber), CONSTRAINT foreign_employee FOREIGN KEY (EmpId) REFERENCES Employee (Id), CONSTRAINT foreign_project FOREIGN KEY (ProjectNumber) REFERENCES Projects (Number));

Таблица Works_on в этом примере задается с тремя декларативными ограничениями для обеспечения целостности: primary_works, foreign_employee и foreign_project. Эти ограничения являются ограничением уровня таблицы, где первое указывает первичный ключ, а второе и третье - внешний ключ таблицы Works_on. Кроме этого, внешние ключи определяют таблицы Employee и Projects, как ссылочные таблицы, а их столбцы Id и Number, как соответствующий первичный ключ столбца с таким же именем в таблице Works_on.

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

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

Ссылочная целостность (referential integrity) обеспечивает выполнение правил для вставок и обновлений таблиц, содержащих внешний ключ и соответствующее ограничение первичного ключа. Пример выше имеет два таких ограничения: foreign_employe и foreign_project. Предложение REFERENCES в примере определяет таблицы Employee и Projects в качестве ссылочных (родительских) таблиц.

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

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

Возможные проблемы со ссылочной целостностью - случай 1

Вставка новой строки в таблицу Works_on с номером сотрудника 11111. Соответствующая инструкция Transact-SQL выглядит таким образом:

USE SampleDb; INSERT INTO Works_on VALUES (11111, "p1", "qwe", GETDATE())

При вставке новой строки в дочернюю таблицу Works_on используется новый номер сотрудника EmpId, для которого нет совпадающего сотрудника (и номера) в родительской таблице Employee. Если для обеих таблиц определена ссылочная целостность, как это сделано ранее, то компонент Database Engine не допустит вставки новой строки с таким номером EmpId.

Возможные проблемы со ссылочной целостностью - случай 2

Изменение номера сотрудника 9502 во всех строка таблицы Works_on на номер 11111. Соответствующая инструкция Transact-SQL выглядит таким образом:

В данном случае существующее значение внешнего ключа в ссылающейся таблице Works_on заменяется новым значением, для которого нет совпадающего значения в родительской таблице Employee. Если для обеих таблиц определена ссылочная целостность, то система управления базой данных не допустит модификацию строки с таким номером EmpId в таблице Works_on.

Возможные проблемы со ссылочной целостностью - случай 3

Замена значения 9502 номера сотрудника Id на значение 22222 в таблице Employee. Соответствующая инструкция Transact-SQL будет выглядеть таким образом:

В данном случае предпринимается попытка заменить существующее значение 9502 номера сотрудника Id значением 22222 только в родительской таблице Employee, не меняя соответствующие значения Id в ссылающейся таблице Works_on. Система не разрешает выполнения этой операции. Ссылочная целостность не допускает существования в ссылающейся таблице (таблице, для которой предложением FOREIGN KEY определен внешний ключ) таких значений, для которых в родительской таблице (таблице, для которой предложением PRIMARY KEY определен первичный ключ) не существует соответствующего значения. В противном случае такие строки в ссылающейся таблице были бы "сиротами". Если бы описанная выше модификация таблицы Employee была разрешена, тогда строки в таблице Works_on со значением Id равным 9502 были бы сиротами. Поэтому система и не разрешает выполнения такой модификации.

Возможные проблемы со ссылочной целостностью - случай 4

Удаление строки в таблице Employee со значением Id равным 9502.

Этот случай похожий на случай 3. В случае выполнения этой операции, из таблицы Employee была бы удалена строка со значением Id, для которого существуют совпадающие значения в ссылающейся (дочерней) таблице Works_on.

Опции ON DELETE и ON UPDATE

Компонент Database Engine на попытку удаления и модифицирования первичного ключа может реагировать по-разному. Если попытаться обновить значения внешнего ключа, то все эти обновления будут несогласованы с соответствующим первичным ключом, база данных откажется выполнять эти обновления и выведет сообщение об ошибке.

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

NO ACTION

Модифицируются (обновляются или удаляются) только те значения в родительской таблице, для которых нет соответствующих значений во внешнем ключе дочерней (ссылающейся) таблицы.

CASCADE

Разрешается модификация (обновление или удаление) любых значений в родительской таблице. При обновлении значения первичного ключа в родительской таблице или при удалении всей строки, содержащей данное значение, в дочерней (ссылающейся) таблице обновляются (т.е. удаляются) все строки с соответствующими значениями внешнего ключа.

SET NULL

Разрешается модификация (обновление или удаление) любых значений в родительской таблице. Если обновление значения в родительской таблице вызывает несогласованность в дочерней таблице, система базы данных присваивает внешнему ключу всех соответствующих строк в дочерней таблице значение NULL. То же самое происходит и в случае удаления строки в родительской таблице, вызывающего несогласованность в дочерней таблице. Таким образом, все несогласованности данных пропускаются.

SET DEFAULT

Аналогично опции SET NULL, но с одним исключением: всем внешним ключам, соответствующим модифицируемому первичному ключу, присваивается значение по умолчанию. Само собой разумеется, что после модификации первичный ключ родительской таблицы все равно должен содержать значение по умолчанию.

В языке Transact-SQL поддерживаются первые две из этих опций. Использование опций ON DELETE и ON UPDATE показано в примере ниже:

USE SampleDb; CREATE TABLE Works_on (EmpId INT NOT NULL, ProjectNumber CHAR (4) NOT NULL, Job NCHAR (15) NULL, EnterDate DATE NULL, CONSTRAINT primary_works PRIMARY KEY (EmpId, ProjectNumber), CONSTRAINT foreign_employee FOREIGN KEY (EmpId) REFERENCES Employee (Id) ON DELETE CASCADE, CONSTRAINT foreign_project FOREIGN KEY (ProjectNumber) REFERENCES Projects (Number) ON UPDATE CASCADE);

В этом примере создается таблица Works_on с использованием опций ON DELETE CASCADE и ON UPDATE CASCADE. Если таблицу Works_on загрузить значениями, каждое удаление строки в таблице Employee будет вызывать каскадное удаление всех строк в таблице Works_on, которые имеют значения внешнего ключа, соответствующие значениям первичного ключа строк, удаляемых в таблице Employee. Подобным образом каждое обновление значения столбца Number таблицы Project будет вызывать такое же обновление всех соответствующих значений столбца ProjectNumber таблицы Works_on.

Работа с базами данных непосредственно связана с изменением таблиц и содержащихся в них данных. Но перед началом проведения действий таблицы необходимо создать. Для автоматизации этого процесса существует специальная функция SQL - "CREATE TABLE".

Первым делом!

Перед тем как разбираться с процессом создания таблиц с помощью команды MS SQL "CREATE TABLE", стоит остановиться на том, что надо знать перед началом использования функции.

Прежде всего, необходимо придумать имя таблице - оно должно быть уникальным, в сравнении с другими, находящимися в базе данных, и следовать нескольким правилам. Имя должно начинаться с буквы (a-z), после чего могут следовать любые буквы, цифры и знак подчеркивания, при этом полученная фраза не должна быть зарезервированным словом. Длина названия таблицы не должна превышать 18 символов.

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

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

Синтаксис

Разработав структуру таблицы, можно переходить к её созданию. Сделать это достаточно просто, воспользовавшись "CREATE TABLE". В ней пользователю требуется указать придуманные ранее имя таблицы и список столбцов, указав для каждого из них тип и имя. Синтаксис функции выглядит следующим образом:

CREATE TABLE table_name
({column_name datatype …| table_constraint}
[,{column_name datatype …| table_constraint}]…)

Аргументы, используемые в конструкции функции, означают следующее:

table_name - имя таблицы
column_name - имя столбца
datatype - тип данных, используемый в данном поле
DEFAULT - выражение, используемое в столбце по умолчанию.

Также возможно использование ещё двух аргументов функции:

colum_constraint - параметры столбца
table_constraint - параметры таблицы

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

Особенности создания таблиц

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

Для того чтобы определить, может ли в ячейке находиться пустое значение, после указания имени и типа столбца следует прописать одно из ключевых слов: NULL (могут быть пустые значения) или NOT NULL (поле должно быть заполнено).

При создании таблицы в большинстве случаев требуется унифицировать каждую запись, чтобы избежать наличия двух одинаковых. Для этого чаще всего используют нумерацию строк. И, чтобы не требовать от пользователя знания последнего номера, имеющегося в таблице, в функции "CREATE TABLE" достаточно указать столбец первичного ключа, написав ключевое слово "Primary key" после соответствующего поля. Чаще всего именно по первичному ключу и происходит соединение таблиц между собой.

Для обеспечения сцепки с Primary key используется свойство внешнего ключа "FOREIGN KEY". Указав для столбца данное свойство, можно обеспечить, что в данном поле будет содержаться значение, совпадающее с одним из тех, что находятся в столбце первичного ключа этой же или другой таблицы. Таким образом можно обеспечить соответствие данных.

Чтобы обеспечить проверку на соответствие некоторому заданному набору или определению, следует воспользоваться атрибутом CHECK. Он прописывается последним в списке аргументов функции и в качестве личного параметра имеет некоторое логическое выражение. С его помощью можно ограничить список возможных значений, например, использование в поле таблицы "Пол" только буквы "М" и "Ж".

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

Примеры

Чтобы полноценно понять принцип работы функции, стоит рассмотреть на практике, как работает CREATE TABLE (SQL). Пример, приведенный ниже, создает таблицу, представленную на рисунке:

CREATE TABLE Custom
(ID CHAR(10) NOT NULL Primary key,
Custom_name CHAR(20),
Custom_address CHAR(30),
Custom_city CHAR(20),
Custom_Country CHAR(20),
ArcDate CHAR(20))

Как можно заметить, параметр возможного отсутствия значения в ячейке (NULL) можно опускать, так как он используется по умолчанию.