Values('Иванов и.И.','Пушкинская 27 кв 1',1)
INSERT INTO TEL_POKUP (Id_POKUP,PHONE_POK)
values(1,'726-14-87')
INSERT INTO TEL_POKUP (Id_POKUP,PHONE_POK)
values(1,'64-18-27')
Рис. 21 Заполнение таблицы телефонов покупателей
Рис.22 Список покупателей
Попробуем теперь удалить запись о покупателе из таблицы POKUP:
DELETE FROM POKUP
WHERE Id_POKUP = 1
Как и следовало ожидать, исчезли записи как о покупатели, так и все связанные с ним телефоны в справочнике телефонов (см. рисунок 23).
|
|
|
Рис.23 Пример каскадного удаления
Создадим теперь таблицу-справочник Городов, родительскую для таблицы Покупателей:
CREATE TABLE CITY (
ID_CITY INTEGER NOT NULL,
CITY_NAME VARCHAR(15) CHARACTER SET WIN1251 NOT NULL,
CONSTRAINT "CITY_PK" PRIMARY KEY (ID_CITY)
);
CREATE GENERATOR CITY_GEN;
COMMIT;
SET GENERATOR CITY_GEN TO 0;
COMMIT;
SET TERM !! ;
CREATE TRIGGER CITY_ID_BI FOR CITY
BEFORE INSERT
AS BEGIN
NEW.ID_CITY = GEN_ID(CITY_GEN, 1);
END
!!
SET TERM ; !!
Добавим запись для города Одесса
Установим связи таблиц Родительская – Дочерняя между CITY и POKUP:
ALTER TABLE POKUP
ADD CONSTRAINT CITY_POKUP_FK FOREIGN KEY (POK_CITY) REFERENCES CITY
ON DELETE NO ACTION
ON UPDATE NO ACTION;
Снова добавим запись в таблицу POKUP:
INSERT INTO POKUP (POK_Name,POK_ADDRESS,POK_City)
Values('Иванов и.И.','Пушкинская 27 кв 1',1)
и попробуем удалить запись о городе Одесса из таблицы CITY.
Мы получили сообщение о невозможности такой операции из-за ограничения по FOREIGN KEY:
Рис.24 Нарушение ссылочной целостности
Удалить город можно будет только тогда, когда в таблице покупателей не будет записей, связанных с этим городом.
Настало время создания таблиц НАКЛАДНАЯ и ОТПУСК-ТОВАРА-СО-СКЛАДА.
Создаем их с автоинкрементными Первичными ключами
CREATE TABLE NAKL (
ID_NAKL INTEGER NOT NULL,
NAKL_DATE DATE DEFAULT 'NOW' NOT NULL,
POK_Id INTEGER NOT NULL,
"POK_CREAT" VARCHAR(10) CHARACTER SET WIN1251 DEFAULT USER NOT NULL,
"POK_TSTAMP" DATE DEFAULT 'NOW' NOT NULL,
CONSTRAINT NAKL_PK PRIMARY KEY ("ID_NAKL")
);
CREATE GENERATOR NAKL_GEN;
COMMIT;
SET GENERATOR NAKL_GEN TO 0;
COMMIT;
SET TERM !! ;
CREATE TRIGGER NAKL_ID_BI FOR NAKL
BEFORE INSERT
AS BEGIN
NEW.ID_NAKL = GEN_ID(NAKL_GEN, 1);
END !!
SET TERM ; !!
ALTER TABLE NAKL
ADD CONSTRAINT POKUP_NAKL_FK FOREIGN KEY (POK_ID) REFERENCES POKUP
ON DELETE NO ACTION
ON UPDATE NO ACTION;
CREATE TABLE OTPUSK
(
NAKL_ID INTEGER NOT NULL,
TOV_ID INTEGER NOT NULL,
"OTP_CREAT" VARCHAR(10) CHARACTER SET WIN1251 DEFAULT USER NOT NULL,
"OTP_TSTAMP" DATE DEFAULT 'NOW' NOT NULL,
CONSTRAINT OTP_PK PRIMARY KEY (NAKL_ID, TOV_ID)
);
ALTER TABLE OTPUSK
ADD CONSTRAINT NAKL_OTPUSK_FK FOREIGN KEY (NAKL_ID) REFERENCES NAKL
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE OTPUSK
ADD CONSTRAINT TOV_OTPUSK_FK FOREIGN KEY (TOV_ID) REFERENCES TOVAR
ON DELETE NO ACTION
ON UPDATE NO ACTION;
Теперь можно создать запись в таблице ОТПУСК_ТОВАРА только если такой товар и накладная существуют, удалить товар без удаления всех записей о его отпуске нельзя (неизвестна будет цена), а вот при удалении накладной все записи об отпущенном товаре будут удалены.
Вставим запись в таблицы НАКЛАДНАЯ и ОТПУСК:
INSERT INTO NAKL (POK_ID) VALUES(2);
INSERT INTO OTPUSK (NAKL_ID,TOV_ID,KOLVO) VALUES(1,1,10);
INSERT INTO OTPUSK (NAKL_ID,TOV_ID,KOLVO) VALUES(1,4,8);
INSERT INTO OTPUSK (NAKL_ID,TOV_ID,KOLVO) VALUES(1,5,12);
INSERT INTO OTPUSK (NAKL_ID,TOV_ID,KOLVO) VALUES(1,6,25);
Посмотрим на наши данные, выполнив запрос:
select T.TOV_NAME, T.TOV_CENA, O.KOLVO, T.TOV_CENA*O.KOLVO AS SUMMA
FROM TOVAR T, OTPUSK O, NAKL N
WHERE (N.Id_NAKL = O.NAKL_Id) AND (T.Id_TOV = O.TOV_Id)
Рис.25 Обращение к вычисляемым полям
На базе этого запроса создадим ПРОСМОТР:
CREATE VIEW PO_NAKL (TOV_NAME, TOV_CENA, KOLVO, NAKL_ID, SUMMA)
AS
SELECT TOVAR.TOV_NAME, TOVAR.TOV_CENA, OTPUSK.KOLVO, OTPUSK.NAKL_ID,
TOVAR.TOV_CENA*OTPUSK.KOLVO AS SUMMA
FROM TOVAR, OTPUSK
WHERE (TOVAR.Id_TOV = OTPUSK.TOV_Id);
Тогда мы всегда можем вызвать:
SELECT * FROM PO_NAKL WHERE NAKL_ID = 1
Аналогично:
CREATE VIEW POK_TEL AS
SELECT P.Id_POKUP, P.POK_NAME, C.CITY_NAME, T.PHONE_POK
FROM TEL_POKUP T, POKUP P, CITY C
WHERE (T.Id_POKUP = P.Id_POKUP) AND (C.Id_CITY=P.POK_CITY)
SELECT * FROM POK_TEL WHERE Id_POKUP = 2
Поскольку просмотры, выбирающие данные из нескольких таблиц не позволяют изменять данные и показывают только то, что мы считаем нужным показать, именно на них мы и будем давать право просмотра пользователям, не входящим в Роль Student_Info.