Bazy Danych

Spis treści



Bazy danych wstęp



Geneza relacyjnych baz danych



Podstawy relacyjnych baz danych



Podstawy-SQL-1



Podstawy-SQL-2



Podstawy-SQL-3



Operacje na danych



Transakcje i blokady



Normalizacja

UPDATE - aktualizacja danych w tabeli

Składnia:

UPDATE nazwa_tabeli

SET nazwa_kolumny=wartość

WHERE warunek

Przykład:

UPDATE klienci

SET miasto=‘Warszawa’, kod=’04-899’

WHERE nazwa=‘ACTION S.A.’

DELETE FROM - usuwanie wierszy z

tabeli

Składnia:

DELETE FROM nazwa_tabeli

WHERE warunek

Przykład

DELETE FROM klienci

WHERE nazwa=‘ACTION S.A.’

TRUNCATE - usuwanie wszystkich

wierszy



Instrukcja DELETE działa na pojedynczych
wierszach. Aby usunąć wszystkie wiersze z
tabeli należy użyć instrukcję TRUNCATE

TRUNCATE TABLE klient

CREATE DATABASE — tworzenie nowej

bazy

Składnia:
CREATE DATABASE nazwa
[ WITH [ LOCATION = ’ścieżka’ ]
[ TEMPLATE = template ]
[ ENCODING = kodowanie ] ]

Przykład:
CREATE DATABASE klienci;

DROP DATABASE – usunięcie bazy

danych

Składnia:

DROP DATABASE nazwa

Przykład:

DROP DATABASE klienci;

VACUUM – porządkowanie bazy i analiza

Składnia:
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE

[ table [ (column [, ...] ) ] ]

Przykład:
VACUUM VERBOSE ANALYZE baza;

CREATE USER – definiowanie nowego

użytkownika

Składnia:
CREATE USER nazwa [ [ WITH ] opcje [ ... ] ]
Gdzie opcje:
SYSID uid
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD

CREATE USER – definiowanie nowego

użytkownika

Przykład:

CREATE USER Jan;

CREATE USER Jan WITH PASSWORD ’jw8s0F4’;

CREATE USER Jan WITH PASSWORD ’jw8s0F4’
VALID UNTIL ’Jan 1 2004’;

CREATE USER Jan WITH PASSWORD ’jw8s0F4’ CREATEDB;

ALTER USER – zmiana uprawnień

użytkownika

Przykład:
ALTER USER Kowalski WITH PASSWORD ’hu8jmn3’;
ALTER USER Nowak CREATEUSER CREATEDB;

GRANT – nadawanie uprawnień

Składnia:
GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE

| REFERENCES | TRIGGER } [,...] | ALL PRIVILEGES] }
ON [ TABLE ] objectname [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...]

Przykład:
GRANT SELECT ON klienci TO student;
GRANT ALL PRIVILEGES ON klienci TO student;

REVOKE – odbieranie uprawnień

Składnia:
REVOKE { { SELECT | INSERT | UPDATE | DELETE | RULE

| REFERENCES | TRIGGER } [,...] | ALL ] }
ON [ TABLE ] object [, ...]
FROM { username | GROUP groupname | PUBLIC } [, ...]

Przykład:
REVOKE SELECT ON klienci FROM student;
REVOKE ALL PRIVILEGES ON klienci FROM student;

CREATE GROUP – tworzenie grupy

Składnia:
CREATE GROUP name [ [ WITH ] option [ ... ] ]
option:
SYSID gid
| USER username [, ...]

Przykład:
CREATE GROUP marketing WITH USER Kowalski,

Kwiatkowski;

ALTER GROUP – Zmiana grupy

Składnia:
ALTER GROUP name ADD USER username [, ... ]
ALTER GROUP name DROP USER username [, ... ]

Przykład:
ALTER GROUP marketing ADD USER Nowak,Jackowski;
ALTER GROUP marketing DROP USER Nowak,Jackowski;

Kolumna oid



Kolumna

oid

– kiedy wprowadzamy dane baza

odpowiada na dwoma liczbami:



ukrytym zazwyczaj numerem referencyjnym (oid), który
PostgreSQL zapisuje dla każdego wiersza w kolumnie



liczbą wprowadzanych wierszy



Numer

oid

czyni wiersz unikalnym ale w poprawnie

zaprojektowanej bazie danych nie ma potrzeby go
używać.

Przykład:

SELECT oid, imie, nazwisko FROM klienci;

CREATE SEQUENCE - tworzenie

sekwencji

Składnia:
CREATE [ TEMPORARY | TEMP ] SEQUENCE seqname
[ INCREMENT increment ]
[ MINVALUE minvalue ] [ MAXVALUE maxvalue ]
[ START start ] [ CACHE cache ] [ CYCLE ]

Przykład:
CREATE SEQUENCE kod START 100;
SELECT nextval(’kod’);
nextval
-------
101

CREATE SEQUENCE - tworzenie

sekwencji



Sekwencja może być tworzona ręcznie (poprzedni slajd) lub
automatycznie. Jeżeli utworzymy w tablicy kolumnę typu serial
to PostgreSQL poinformuje nas o automatycznym utworzeniu
sekwencji. Definiując tabele klient utworzyliśmy kolumnę
klient_id jako serial. PostgreSQL utworzył sekwencję. Definicja
pola wygląda następująco;

Przykład:
CREATE TABLE klienci(
klient_id integer NOT NULL DEFAULT

nextval(‘klient_id_seq’::text),
...
)
numer sekwencji można otrzymać:

currval(‘nazwa_sekwencji’)
numer następny:

nextval(‘nazwa_sekwencji’)
można zmienić wartość sekwencji: settval(‘nazwa_sekwencji’)

CREATE SEQUENCE - tworzenie

sekwencji



Wprowadzając dane do tabeli nie należy
wypełniać pola z sekwencją. PostgreSQL zrobi
to automatycznie wprowadzając dla każdego
rekordu nową unikalną wartość.

CONSTRAINT – ograniczenia dla tabeli



Tworzenie tablic



typ kolumny np.: VARCHAR(10)



typ ograniczenia np.: NOT NULL

Składnia:
CREATE [TEMPORARY] TABLE nazwa_tabeli
(

{nazwa_kolumny typ

[ograniczenia_dla_kolumny], ...}

[CONSTRAINT ograniczenie_dla_tabeli]

) [INHERITS (nazwa_istniejącej_tabeli)];

CONSTRAINT – ograniczenia dla tabeli

Ograniczenie

Opis

NOT NULL

W kolumnie nie mogą być zapisywane

wartości NULL

UNIQUE

Zapisywane wartości muszą być różne

dla każdego wiersza w tabeli

PRIMARY KEY

Klucz podstawowy tabeli – może być

tylko jeden

DEFAULT wartość

Umożliwia zdefiniowanie wartości

domyślnej w czasie wprowadzania

danych

CHECK (warunek)

Pozwala na sprawdzenie warunku w

czasie wprowadzania lub modyfikacji

danych

REFERENCES

Patrz: ograniczenia kluczy obcych



Ograniczenia dla

kolumn

CONSTRAINT – ograniczenia dla tabeli

Przykład:
CREATE TABLE filmy
(
kod CHAR (5)

CONSTRAINT klucz PRIMARY

KEY,
tytol VARCHAR(40)

NOT NULL,

dyst DECIMAL(3) NOT NULL,
data_prod DATE,
kind CHAR(10)
);
CREATE TABLE dystrybutorzy
(
dyst DECIMAL(3) PRIMARY KEY DEFAULT
NEXTVAL(’serial’),
nazwa VARCHAR(40) NOT NULL CHECK (name <> ”)
);

Projekt tabeli – E/R

MIEJ SCA

NR_M

ULICA
NUMER
MIASTO
KOD
TELEFON
UWAGI



Projekt tabeli (poziom logiczny)



Projekt tabeli (poziom fizyczny)

MIEJ SCA

NR_M: INTEGER

ULICA: VARCHAR(20)
NUMER: VARCHAR(10)
MIASTO: VARCHAR(20)
KOD: CHAR(5)
TELEFON: VARCHAR(20)
UWAGI: TEXT

Tabela – brak klucza głównego

CREATE TABLE MIEJSCA

(

NR_M

INTEGER,

ULICA

VARCHAR(24) NOT NULL,

NUMER

CHAR(8)

NOT NULL,

MIASTO

VARCHAR(24) NOT NULL,

KOD

CHAR(6)

NOT NULL,

TELEFON

CHAR(16),

UWAGI

VARCHAR(40),

);

Tabela – klucz główny (primary key)



Każda tabela w bazie powinna zawierać klucz
główny. Klucz główny to kolumna lub grupa
kolumn, która w jednoznaczny sposób
identyfikuje wiersz w tabeli.



Na przykład dla tabeli MIEJSCA kluczem
głównym może być pole NR_M (numer
miejsca).



Klucz główny zapobiega wstawianiu dwóch
identycznych wierszy do tabeli.

Tabela – klucz główny (primary key)

CREATE TABLE MIEJSCA

(

NR_M

INTEGER

PRIMARY KEY,

ULICA

VARCHAR(24) NOT NULL,

NUMER

CHAR(8)

NOT NULL,

MIASTO

VARCHAR(24) NOT NULL,

KOD

CHAR(6)

NOT NULL,

TELEFON

CHAR(16),

UWAGI

VARCHAR(40),

);

Tabela – wprowadzanie danych

INSERT INTO MIEJSCA(NR_M,ULICA,NUMER,MIASTO,KOD,TELEFON)
VALUES (

1000

,'LEWARTOWSKIEGO', '12', 'WARSZAWA', '10-100', '228-

277-097');

INSERT INTO MIEJSCA (NR_M,ULICA,NUMER,MIASTO,KOD,TELEFON)
VALUES (

1001

,ALEJE LIPOWE', '3', 'WROCLAW', '32-134', '388-299-

086');

INSERT INTO MIEJSCA (NR_M,ULICA,NUMER,MIASTO,KOD,TELEFON)
VALUES (

1002

,'KOCHANOWSKIEGO', '8', 'KRAKOW', '91-200', '222-312-

498');

INSERT INTO MIEJSCA(NR_M,ULICA,NUMER,MIASTO,KOD,TELEFON)
VALUES (

1003

,'LOTNICZA', '9', 'POZNAN', '22-200', '778-512-044');

Tabela – sekwencje



Sekwencja to generator kolejnych liczb,
związany z bieżącą bazą danych umożliwiający
zarządzanie indeksami w tabeli.



Po utworzeniu sekwencji używamy funkcji
nextval, currval i setval do obsługi sekwencji.

CREATE SEQUENCE NR_M START 1000;

numer sekwencji można otrzymać:

currval(‘nazwa_sekwencji’)
numer następny:

nextval(‘nazwa_sekwencji’)
można zmienić wartość sekwencji:

settval(‘nazwa_sekwencji’)

Tabela – sekwencje

CREATE TABLE MIEJSCA
(
NR_M INTEGER

NOT NULL PRIMARY KEY DEFAULT

NEXTVAL('NR_M'),

ULICA VARCHAR(24) NOT NULL,
NUMER CHAR(8)

NOT NULL,

MIASTO VARCHAR(24) NOT NULL,
KOD CHAR(6) NOT NULL,
TELEFON CHAR(16),
UWAGI VARCHAR(40),
);

Tabela – wprowadzanie danych z

sekwencjami

INSERT INTO MIEJSCA(NR_M,ULICA,NUMER,MIASTO,KOD,TELEFON)
VALUES ('LEWARTOWSKIEGO', '12', 'WARSZAWA', '10-100', '228-277-
097');

INSERT INTO MIEJSCA (NR_M,ULICA,NUMER,MIASTO,KOD,TELEFON)
VALUES ('ALEJE LIPOWE', '3', 'WROCLAW', '32-134', '388-299-086');

INSERT INTO MIEJSCA (NR_M,ULICA,NUMER,MIASTO,KOD,TELEFON)
VALUES ('KOCHANOWSKIEGO', '8', 'KRAKOW', '91-200', '222-312-498');

INSERT INTO MIEJSCA(NR_M,ULICA,NUMER,MIASTO,KOD,TELEFON)
VALUES ('LOTNICZA', '9', 'POZNAN', '22-200', '778-512-044');

Tabela – równoważne definicje

CREATE TABLE MIEJSCA

(

NR_M

INTEGER

PRIMARY KEY,

ULICA

VARCHAR(24) NOT NULL,

NUMER

CHAR(8)

NOT NULL,

MIASTO

VARCHAR(24) NOT NULL,

);

CREATE TABLE MIEJSCA

(

NR_M

INTEGER,

ULICA

VARCHAR(24) NOT NULL,

NUMER

CHAR(8)

NOT NULL,

MIASTO

VARCHAR(24) NOT NULL,

CONSTRAINT

PRIMARY KEY NR_M

);

Tabela – równoważne definicje



W powyższym przykładzie definicje tworzenia
tabeli są w zasadzie identyczne. Wynika to z
faktu, że tzw. ograniczenia ( CONSTRAINTS )
mogą dotyczyć kolumn (pierwsza definicja)
albo całej tabeli (druga definicja)



Ograniczenia będą omówione w późniejszej
części wykładu.

Tabela – klucz obcy (foreign key)



Klucz obcy to jedna lub więcej kolumn tabeli

odwołujących się do klucza głównego w innej tabeli.

Klucze obce są wykorzystywane do utrzymywania

integralności referencyjnej w bazie danych.



Tworząc klucz obcy, definiujemy związek między

tabelą klucza głównego i tabelą klucza obcego.



Związek taki powstaje podczas złączenia kolumn

takich samych typów danych z każdej tabeli.



Złączanie tabel przez odpowiednie kolumny chroni

dane z tabeli klucza obcego przed osieroceniem

jakie mogłoby nastąpić w wyniku usunięcia

odpowiadających im danych z tabeli klucza

głównego

Tabela – klucz obcy (foreign key)

NR_M_FK

MIEJ SCA

NR_M: INTEGER

ULICA: VARCHAR(20)
NUMER: VARCHAR(10)
MIASTO: VARCHAR(20)
KOD: CHAR(5)
TELEFON: VARCHAR(20)
UWAGI: TEXT

PRACOWNICY

NR_P: VARCHAR(20)
NR_M: INTEGER (FK)

IMIE: VARCHAR(20)
NAZWISKO: VARCHAR(20)
DATA_ZATR: TIMESTAMP
DZIAL: VARCHAR(20)
STANOWISKO: VARCHAR(20)
PENSJ A: INTEGER
DODATEK: INTEGER
TELEFON: VARCHAR(20)

NR_M_FK

MIEJ SCA

NR_M

ULICA
NUMER
MIASTO
KOD
TELEFON
UWAGI

PRACOWNICY

NR_P
NR_M (FK)

IMIE
NAZWISKO
DATA_ZATR
DZIAL
STANOWISKO
PENSJ A
DODATEK
TELEFON

NR_M_FK

jest nazwą związku (relationship) pomiędzy

tabelami

Tabela – klucz obcy (foreign key)

CREATE TABLE PRACOWNICY
(
NR_P INTEGER

NOT NULL DEFAULT NEXTVAL('NR_P'),

IMIE VARCHAR(20)

NOT NULL,

NAZWISKO VARCHAR(20)

NOT NULL,

DATA_ZATR DATE

NOT NULL,

DZIAL VARCHAR(20)

NOT NULL,

STANOWISKO VARCHAR(20)

NOT NULL,

PENSJA DECIMAL(8,2),
DODATEK DECIMAL(8,2),
NR_M

VARCHAR(4),

TELEFON

CHAR(16),

CONSTRAINT NR_P_PK PRIMARY KEY (NR_P)
CONSTRAINT

NR_M_FK FOREIGN KEY (NR_M) REFERENCES MIEJSCA(NR_M)

);

CREATE TABLE – równoważna definicja

Przykład:
CREATE TABLE dystrybutorzy (
dystr DECIMAL(3),
nazwa VARCHAR(40),
PRIMARY KEY(dystr)
);

CREATE TABLE dystrybutorzy (
dystr DECIMAL(3) PRIMARY KEY,
nazwa VARCHAR(40)
);

CONSTRAINT – ograniczenia dla tabeli

Funkcja

Opis

UNIQUE (lista_kolumn)

Zapisywane wartości muszą być różne

dla każdego wiersza każdej kolumny w

tabeli

PRIMARY KEY(lista_kolumn)

Klucz podstawowy tabeli – może być

tylko jeden

CHECK (warunek)

Pozwala na sprawdzenie warunku w

czasie wprowadzania lub modyfikacji

danych

REFERENCES

Patrz: ograniczenia kluczy obcych



Ograniczenia dla tabel

ALTER TABLE – uaktualnianie struktury

tabel



Uaktualnianie struktury tabeli posiadającej dane.
Kolumny dodane do tabeli będą posiadały wartości

null

Składnia:
ALTER TABLE nazwa_tabeli
ADD COLUMN nazwa_kolumny typ_kolumny
ALTER TABLE nazwa_tabeli
RENAME COLUMN stara_nazwa TO nowa_nazwa
ALTER TABLE nazwa_tabeli RENAME TO nowa_nazwa_tabeli

Przykład:
ALTER TABLE test_const ADD COLUMN data_urodzin DATE;
ALTER TABLE test_const RENAME COLUMN data_urodzin TO
urodziny;
ALTER TABLE test_const RENAME TO testowa;

CREATE TEMP TABLE – tabele

tymczasowe



Tabele tymczasowe. Jeżeli instrukcja SELECT
jest bardzo złożona to lepiej wyniki częściowe
przechowywać w tzw. tabelach tymczasowych.
Tabele te nikną z końcem sesji. Tabele
tymczasowe można wyświetlać z poziomu

psql

poleceniem

\dt

Przykład:
CREATE TEMPORARY TABLE pomocnicza ( ...);
CREATE TEMP TABLE pomocnicza (...);

INSERT INTO – wprowadź dane do

tablicy

Składnia:
INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( expression [, ...] ) |
SELECT query }

Przykład:
INSERT INTO klienci (klient_id,
nazwa,miasto,kod,ulica, nr )
VALUES (1001, ’ACTION S.A.’, ‘Warszawa ‘,’34-256’,’
Jana Kazimierza’,122);

COPY FROM (TO) – kopiuj dane do (z)

tablicy

Składnia:
COPY [ BINARY ] table [ WITH OIDS ]
FROM { ’filename’ | stdin }
[ [USING] DELIMITERS ’delimiter’ ]
[ WITH NULL AS ’null string’ ]

COPY [ BINARY ] table [ WITH OIDS ]
TO { ’filename’ | stdout }
[ [USING] DELIMITERS ’delimiter’ ]
[ WITH NULL AS ’null string’ ]

COPY FROM (TO) – kopiuj dane do (z)

tablicy

Przykład:
COPY kraje TO stdout USING DELIMITERS ’|’;

COPY kraje FROM ’/usr1/proj/bray/sql/dane’;

plik dane zawiera:

AF AFGHANISTAN
AL ALBANIA
DZ ALGERIA
ZM ZAMBIA
ZW ZIMBABWE
\.

CREATE – tworzenie tabeli jako rezultatu

zapytania

Składnia:

CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE
table_name [ (column_name [, ...] ) ]
AS query

Przykład:
CREATE TABLE klienci_warszawa
AS
SELECT klient_id, nazwa, kod, ulica, numer
FROM klienci
WHERE miasto=‘Warszawa’

CREATE VIEW – tworzenie perspektyw



Perspektywa to inaczej iluzja tabeli. Może zawierać

kolumny z jednej lub kilku tabel. Tworzenie

perspektywy polega na nazwaniu instrukcji SELECT

Składnia:
CREATE VIEW nazwa_perspektywy AS instrukcja_select;

Przykład:
CREATE VIEW cena AS

SELECT id_towaru, opis, cena
FROM towary;

CREATE INDEX – tworzenie indeksu

Składnia:
CREATE [ UNIQUE ] INDEX index_name ON table
[ USING acc_method ] ( column [ ops_name ] [, ...] )
[ WHERE predicate ]
CREATE [ UNIQUE ] INDEX index_name ON table
[ USING acc_method ] ( func_name( column [, ... ])
[ ops_name ] )
[ WHERE predicate ]

Przykład:
CREATE UNIQUE INDEX tytol_idx
ON filmy (tytol);

Ograniczenia kluczy obcych

ELEMENT_ID INTEGER

OPIS VARCHAR(64)

KOSZT INTEGER

CENA INTEGER

ELEMENT

KOD_ID VARCHAR(13)

ELEMENT_ID INTEGER

KOD_KRESKOWY

ELEMENT_ID – ELEMENT_ID

ELEMENT_ID INTEGER

ILOŚĆ INTEGER

ZAPAS

ELEMENT_ID – ELEMENT_ID

INFO_ID VARCHAR(13)

ELEMENT_ID INTEGER
ILOŚĆ INTEGER

LINIA_ZLECENIA

INFO_ID INTEGER

KLIENT_ID VARCHAR(64)

DATA_DOSTAWY DATE

DATA_WYSYLKI DATE

INFO_ZLECENIA

KLIENT_ID INTEGER

NAZWA VARCHAR(32)

ADRES VARCHAR(32)

MIASTO VARCHAR(32)

KLIENT

KLIENT_ID – KLIENT_ID

ELEMENT_ID – ELEMENT_ID

INFO_ID – INFO_ID

Ograniczenia kluczy obcych



Wprowadzamy pojęcie

klucza obcego

(ang. foreign

key). Przykładem klucz obcego jest pole KLIENT_ID w
tablicy INFO_ZLECENIA. Pod pojęciem klucza obcego
rozumiemy fakt, że pole KLIENT_ID nie jest w tej
tablicy kluczem podstawowym to jednak kolumna w
tabeli KLIENT z którą pole to wiążę pojedynczy wiersz
jest unikalna. Odwrotny związek nie zachodzi. W tabeli
KLIENT nie ma unikalnych kluczy żadnej innej tabeli.
Możemy stwierdzić że tabela KLIENT nie zawiera
kluczy obcych.



Istnieje możliwość że tabela zawiera więcej niż jeden
klucz obcy. W tabeli LINIA_ZLECENIA pola INFO_ID i
ELEMENT_ID są kluczami obcymi.

Ograniczenia kluczy obcych



Pole ELEMENT_ID jest kluczem podstawowym w tabeli
ELEMENTY i kluczem obcym w tabeli ZAPAS. Pojedyncza
kolumna może być zarówno kluczem podstawowym jak i
obcym co implikuje relację

jeden do jednego

pomiędzy

wierszami w obu tabelach.



W naszym przykładzie relacje te mają kluczowe znaczenie.
Jeżeli w tabeli INFO_ZLECENIA istnieje wiersz w którym
pole KLIENT_ID nie odpowiada polu KLIENT_ID w tabeli
KLIENT, to powstaje duży problem. Posiadamy
zamówienie natomiast nie wiemy który klient go złożył.



Taki związek kluczy obcych można zadeklarować jako
ograniczenie dla kolumn i tabel. Robi się to podczas
tworzenia tabeli jako część instrukcji CREATE TABLE za
pomocą ograniczenia REFERENCES

Klucze obce jako ograniczenia dla kolumn



Deklaracja kolumny jako klucza obcego w innej tabeli.
Nazwa ograniczenia jest opcjonalna. Warto ją definiować
bo może być pomocna w diagnozowaniu błędów

Składnia:

[CONSTRAINT nazwa_dowolna istniejąca_nazwa_kolumny
typ]
REFERENCE
nazwa_tabeli_zewnętrznej(kolumna_w_tabeli_zewnętrznej)

Klucze obce jako ograniczenia dla kolumn



Deklaracja kolumny jako klucza obcego w innej tabeli



Wprowadziliśmy

info_pk

jako nazwę ograniczenia

Przykład:
CREATE TABLE info_zlecenia
(

info_id

serial,

klient_id

integer not null REFERENCE

klient(klient_id),

data_dostawy date not null,
data_wysylki date,
CONSTRAINT

info_pk PRIMARY KEY(info_id)

)

Klucze obce jako ograniczenia dla tabel



Możemy deklarować klucze obce jako ograniczenia
dla kolumn ale lepiej jest deklarować je na poziomie
tabel wraz z ograniczeniem PRIMARY KEY.



Ograniczeń dla kolumn nie można zastosować jeśli
w powiązaniu bierze udział wiele kolumn z bieżącej
tabeli.

Składnia:

[CONSTRAINT nazwa_dowolna ] FOREIGN KEY
(lista_kolumn)
REFERENCE nazwa_tabeli_zewnętrznej (lista_kolumn_w
tabeli_zewnętrznej)

Klucze obce jako ograniczenia dla tabel

Przykład:

CREATE TABLE info_zlecenia
(

info_id

serial,

klient_id

integer not null,

data_dostawy date not null,
data_wysylki date,
CONSTRAINT

info_pk

PRIMARY KEY(info_id),

CONSTRAINT

klient_fk

FOREIGN

KEY(klient_id)

REFERENCES

klient(klient_id)

)

Klucze obce jako ograniczenia dla tabel

Przykład:

CREATE TABLE linia_zlecenia
(

info_id

serial,

element_id

integer not null,

zapas

integer not null,

CONSTRAINT

linia_pk

PRIMARY KEY(info_id),

REFERENCES

info_zlecenia(info-id),

CONSTRAINT

element_id_pk

FOREIGN

KEY(element_id)

REFERENCES

element(element_id)

)

Klucze obce jako ograniczenia dla tabel



W związku z ograniczeniami tabele należy
wypełniać (i kasować) w odpowiedniej
kolejności. Ograniczenia sugerują następującą
kolejność:



klienci



element



info_zlecenia



linia_zlecenia



zapas



kod_kreskowy

Klucze obce jako ograniczenia dla tabel



Może się zdarzyć, że wystąpi potrzeba
uaktualnienia kolumny klient_id w tablicy
info_zlecenia. Próby aktualizacji skończą się
niepowodzeniem.



Istnieją dwa sposoby na rozwiązanie tego
problemu;



zastosowanie predykatu DEFERRABLE powoduje, że
przy zastosowaniu transakcji Postgre SQL pozwoli
wykonać tą czynność wewnątrz transakcji.



zastosowanie predykatu ON DELETE CASCADE
oraz SET NULL. Są to tzw. reguły dla kluczy obcych
dotyczące obsługi uaktualnienia bądź kasowania.

Opcje ograniczeń dla kluczy obcych – I

metoda



zastosowanie predykatu DEFERRABLE powoduje,
że przy zastosowaniu transakcji Postgre SQL
pozwoli na naruszenie kluczy obcych ale tylko
wewnątrz transakcji..

CREATE TABLE info_zlecenia
(

info_id

serial,

klient_id

integer not null,

data_dostawy date not null,
data_wysylki date,
CONSTRAINT

info_pk

PRIMARY KEY(info_id),

CONSTRAINT

klient_fk

FOREIGN

KEY(klient_id)

REFERENCES

klient(klient_id) DEFERRABLE

)

Opcje ograniczeń dla kluczy obcych – II

metoda



Aby można było kasować rekordy z tablicy klienci stosujemy
predykat ON DELETE CASCADE powodujący w przypadku
usunięcia rekordu z tablicy klienci automatyczne usunięcie
rekordów z tablicy info_zlecenia. Należy postępować jednak
bardzo uważnie. I lepiej wykonywać to programowo

CREATE TABLE info_zlecenia
(

info_id

serial,

klient_id

integer not null,

data_dostawy date not null,
data_wysylki date,
CONSTRAINT

info_pk

PRIMARY KEY(info_id),

CONSTRAINT

klient_fk

FOREIGN

KEY(klient_id)

REFERENCES

klient(klient_id) ON DELETE

CASCADE

)

Document Outline