Bazy Danych

Europejska Wyższa Szkoła Informatyczno-
Ekonomiczna
Mgr inż. Piotr Greniewski

Wykład 6: Podstawy SQL – część 3

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;

DROP USER – kasowanie użytkownika

Składnia:
DROP USER nazwa

Przykład:
DROP USER Jan;

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;

DROP GROUP – kasowanie grupy

Składnia:
DROP GROUP name

Przykład:
DROP GROUP marketing;

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ść.

DROP SEQUENCE – kasowanie sekwencji

Składnia:
DROP SEQUENCE name [, ...]

Przykład:
DROP SEQUENCE kod;

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;

DROP TABLE – kasowanie tabel

Składnia:
DROP TABLE nazwa_tabeli;

Przykład:
DROP TABLE klienci;

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;

DROP VIEW – kasowanie perspektyw

Składnia:
DROP VIEW nazwa_perspektywy;

Przykład:
DROP VIEW cena;

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);

DROP INDEX – kasowanie indeksu

Składnia:
DROP INDEX index_name [, ...]

Przykład:
DROP INDEX tytol_idx;

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

Slide 1
Slide 2
Slide 3
Slide 4
Slide 5
Slide 6
Slide 7
Slide 8
Slide 9
Slide 10
Slide 11
Slide 12
Slide 13
Slide 14
Slide 15
Slide 16
Slide 17
Slide 18
Slide 19
Slide 20
Slide 21
Slide 22
Slide 23
Slide 24
Slide 25
Slide 26
Slide 27
Slide 28
Slide 29
Slide 30
Slide 31
Slide 32
Slide 33
Slide 34
Slide 35
Slide 36
Slide 37
Slide 38
Slide 39
Slide 40
Slide 41
Slide 42
Slide 43
Slide 44
Slide 45
Slide 46
Slide 47
Slide 48
Slide 49
Slide 50
Slide 51
Slide 52
Slide 53
Slide 54
Slide 55
Slide 56
Slide 57
Slide 58
Slide 59
Slide 60
Slide 61
Slide 62
Slide 63

Wyszukiwarka

Podobne podstrony:
06 Podstawy syntezy polimerówid 6357 ppt
06 Marketing bezpośredni 3id 6145 ppt
05 podstawy SQL 2id 5972 ppt
06 Podstawy syntezy polimerówid 6357 ppt
16 podstawowe funkcje matematyczne 3id 16802 ppt
06 Kwestia potencjalności Aid 6191 ppt
Podstawy elektroniki i energoelektroniki prezentacja ppt
09 Podstawy chirurgii onkologicznejid 7979 ppt
11 3id 12114 ppt
2 Podstawowe definicje (2)id 19609 ppt
009 Dystrybucja 3id 2475 ppt
06 Testowanie hipotez statystycznychid 6412 ppt
1 Epidemiologia i podstawowe informacje o NSid 8500 ppt
02 3id 3357 ppt
GWSH - Podstawy turystyki, Podstawy turystyki wyklad 05.03.06, Podstawy turystyki wykład 05
Podstawy SQL Zajęcia 2, Podstawy SQL 2

więcej podobnych podstron

06 podstawy SQL 3id 6524 ppt

Document Outline