Лекция 3 Домены Ограничения на значения столбцов Создание, изменение и удаление таблиц Ключи и ссылочная целостность Защита таблиц

Домен Домен - это заранее созданное описание столбца. Домены: имеют уникальные имена; хранятся в БД; используются вместо типов столбцов; обеспечивают унификацию типов данных в разных столбцах, в разных таблицах

Пример домена CREATE DOMAIN firm_contact_names as varchar(40) character set win1251 collate PXW_CYR CREATE TABLE firms(..., contact_name_1 firm_contact_names, contact_name_2 firm_contact_names,..., )

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

Пример ограничений CREATE TABLE MyTable ( /* больше 0 */ col1 int check (col1>=0), /* от 100 до 299 */ col2 int check (col2 between 100 and 299), /* строка, заканчивающаяся на «у.е.» */ col3 char(40) check (col3 like '%у.e.'), /* должно заканчиваться на «%» */ col4 char(40) check (col4 like '%\%'),

Пример ограничений /* то же, но с другим escape-символом */ col4_1 char(20) check ( col4_1 like '%!%' escape '!'), /* значение содержится в таблице */ col5 int check (col5 in (SELECT BookID FROM Books)),

Пример ограничений /* значение должно быть больше значения, */ /* возвращаемого генератором GEN_BOOKS */ col6 int check (col6 > Gen_ID(GEN_BOOKS, 0)), /* строка должна начинаться с пробела */ col7 char(5) check (col7 starting with ' ') )

Изучить самостоятельно Полное описание CHECK() Работу функции LIKE() Работу с генераторами

Создание таблиц CREATE TABLE имя_таблицы [INTERNAL [FILE] "имя_файла"] ( опр_столбца [, опр_столбца [,...]] )

Определение столбца опр_столбца = имя_столбца { тип_данных| COMPUTED [BY] (выражение)| домен } [DEFAULT {литерал|NULL|USER}] [NOT NULL] [ограничение столбца] [COLLATE способ_сортировки]

Определение столбца COMPUTED [BY] (выражение) - для определения столбца вычисляемых значений; INTERNAL [FILE] "имя_файла - для создания таблицы вне файла БД (внешняя таблица); Внешние таблицы применяются только для обмена данным между разными БД.

Для внешних таблиц InterBase: только вставляет записи; не изменяет записи; не удаляет записи; не создаёт индексы; не удаляет файл внешней таблицы оператором DROP TABLE если создать новую внешнюю таблицу для существующего файла, то данные из него будут доступны только на чтение

Вычисляемые столбцы Вычисление выражения выполняет сервер. Можно добавить не только при создании таблицы: ALTER TABLE MovedBook ADD Summa COMPUTED (MovedQuantity*MovedPrice)

Ключи и ссылочная целостность

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

Первичный ключ - 1 столбец CREATE TABLE MyTable ( Col1 INT NOT NULL PRIMARY KEY, Col2 VARCHAR(20) )

Первичный ключ - несколько столбцов CREATE TABLE MyTable ( Col1 INT NOT NULL, Col2 VARCHAR(20) NOT NULL, PRIMARY KEY (Col1, Col2) )

Внешние ключи Используются для обеспечения ссылочной целостности; Ссылочную целостность отслеживает сервер БД InterBase; Создаются в дочерних таблицах; Для создания используется спецификатор FOREIGN KEY.

Синтаксис FOREIGN KEY FOREIGN KEY (список_столбцов_доч_табл) REFERENCES имя_род_таблицы [(список_столбцов_род_табл)] [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

Синтаксис FOREIGN KEY список_столбцов_род_табл - ключевые столбцы для связи таблиц. Можно не указывать, если связь таблиц устанавливается по первичному ключу; ON... - что выполнять серверу в случае события; DELETE или UPDATE - возможные события, влияющие на целостность данных.

Действия при нарушении ссылочной целостности NO ACTION - блокировать действие, если в дочерней таблице есть записи, ссылающиеся на удалённое или изменённое значение первичного ключа в родительской таблице;

Действия при нарушении ссылочной целостности CASCADE - выполнить каскадные изменения в дочерней таблице: –удалить записи, ссылающиеся на удалённое значение первичного ключа в родительской таблице или –изменить значения в полях вторичного ключа в соответствие с новым значением первичного ключа

Действия при нарушении ссылочной целостности SET DEFAULT - установить значение вторичного ключа по умолчанию; SET NULL - установить значение NULL.

Пример обеспечения ссылочной целостности

Пример ссылочной целостности: создание таблиц БД содержит 2 таблицы (Р и С): CREATE TABLE P ( P_ID INT NOT NULL PRIMARY KEY, P_Other INT )

Пример ссылочной целостности: создание таблиц CREATE TABLE C ( C_Number INT, C_Other INT, FOREIGN KEY (C_Number) REFERENCE P ON UPDATE CASCADE ON DELETE NO ACTION )

Пример ссылочной целостности: исходное состояние таблиц P C

Пример ссылочной целостности: изменение первичного ключа UPDATE P SET P_ID=34 WHERE P_ID=1

Пример ссылочной целостности: состояние после изменения первичного ключа P C

Пример ссылочной целостности: возможности удаления Если в родительской таблице объявлена ссылочная целостность ON DELETE NO ACTION, то удаление записи из родительской таблицы возможно только, если: –в дочерней таблице нет записей, ссылающихся на удаляемое из родительской таблице значение первичного ключа или –для родительской таблицы определён триггер события BEFORE DELETE, в котором удаляются все записи, ссылающиеся на удаляемое значение первичного ключа.

Пример создания триггера BEFORE DELETE CREATE TRIGGER BEFORE_DELETE_P FROM P BEFORE DELETE AS BEGIN DELETE FROM C WHERE C_Number=OLD.P_ID END

Именование ссылочной целостности Именование ссылочной целостности используется для удаления или изменения ссылочной целостности без переопределения всей таблицы; Для именования ссылочной целостности используется спецификатор CONSTRAINT

Пример именования ссылочной целостности CREATE TABLE C ( C_Number INT, C_Other INT, CONSTRAINT Cons_C /* имя */ FOREIGN KEY (C_Number) REFERENCE P ON UPDATE CASCADE ON DELETE NO ACTION )

Изменение и удаление таблиц

Изменение таблицы Осуществляется операторами: ALTER TABLE … ADD для добавления столбцов, индексов и ссылочных целостностей (терпит неудачу, если текущее состояние таблиц нарушает целостность) ALTER TALBE … DROP для удаления столбцов, именованных индексов и именованных ссылочных целостностей

Примеры изменения таблицы Добавить новую колонку ALTER TABLE P ADD New_Column VARCHAR(50) Удалить ссылочную целостность ALTER TABLE C DROP Cons_C

Изменение атрибутов столбца 1.Создаём временный столбец с аналогичными атрибутами: ALTER TABLE P ADD Temp VARCHAR(50); 2.Копируем данные во временный столбец: UPDATE P SET Temp=New_Column

Изменение атрибутов столбца 3.Удаляем изменяемый столбец: ALTER TABLE P DROP New_Column; 4.Создаём новый столбец с нужными атрибутами: ALTER TABLE P ADD New_Column VARCHAR(70);

Изменение атрибутов столбца 5.Копируем данные из временного столбца в новый столбец: UPDATE P SET New_Column=Temp; 6.Удаляем временный столбец: ALTER TABLE P DROP Temp;

Удаление таблицы Выполняется оператором DROP TABLE MyTable Вместе с таблицей удаляются навсегда данные, индексы, ссылочные целостности, ограничения. Удаление родительской таблицы блокируется, если объявлена ссылочная целостность ON DELETE NO ACTION

Защита таблиц InterBase Для защиты таблицы БД от несанкционированного доступа InterBase применяет метод прав и ролей; После создания таблицы все права на имеет создатель (собственник) и системный администратор SYSDBA.

Передача прав Создатель (собственник) или системный администратор могут передать все или часть прав другому пользователю оператором GRANT : GRANT набор_прав ON имя_таблицы TO пользователи

Права All - все права, кроме Execute и Role ; Select - чтение данных; Delete - удаление данных; Insert - добавление данных; Update - изменение существующих данных; Execute - вызов хранимой процедуры; References - установление ссылочной целостности; Role - все права для указанной роли.

Примеры передачи прав Разрешить чтение таблицы books пользователю Ivanov : GRANT select ON books TO Ivanov; Разрешить все операции над таблицей firms пользователям Ivanov, Petrov, Sidorov : GRANT all ON firms TO Ivanov, Petrov, Sidorov

Примеры передачи прав Права на добавление и выборку: GRANT insert, select ON movebook TO Petrov; Передача прав любому пользователю: GRANT all ON books TO PUBLIC; Передача прав только на столбцы: GRANT update ON Firms(FAddress,FPhone,F ) TO Sidorov;

Лишение прав Выполняется оператором REVOKE: REVOKE select ON books TO Ivanov;

Роли Роль - это именованный набор прав. Создание роли: CREATE ROLE имя_роли; например: CREATE ROLE customer; Передача прав роли: GRANT all ON movebook TO customer; Назначение ролей пользователям: GRANT customer TO Ivanov, Petrov