Структура языка SQL

 

2.2. Ограничения для доменов

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

CHECK {searchCandition}

При определении ограничений для отдельного столбца в конструкции CHECK можно ссылаться только на определяемый столбец. Например, для указания того, что столбец sex может содержать лишь два допустимых значения ( ' М ' и 'F'), следует объявить его таким образом:

Sех CHAR NOT NULL CHECK {sex IN CM1 , ' F 1 } )

Однако стандарт ISO позволяет  определять и более сложные домены, для чего предназначен второй механизм — использование оператора CREATE DOMAIN, имеющего следующий формат:

CREATE DOMAIN domainWame [AS] datatype

[DEFAULT defaultOption]

[CHECK (searcftCoriditicn)]

Каждому создаваемому домену присваивается  имя, задаваемое параметром domainName, тип данных, определяемый параметром dataType (см. раздел 6.1.2), необязательное значение по умолчанию, устанавливаемое параметром defaulCOption, и необязательный набор допустимых значений, определяемый в конструкции CHECK. Следует отметить, что приведенный формат оператора CREATE DOMAIN является неполным, однако его достаточно для демонстрации основных возможностей. Таким образом, в условиях предыдущего примера мы могли бы определить домен для столбца sex с помощью следующего оператора:

CREATE DOMAIN SexType AS CHAR

DEFAULT 'M'

CHECK (VALUE IN { ' M ' , ' F ' ) ) ;

В результате обработки этого оператора  в базе данных будет создан домен  под именем SexType, состоящий из двух отдельных символов, имеющих значения "М1 и 'F'. Теперь столбец sex в таблице Staff можно будет описать, используя домен SexType вместо определителя типа данных CHAR:

sex SexType NOT NULL

Значение параметра searchCondicion может  предусматривать обращение к справочной таблице. Например, можно создать домен BranchNumber (Номер отделения), который позволит вводить в соответствующие столбцы различных таблиц только те значения, которые уже существуют в столбце branchNo таблицы Branch. Для этой цели необходимо использовать следующий оператор:

CREATE DOMAIN BranchNumber AS VARCHAR(4)

CHECK (VALUE IN (SELECT branchNo PROM Branch));

Удаление доменов из базы данных выполняется с помощью оператора DROP DOMAIN, имеющего следующий формат:

DROP DOMAIN domainName [RESTRICT | CASCADE]

Спецификатор способа удаления домена (RESTRICT или CASCADE) определяет, какие  действия выполняются в базе данных, если домен в настоящее время  используется. Если задан спецификатор RESTRICT, а домен применяется в  существующей таблице, представлении или определении проверки (см. раздел 6.5.2), то операция удаления оканчивается неудачей. А если задан спецификатор CASCADE, то в любой столбец таблицы, который основан на определении домена, автоматически вносятся изменения таким образом, чтобы в нем применялся базовый тип данных домена, а любые ограничения или применяемые по умолчанию конструкции операторов для этого домена заменяются в случае необходимости ограничениями столбца или применяемой по умолчанию конструкцией оператора для соответствующего столбца.

 

2.3. Целостность сущностей

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

PRIMARY KEY(staffNo)

В случае составного первичного ключа, например, первичного ключа таблицы Viewing, состоящего из двух столбцов под именами clientNo и propertyNo, конструкция определения первичного ключа PRIMARY KEY будет иметь вид

PRIMARY KEY(clientNo, propertyNo)

Конструкция PRIMARY KEY может указываться  в определении таблицы только один раз. Однако существует возможность гарантировать уникальность значений и для любых альтернативных ключей таблицы, для чего предназначено ключевое слово UNIQUE. Кроме того, при определении столбцов альтернативных ключей рекомендуется использовать и спецификаторы NOT NULL. В каждой таблице может быть определено произвольное количество конструкций UNIQUE. База данных отвергает любые попытки выполнения операций INSERT или UPDATE, которые влекут за собой создание повторяющегося значения в любом потенциальном ключе (под этим подразумевается первичный или альтернативный ключ). Например, определение таблицы Viewing можно переписать следующим образом:

clientNo VARCHAR{5) NOT NULL,

propertyNo VARCHAR(S) NOT NULL,

UNIQUE (clientNo, propertyNo)

 

2.4. Ссылочная целостность.

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

Стандарт ISO предусматривает механизм определения внешних ключей с  помощью конструкции FOREIGN KEY операторов CREATE TABLE и ALTER TABLE. Например, для определения внешнего ключа branchNo в таблице PropertyForRent можно использовать следующуюконструкцию:

FOREIGN KEY(branchNo) REFERENCES Branch

Теперь система отклонит выполнение любых операторов INSERT или UPDATE, с помощью которых будет предпринята попытка создать в дочерней таблице значение внешнего ключа, не соответствующее одному из уже существующих значений потенциального ключа родительской таблицы. Действия системы, выполняемые при поступлении операторов UPDATE или DELETE, содержащих попытку обновить или удалить значение потенциального ключа в родительской таблице, которому соответствует одна или несколько строк дочерней таблицы, зависят от правил поддержки ссылочной целостности, указанных в конструкциях ON UPDATE и ON DELETE конструкции FOREIGN KEY. На тот случай, если пользователь предпринимает попытку удалить из родительской таблицы строку, на которую ссылается одна или несколько строк дочерней таблицы, в языке SQL предусмотрены следующие четыре допустимых варианта действий.

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

• SET NULL. Выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносятся значения NULL. Этот вариант применим только в том случае, если в определении столбца внешнего ключа отсутствует ключевое слово NOT NULL.

• SET DEFAULT. Выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносится значение, принимаемое по умолчанию. Этот вариант применим только в том случае если в определении столбца внешнего ключа присутствует ключевое слово DEFAULT и задано значение, используемое по умолчанию.

• NO ACTION. Операция удаления строки из родительской таблицы отвергается. Именно это значение используется по умолчанию в тех случаях, когда в описании внешнего ключа конструкция ON DELETE опущена. Те же правила применяются в языке SQL и тогда, когда значение потенциального ключа родительской таблицы обновляется. В случае использования правила CASCADE в столбцы внешнего ключа дочерней таблицы помещается новое, измененное значение потенциального ключа родительской таблицы. Аналогичным образом, обновления каскадно распространяются на другие таблицы, если их внешние ключи ссылаются на обновленные столбцы дочерней таблицы. Например, в таблице PropertyForRent столбец табельного номера работника staff No является внешним ключом, ссылающимся на таблицу staff. Для этого внешнего ключа можно установить правило удаления, указывающее, что в случае удаления записи о работнике из таблицы staff соответствующее значение в столбце staffNo таблицы PropertyForRent должно быть заменено значением NULL:

FOREIGN KEY (staffNo} REFERENCES Staff ON DELETE SET NULL

Аналогичным образом, столбец с  номером владельца объекта недвижимости ownerNo таблицы PropertyForRent является внешним ключом, связывающим ее с таблицей PrivateOwner. Можно установить правило обновления, указывающее, что в случае изменения номера владельца в таблице PrivateOwner соответствующие значения в столбце ownerNo таблицы PropertyForRent также должны быть заменены новым значением:

FOREIGN KEY {ownerNo) REFERENCES PrivateOwner ON UPDATE CASCADE

 

2.5. Требования данного  предприятия

Обновления данных в таблицах могут  быть ограничены существующими в  данной организации требованиями (которые принято также называть деловым регламентом), установленными в отношении выполнения вручную операций, связанных с внесением изменений в информацию. Например, в компании DreamHome существует правило, ограничивающее количество сдаваемых в аренду объектов, за которые может отвечать один работник, причем верхний предел установлен равным ста объектам. Стандарт ISO позволяет реализовать деловой регламент предприятий либо с помощью конструкций CHECK и ключевого слова UNIQUE в операторах CREATE TABLE и ALTER TABLE, либо с помощью оператора CREATE ASSERTION. Использование конструкции CHECK и ключевого слова UNIQUE уже обсуждалось выше в этом разделе. Оператор CREATE ASSERTION предназначен для введения ограничений целостности данных, которые непосредственно не связаны с определениями таблиц. Этот оператор имеет следующий формат:

CREATE ASSERTION ArsercicnNane

CHECK   (searchCondion);

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

CHEATS ASSERTION Staff NotHandlingTooMuch

CHECK (NOT EXISTS (SELECT staff No

FROM PropertyForRent

GROUP BY staffNo

HAVING COUNT(*} > 100))

В следующем разделе показано, как  используются эти средства обеспечения  целостности в операторах CREATE TABLE и ALTER TABLE.

 

3. Определение данных.

Язык определения данных SQL DDL (Data Definition Language) позволяет создавать  и уничтожать такие объекты базы данных, как схемы, домены, таблицы, представления и индексы. В настоящем  разделе кратко рассматриваются  способы создания и удаления схем, таблиц и индексов, а в следующем разделе показано, как создавать и удалять представления. Стандарт ISO предусматривает также возможность создания наборов символов, схем сортировки и преобразования. Но в настоящей книге эти объекты базы данных не рассматриваются. Ниже перечислены основные операторы языка определения данных SQL.

CREATE SCHEMA

CREATE DOMAIN

CREATE TABLE

CREATE VIEW

ALTER DOMAIN

ALTER TABLE

DROP SCHEMA

DROP DOMAIN

DROP TABLE

DROP VIEW

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

CREATE INDEX DROP INDEX

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

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

В различных СУБД процедура создания баз данных существенно отличается. В многопользовательских системах право создания баз данных обычно закрепляется только за администратором базы данных (АБД). В однопользовательских системах предусмотренная по умолчанию база данных может быть создана непосредственно в процессе установки и настройки параметров самой СУБД, а другие базы данных создаются самим пользователем по мере необходимости. Стандарт ISO не определяет, как должны создаваться базы данных, поэтому в каждом из диалектов языка SQL обычно используется собственный подход. В соответствии со стандартом ISO, таблицы и другие объекты базы данных существуют в некоторой среде (environment). Помимо всего прочего, каждая среда состоит из одного или нескольких каталогов (catalog), а каждый каталог — из набора схем (schema). Схема представляет собой именованную коллекцию объектов базы данных, которые определенным образом связаны друг с другом (все объекты в базе данных должны быть описаны в той или иной схеме). Объектами схемы могут быть таблиц, представления, домены, утверждения, сопоставления, толкования и наборы символов. Все объекты схемы имеют одного и того же владельца и множество общих значений, применяемых по умолчанию.

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

CREATE. SCHEMA [nаше | AUTHORIZATION  Creator-Identifier]

Таким образом, если создателем схемы  под именем SqlTests является пользователь Smith, то данный оператор будет выглядеть  следующим образом:

CREATE SCHEMA SqlTests AUTHORIZATION Smith;

В стандарте ISO также указано, что  должна существовать возможность определить в рамках данного оператора диапазон средств, доступных пользователям создаваемой схемы. Однако конкретные способы определения подобных привилегий в разных СУБД различаются. Схема удаляется с помощью оператора DROP SCHEMA, который имеет следующий формат:

DROP SCHEMA Name [ RESTRICT | CASCADE]

Если указано ключевое слово RESTRICT (именно оно принимается по умолчанию), схема должна быть пустой, иначе выполнение операции будет отменено. Если указано ключевое слово CASCADE, при выполнении оператора будут автоматически удалены все связанные с удаляемой схемой объекты, причем в порядке, указанном выше. Если одна из этих операций удаления будет завершена неудачно, выполнение всего оператора DROP SCHEMA будет отменено. Общий эффект от выполнения оператора DROP SCHEMA с параметром CASCADE может затронуть значительную часть базы данных, поэтому подобные операторы должны вводиться с исключительной осторожностью.