Изменение и удаление таблиц и связей между ними. Генераторы.
Соединимся с БД как SYSDBA и изменим структуру таблицы Tovar
Рис.16 Системные таблицы БД
Удалим первичный ключ, добавим новые поля: Id_Tovar ( INTEGER ) - будущий PK, Tov_Creat ( varchar(10) ), Tov_Tstamp ( TIMESTAMP ).
Как удалить Первичный ключ - операции по его удалению нет в ALTER TABLE. Давайте посмотрим, где он описан в нашей таблице - в закладке Метадата (это понятно, но бесполезно) и при внимательном поиске, нажав кнопку Show Indexes на закладке Свойства увидим RDB$PRIMARY1.
Рис.17 Список индексов для таблицы TOVAR
Судя по названию этого ключа - это системный объект. Где же он расположен и как его удалить? В Меню выберем View и щелкнув включим флажок System Data. Теперь, открыв Таблицы, мы сразу и не увидим нашей TOVAR. Перед нами длинный список системных таблиц (см. рисунок 18). Нам нужна RDB$INDIСES.В самом ее конце мы увидим:
Рис.18 Список системных таблиц БД
Теперь ясно, что удалять нужно данные из системной таблицы. Попытка выполнить удаление ПК не удается из-за нарушения системной целостности данных. В дальнейшем будем создавать только явно поименованные объекты
Чтобы удалить первичный ключ из системных таблиц, нужно найти его имя. Чтобы понять, где следует его искать посмотрим ВНИМАТЕЛЬНО справку по команде CREATE TABLE.
Полный синтаксис оператора:
CREATE TABLE TOVAR
( имя_поля1 тип,
……………….
имя_поляN тип,
<tconstraint> );
где <tconstraint> = [ CONSTRAINT имя_ограничения ]
{{PRIMARY KEY или UNIQUE} (col [, col …])
| FOREIGN KEY (col [, col …]) REFERENCES other_table
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
| CHECK (<search_condition>)}
Ищем в системных таблицах, у которых в имени есть слово CONSTRAINT. Интересующее нас ограничение находится в таблице RDB$RELATION_CONSTRAINTS и имеет имя INTEG_3. Выполним оператор:
ALTER TABLE TOVAR
DROP CONSTRAINT Integ_3
Получилось. Ключ удален. Возьмем себе за правило: Назначать ограничения, в том числе и Первичные Ключи с явными, а не системными именами.
Удалим из таблицы ненужную запись на английском языке…
DELETE FROM TOVAR
WHERE TOV_EDIZM = 'kg'
И добавим следующие поля:
ALTER TABLE TOVAR
ADD Id_Tov integer NOT NULL
ALTER TABLE TOVAR
ALTER Id_Tov POSITION 0, а также
ALTER TABLE TOVAR
ADD TOV_CREAT VARCHAR(10) CHARACTER SET WIN1251 DEFAULT USER NOT NULL,
ADD TOV_TSTAMP DATE DEFAULT 'NOW' NOT NULL
Рис.19 Результат заполнения таблиц БД
После этого ввод данных из консоли не получается, но при помощи операторов - все замечательно.
В таблице фиксируется кто и когда добавлял в нее записи.
Создаем новый Первичный ключ по полю Id_Tov:
ALTER TABLE TOVAR
ADD CONSTRAINT Tov_PK PRIMARY KEY (Id_Tov)
Создаем новый альтернативный ключ по полям Tov_NAME, TOV_EDIZM:
ALTER TABLE TOVAR
ADD CONSTRAINT Tov_UK UNIQUE (Tov_NAME,TOV_EDIZM)
Создание автоинкрементных полей. Генераторы.
Создадим “Автоинкрементное поле” на основе поля Id_Tov типа INTEGER и Генератора. Его создание позволяет автоматически задавать уникальные значения полям создаваемых записей.
Создаем генератор:
CREATE GENERATOR TOV_GEN
Задаем его начальное значение:
SET GENERATOR TOV_GEN TO 2
Для того, чтобы он “заработал” нужно создать триггер
SET TERM !! ;
CREATE TRIGGER Tov_ID_BI FOR TOVAR
BEFORE INSERT
AS BEGIN
NEW.ID_Tov = GEN_ID(Tov_GEN, 1);
END
!!
SET TERM ; !!
Выполнив этот SQL-скрипт, посмотрите - на закладке Свойства для таблицы TOVAR назначен триггер, срабатывающий перед вставкой новых записей (рисунок 20).
Рис.20 Перечень триггеров для таблицы TOVAR
Для чего нужны SET TERM !!
При передаче серверу команда SQL по умолчанию завершается “;”. Но синтаксис операторов внутри хранимых процедур и триггеров также требует “;” в конце. Поэтому вначале мы переопределяем символ, завершающий оператор SQL на последовательность “!!”. Теперь он завершает команду ввода, а “;” используется для окончания предложений операторов языка SQL внутри процедур. После текста триггера идет “!!”, чтобы завершить команду SQL. Затем мы восстанавливаем значение символа “;”.
О типах триггеров BEFORE и AFTER INSERT, UPDATE, DELETE, о смысле NEW.ID_Tov мы говорили на лекции. GEN_ID(Tov_GEN, 1) - функция, увеличивающая значения генератора Tov_GEN на 1. А могла бы и на 10, 25 и т.д.
Аналогично создайте таблицу покупателей POKUP с целым ПК Id_Pokup и генератором Pok_GEN и таблицу TEL_POKUP с ПК (Telef,Pok_Id):
CREATE TABLE POKUP (
ID_POKUP INTEGER NOT NULL,
POK_NAME VARCHAR(40) CHARACTER SET WIN1251 NOT NULL,
POK_ADDRESS VARCHAR(50) CHARACTER SET WIN1251 NOT NULL,
POK_CITY INTEGER,
POK_CREAT VARCHAR(10) CHARACTER SET WIN1251 DEFAULT USER NOT NULL,
POK_TSTAMP DATE DEFAULT 'NOW' NOT NULL,
CONSTRAINT POK_PK PRIMARY KEY (ID_POKUP),
CONSTRAINT POK_UK UNIQUE (POK_NAME, POK_ADDRESS)
);
CREATE TABLE TEL_POKUP (
ID_POKUP INTEGER NOT NULL,
PHONE_POK VARCHAR(10) NOT NULL,
POK_CREAT VARCHAR(10) CHARACTER SET WIN1251 DEFAULT USER NOT NULL,
POK_TSTAMP DATE DEFAULT 'NOW' NOT NULL,
CONSTRAINT PHONE_POK_PK PRIMARY KEY (ID_POKUP, PHONE_POK)
);
Таким образом определенная таблица TEL_POKUP будет дочерней по отношению к таблице POKUP. Для каждого из покупателей может быть несколько телефонов. Одинаковый телефон может быть у разных покупателей.
Создаем генераторы и триггера для таблицы ПОКУПАТЕЛИ:
CREATE GENERATOR POK_GEN;
SET GENERATOR POK_GEN TO 0;
SET TERM !! ;
CREATE TRIGGER Pok_ID_BI FOR POKUP
BEFORE INSERT
AS BEGIN
NEW.ID_POKUP = GEN_ID(POK_GEN, 1);
END
!!
SET TERM ; !!
В таблице ТЕЛЕФОНЫ у нас не будет автоинкрементных полей.
Установим связи таблиц Родительская – Дочерняя между POKUP и TEL_POKUP:
ALTER TABLE TEL_POKUP
ADD CONSTRAINT POKUP_PHONE_FK FOREIGN KEY (Id_POKUP) REFERENCES POKUP
ON DELETE CASCADE
ON UPDATE CASCADE;
Пробуем добавит запись в таблицу ПОКУПАТЕЛЬ.
INSERT INTO POKUP (POK_Name,POK_ADDRESS,POK_City)