Views, Foreign Keys, Транзакции и Унаследяване

В този урок ще разгледаме няколко мощни конструкции от SQL със съответните PostgreSQL разширения, придаващи гъвкавост на управлението на базите данни и предотвратяващи загуба на данни. В примерите, които ще видим, ще използваме част от база данни на  ж.п. гара.

1. Views

Views (изгледите) са особено полезни когато имаме нужда да използваме често една и съща дълга заявка, комбинираща например две таблици. Вместо всеки път да пишем заявката отново и отново, можем просто да я дефинираме като view, което да извикваме вместо дългата заявка. Ето как можем да извличаме банковите координати на служителите на гарата като проверяваме дали съответният служител съществува в таблица „Служители“:

CODE

1 2 3 4

CREATE VIEW b_koordinati AS         SELECT ime, kod_banka, klon, n_smetka             FROM b_koordinati, slujiteli             WHERE id = id_nomer;

След това,когато искаме да получим дадената информация е достатъчно само да извикаме:

CODE

1	SELECT * FROM b_koordinati;

Views могат да бъдат използвани с DELETE и UPDATE. Не е необичайно да срещнем и views, построени на базата на други views.

2. Foreign Keys

Foreign Key ограничението указва, че стойностите в дадена колона (колони) трябва задължително да съвпадат със стойностите на колоната (колоните) на някой от редовете в друга таблица. Казваме, че това поддържа референтната интегрируемост между две свързани таблици. На SQL това би изглеждало така:

CODE

1 2 3 4 5 6 7 8

CREATE TABLE b_koordinati (         id_nomer INTEGER REFERENCES slujiteli (id),         ime VARCHAR(50),         kod_banka INTEGER NOT NULL,         klon INTEGER NOT NULL,         n_smetka INTEGER NOT NULL,         PRIMARY KEY (id_nomer) );

REFERENCES може да бъде употребено по два начина: с име на таблица и име на колона или само с име на таблица. Когато имената на колоните в две таблици между които установяваме зависимост съвпадат можем да не указваме име на колона. Горния пример можем да дефинираме и по следния начин:

CODE

1 2 3 4 5 6 7 8 9

CREATE TABLE b_koordinati (         id_nomer INTEGER,         ime VARCHAR(50),         kod_banka INTEGER NOT NULL,         klon INTEGER NOT NULL,         n_smetka INTEGER NOT NULL,         FOREIGN KEY (id_nomer) REFERENCES slujiteli (id),         PRIMARY KEY (id_nomer) );

Нека вземем отново за пример таблиците ни със служителите и банковите им координати. Искаме никой да не може да добавя банкови координати за лице, което не фигурира като служител на гарата. Също така ще искаме да изтриваме банковите координати на служителите при премахването на последните от таблицата employes.

CODE

1 2 3 4 5 6 7 8

CREATE TABLE b_koordinati (         id_nomer INTEGER REFERENCES employes (id) ON DELETE CASCADE,         ime VARCHAR(50),         kod_banka INTEGER NOT NULL,         klon INTEGER NOT NULL,         n_smetka INTEGER NOT NULL,         PRIMARY KEY (id_nomer) );

В случай, че не искаме да изтриваме банковите координати при изтриването на служител, просто заменяме CASCADE с RESTRICT. Казаното за ON DELETE важи с пълна сила и за ON UPDATE.
Нека при така създадените тааблици да се опитаме да въведем банкови координати на лице, което не съществува:

CODE

1	INSERT INTO b_koordinati VALUES ('67', 'Iwan Iwanow', '30003', '01841', '00050450365');

Ще получим следното съобщение за грешка:

CODE

1 2	ERROR:  insert or update on table "b_koordinati" violates foreign key constraint "b_koordinati_id_nomer_fkey" DETAIL:  Key (id_nomer)=(67) is not present in table "slujiteli".