Название: Базы данных. Концепция баз данных, реляционная модель данных, языки SQL и XML (Токмаков Г. П.)

Жанр: Информационные системы и технологии

Просмотров: 1401


3.3. создание таблиц

 

3.3.1. И НСТРУ КЦИЯ  CREATE  TABLE

 

Инструкция CREATE TABLE определяет новую таблицу и подготавливает ее к приему данных. Перед созданием таблиц базы данных необходимо продумать определения всех столбцов таблицы и характеристик каждого столбца (таких как тип, длина, обязательность для ввода, ограничения, накладываемые на зна- чения и т. д.), индексов, ограничений целостности по отношению к другим таб- лицам. Если при определении столбцов используются домены, то эти домены должны быть предварительно созданы оператором CREATE DOMAIN.

Создание таблицы базы данных осуществляется оператором

 

CREATE TABLE ИмяТаблицы

(столбец тип_данных ¦ домен [DEFAULT значение NOT NULL,] PRIMARY KEY (поле, ... ),

[CONSTRAINT <имя отношения>]

FOREIGN KEY (<список столбцов внешнего ключа>)

REFERENCES <имя таблицы‐предка> [<список столбцов таблицы‐предка>] [ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]

[ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}] UNIQUE (поле, ... ),

CHECK (условие_отбора) );

 

Опр е д е лени е  столбцов

 

Определения столбцов представляют собой заключенный в скобки список,

элементы которого отделены друг от друга запятыми:

– столбец − имя столбца, которое используется для ссылки на столбец в инст- рукциях SQL. Каждый столбец в таблице должен иметь уникальное имя, но в раз- ных таблицах имена столбцов могут совпадать;

– тип_данных – показывает, данные какого вида хранятся в столбце;

– домен − имя домена, т. е. ранее описанного типа столбца;

– DEFAULT − определяет значение, которое по умолчанию заносится в столбец,

ассоциированный с доменом, при создании записи таблицы;

– NOT NULL  указывает на то, что столбец обязательно должно содержать значение.

Ниже приведен пример инструкции CREATE TABLE для таблицы OFFISY из учебной базы данных.

 

CREATE TABLE OFFISY

( ID_OFC INTEGER NOT NULL,

CITY VARCHAR(15) NOT NULL, REGION VARCHAR(10) NOT NULL, MNGR INTEGER,

TARGET MONEY,

SALES MONEY NOT NULL);

Пр ед ло жения  PRIMARY  KEY  и  FORE IGN  KEY

 

Кроме определений столбцов таблицы, в инструкции CREATE TABLE указы- вается информация о первичном ключе таблицы и ее связях с другими табли- цами базы данных. Эта информация содержится в предложениях PRIMARY KEY и FOREIGN KEY.

Предложением PRIMARY KEY задается столбец или столбцы, которые обра- зуют первичный ключ и служат в качестве уникального идентификатора строк таблицы. СУБД автоматически следит за тем, чтобы первичный ключ был уни- кален. Кроме того в определениях столбцов первичного ключа должно быть указано,  что  они  не  могут  содержать  значения  NULL (имеют  ограничения NOT NULL).

В предложении FOREIGN KEY задается внешний ключ таблицы и определяет-

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

– CONSTRAINT определяет необязательное имя отношения; оно не используется в инструкциях SQL, но может появляться в сообщениях об ошибках и потребуется

в дальнейшем, если будет необходимо удалить внешний ключ;

– список столбцов внешнего ключа − определяет столбцы дочерней таблицы,

по которым строится внешний ключ; столбец или столбцы создаваемой таблицы,

которые образуют внешний ключ;

– имя таблицы‐предка − определяет таблицу, в которой описан первичный ключ. На этот ключ должен ссылаться внешний ключ дочерней таблицы для обес- печения ссылочной целостности; таблица, связь с которой создает внешний ключ;

это таблица-предок, а определяемая таблица в данном отношении является потом-

ком;

– список столбцов таблицы‐предка − необязателен при ссылке на первичный ключ родительской таблицы;

– ON DELETE или ON UPDATE − определяют способы изменения подчиненных записей дочерней таблицы при удалении или изменении поля связи в записи ро-

дительской таблицы. Перечислим эти способы:

    NO ACTION − запрет удаления/изменения родительской записи при на-

личии подчиненных записей в дочерней таблице;

         CASCADE − для оператора ON DELETE: при удалении записи родитель- ской таблицы происходит удаление подчиненных записей в дочерней таблице; для оператора ON UPDATE: при изменении поля связи в записи родительской таблицы происходит изменение на то же значение поля внешнего ключа у всех подчиненных записей в дочерней таблице;

    SET DEFAULT − в поле внешнего ключа у записей дочерней таблицы за-

носится значение этого поля по умолчанию, указанное при определении поля  (параметр DEFAULT); если  это  значение отсутствует в  первичном

ключе, инициируется исключение;

  SET NULL − в поле внешнего ключа заносится значение NULL.

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

 

CREATE TABLE ZAKAZY

(ID_ORDER INTEGER NOT NULL, DATE_ORDER DATE NOT NULL, ID_CLN INTEGER NOT NULL,

ID_SLZH INTEGER,

ID_MFR CHAR(3) NOT NULL, ID_PRD CHAR(5) NOT NULL, COUNT INTEGER NOT NULL, PRICE_ALL MONEY NOT NULL,

PRIMARY KEY (ID_ORDER), CONSTRAINT PLACEDBY

FOREIGN KEY (ID_CLN) REFERENCES CLIENTY ON DELETE CASCADE, CONSTRAINT TAKENBY

FOREIGN KEY (ID_SLZH) REFERENCES SLUZHASCHIE ON DELETE CASCADE, CONSTRAINT PLACEDBY

FOREIGN KEY (ID_MFR, ID_PRD) REFERENCES TOVARY ON DELETE CASCADE

)

 

На Рис. 3.1.  изображены три созданные этой инструкцией связи с присво- енными им именами. В общем случае связи следует давать имя, поскольку оно помогает лучше понять, какая именно связь создана внешним ключом. Напри- мер, каждый заказ делается клиентом, идентификатор которого находится в столбце CUST таблицы ZAKAZY. Связь с этим столбцом получила имя PLACEDBY.

 

 

 

 

 

 

 

ID_CLN

COMPANY

 

 

 

 

2103

«Базальт»

 

 

 

 

 

 

ID_SLZH

FAMILY

 

 

 

 

196

Федоров

 

 

 

 

 

 
ID_MFR          ID_PRD

 

УАЗ     2А34

 

 

№ ORDER

DATE_ORDER

ID_CLN

ID_SLZH

ID_MFR

ID_PRD

 

 

 

 

 

 

 

 

112961

17.12.99

2103

196

УАЗ

2А34

 

 

 

 

 

 

 

 

 

 

Рис. 3.1.          Имена связей в инструкции CREATE TABLE

 

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

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

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

 

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

 

Для того чтобы сервер автоматически проверял и поддерживал уникальность для некоторого поля, надо для данного поля ввести ограничитель на уникаль- ность. Для этого используется предложение UNIQUE инструкции CREATE TABLE.

Ниже приведена модифицированная инструкция CREATE TABLE для таблицы

OFFISY с включением в нее условием уникальности для столбца CITY:

 

CREATE TABLE OFFISY (

ID_OFC INTEGER NOT NULL,

CITY VARCHAR(15) NOT NULL, REGION VARCHAR(10) NOT NULL, MNGR INTEGER,

TARGET MONEY,

SALES MONEY NOT NULL, PRIMARY KEY (OFFICE), CONSTRAINT HASMGR

FOREIGN KEY (MNGR) REFERENCES SLUZHASCHIE ON DELETE SET NULL, UNIQUE (CITY) );

 

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

 

CREATE TABLE OFFISY

(ID_OFC INTEGER NOT NULL,

CITY VARCHAR(15) NOT NULL UNIQUE,

REGION VARCHAR(10) NOT NULL, MNGR INTEGER,

TARGET MONEY,

SALES MONEY NOT NULL, PRIMARY KEY (OFFICE),

CONSTRAINT HASMGR

FOREIGN KEY (MGR) REFERENCES SLUZHASCHIE ON DELETE SET NULL);

 

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

 

Когда создается таблица, то для каждого поля задается тип его значения. Это может быть INTEGER, CHAR и т. п. Тип определяет допустимое множество значений для данного поля. Но в некоторых случаях это множество значений много шире реально используемого множества. В SQL есть средства для более тонкого описания множества допустимых значений поля (в теории это множе-

ство называется доменом). Задать домен для того или иного поля можно с по- мощью ограничений на значение. Этот ограничитель указывается при создании таблицы. После типа поля или значения по умолчанию надо указать ключевое слово CHECK и логическое выражение в скобках

 

CREATE TABLE OFFISY (

ID_OFC INTEGER NOT NULL, CITY VARCHAR(15) NOT NULL, REGION VARCHAR(10) NOT NULL, MNGR INTEGER,

TARGET MONEY,

SALES MONEY NOT NULL,

PRIMARY KEY (OFFICE), CONSTRAINT HASMGR

FOREIGN KEY (MGR) REFERENCES SLUZHASCHIE ON DELETE SET NULL, CHECK (TARGET >= 0.00) );

 

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

 

3.3.2. И НСТРУ КЦИЯ  ALTER  TABLE

 

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

– добавить в каждую строку таблицы CUSTOMERS имя и номер телефона слу-

жащего компании клиента, через которого поддерживается контакт;

– добавить в таблицу PRODUCTS столбец с указанием минимального количества товара, чтобы иметь возможность предупреждения о том, что запас какого-либо товара стал меньше допустимого предела;

– сделать столбец REGION в таблице OFFISY внешним ключом для вновь соз-

данной таблицы REGIONS, первичным ключом которой является название региона;

– удалить определение внешнего ключа для столбца CUST таблицы ZAKAZY,

связывающего ее с таблицей CLIENTY, и заменить его определениями двух внеш-

них ключей, связывающих столбец CUST с двумя вновь созданными таблицами

CUST_INFO и ACCOUNT_INFO.

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

– добавить новый столбец в уже существующую и заполненную таблицу;

– удалить столбец из существующей таблицы;

– изменить значение по умолчанию для какого-либо столбца;

– добавить или удалить первичный ключ таблицы;

– добавить или удалить внешний ключ таблицы;

– добавить или удалить условие уникальности;

– добавить или удалить условие проверки для любого столбца или для табли-

цы в целом.

Однако оператором ALTER TABLE можно провести только одно из перечис-

ленных изменений, например, за один раз можно добавить 1 столбец.

 

Добавлени е  ст олбц а

 

Чаще всего инструкция ALTER TABLE применяется для добавления столбца в существующую таблицу. Предложение с определением столбца в инструкции ALTER TABLE имеет точно такой же вид, что и в инструкции CREATE TABLE, и вы- полняет ту же самую функцию. Новое определение добавляется в конец опре- делений столбцов таблицы, и в последующих запросах новый столбец будет крайним справа. СУБД обычно предполагает, что новый столбец во всех суще- ствующих строках содержит значения NULL. Поэтому нельзя объявлять новый столбец как NOT NULL.

Но если такое объявление все же необходимо, то необходимо определить этот столбец как NOT NULL WITH DEFAULT. При этом СУБД считает, что этот

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

Пример. Добавить контактный телефон и имя служащего компании кли-

ента в таблицу CLIENTY .

 

ALTER TABLE SLUZHASCHIE

ADD CONTACT_NAME VARCHAR(30)

 

ALTER TABLE SLUZHASCHIE

ADD CONTACT_PHONE CHAR(10)

 

Пример. Добавить в таблицу TOVARY столбец с данными о минимальном допустимом количестве товара на складе.

 

ALTER TABLE TOVARY

ADD MIN_QTY INTEGER NOT NULL WITH DEFAULT 0

 

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

 

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

 

С помощью инструкции ALTER TABLE можно удалить из существующей таб- лицы один или несколько столбцов, если в них больше нет необходимости. Ниже приведен пример удаления столбца QUOTA из таблицы SLUZHASCHIE:

 

ALTER TABLE SLUZHASCHIE DROP QUOTA

 

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

Эти проблемы в стандарте SQL2 решены так же, как и в случае инструкций

DELETE и UPDATE, с помощью правил удаления RESTRICT и CASCADE.

В случае применения правила RESTRICT инструкция ALTER TABLE завершится выдачей сообщения об ошибке и столбец не будет удален. Во втором случае внешние ключи, связанные с удаляемым столбцом будут удалены. Однако прави- ло CASCADE может вызвать целую «лавину» изменений, поэтому применять его следует с осторожностью. Лучше указывать правило RESTRICT, а связанные внеш- ние ключи обрабатывать с помощью дополнительных инструкций типа ALTER.

 

Изменение  пер в ичных  и  втор ичных  ключей

 

Инструкция ALTER TABLE чаще всего применяется для изменения или добав- ления определений первичных и вторичных ключей таблицы. Предложения, добавляющие определения первичного и внешнего ключей, являются точно та- кими же, как в инструкции CREATE TABLE, и выполняет те же функции.

Пример. Сделать столбец REGION таблицы OFFISY внешним ключом для вновь созданной таблицы REGIONS, первичным ключом которой является назва-

ние региона.

 

ALTER TABLE OFFISY

ADD CONSTRAINT IN REGION

FOREIGN KEY (REGION) REFERENCES REGIONS

 

Предложения, удаляющие первичный или внешний ключи, являются до- вольно простыми. Однако следует отметить, что удалить внешний ключ можно только тогда, когда создаваемая им связь имеет имя.

Пример. Изменить первичный ключ таблицы OFFISY.

 

ALTER TABLE SLUZHASCHIE DROP CONSTRAINT WORKSIN

FOREIGN KEY (REP_OFFICE) REFERENCES OFFISY

 

ALTER TABLE OFFISY

DROP PRIMARY KEY (OFFICE)

 

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