Malo slozeniji CHECK constraint
Monday, 22.08.2011 – Zidar
Kad govorimo o projektovanju baze podataka, nekako uvek mislimo na to kako postaviti tabele i veze izmedju njih. Govoreci SQL jeziokom, oslanjamo se uglavnom na FOREIGN KEY ogranicenja. Cesto zaboravljamo da postoje i druga ogranicenja koja daju isti efekat a ponekad pojednostavljuju konacno resenja. Pokazacemo na jednom primeru kako se moze upotrebiti CHECK constraint umesto FOREIGN KEY, kao i kako se ista situacija razresava na ‘klasican nacin’ – upotrebo FOREIGN KEY ogranicenja.
Recimo da imamo tabelu Kupci, gde cuvamo podatke – o kupcima. Kupaca imamo dve vrste, pravna i fizicka lica. Pravna lica (preduzeca) imaju jednu vrstu identifikaciong broja, a fizicka lica imaju maticni broj gradjana. ID broj za pravna lica je osmocifreni broj, a maticni broj ima 18 cifara. Krenimo od ovakvog resenja:
IF OBject_ID(‘Kupci’) IS NOT NULL DROP TABLE Kupci
;
CREATE TABLE Kupci
(
KupacID varchar(12) NOT NULL PRIMARY KEY
, ImeKUPCA varchar(50) NOT NULL
, CONSTRAINT KupacID_samo_cifre CHECK (KupacID NOT LIKE ‘%[^0-9]%’)
, CONSTRAINT KupacID_Duzina CHECK (LEN (KupaciD) = 8 OR LEN(KupacID) = 18)
)
;
Zanemarimo za trenutak cinjenicu da smo u jednu kolonu strpali dve razlicite stvari. ID preduzeca i maticni broj gradjana nisu isto te smo time smo pravilo 1NF. Molim da zanemarite ovu cinjenicu. Nesto drugo zelimo da pokazemo.
Postavili smo ogranicenje da se u varchar kolonu mogu uneti samo cifre. Takodje smo ogranicili duzinu KupacID na 8 ili 18 karaktera, ili ili, ostale duzine nisu dozvoljene.
Da vidimo sta nismo pokrili ogranicenjima. Nista nas sprecava da za pravno lice unesemo 18 cifara a za fizicko lice 8 cifara. Pokusajmo da unesemo nekoliko redova. Prva dve komande padaju, zbog CHECK ogranicenja. Druge dve prolaze, i treba da prodju. Poslednje dve prolaze, a ne treba da prodju. Kompanija Big Brother Co, prolazi sa maticnim brojem gradjana, a gradjanin Donald Duck prolazi sa id brojem preduzeca.
INSERT INTO Kupci VALUES (‘1XX23’,‘KUpac A’);
INSERT INTO Kupci VALUES (‘123’,‘Export-Import Co’);
INSERT INTO Kupci VALUES (‘12345678’,‘Export-Import Co’);
INSERT INTO Kupci VALUES (‘123456789012345678’,‘Marko Markovic’);
INSERT INTO Kupci VALUES (‘987654321012345678’,‘Big Brother Co.’);
INSERT INTO Kupci VALUES (‘87654321’,‘Donald Duck’);
Zato bi trebalo da znamo koji kupac je pravno a koji je fizicko lice. Dodajmo kolonu za novi podatak.
IF OBject_ID(‘Kupci’) IS NOT NULL DROP TABLE Kupci
;
CREATE TABLE Kupci
(
KupacID varchar(18) NOT NULL PRIMARY KEY
, ImeKUPCA varchar(50) NOT NULL
, TipKupca char(1) NOT NULL CHECK ( TipKupca IN (‘P’,‘F’))
, CONSTRAINT KupacID_samo_cifre CHECK (KupacID NOT LIKE ‘%[^0-9]%’)
, CONSTRAINT KupacID_Duzina CHECK (LEN (KupaciD) = 8 OR LEN(KupacID) = 18)
)
;
Sva ogranicenja iz prvog pokusaja i dalje vaze, ali sad mozemo da idemo dalje. Sada imamo dve kolone koje su medjusobno zavisne. Ako je TipKupca = ‘P’ onda KupacID ima 8 cifara. Ako je TipKupca = ‘F’ onda KupacID ima 18 cifara. Sve ostalo nije dozvoljeno. Imamo dve varijable, a =TipKupca i b = LEN(KupacID), sa po dve moguce vrednosti. To nam daje 4 moguce kombinacije, od kojih su neke tacne a neke ne. Napravimo tablicu:
a=TipKupca |
b=LEN(KupacID) |
Rezultat |
P |
8 |
Dozvoljeno |
P |
18 |
Nije dozvoljeno |
F |
8 |
Nije Dozvoljeno |
F |
18 |
Dozvoljeno |
Od cetiri kombinacije, dve su dozvoljene a dve nisu. Ogranicenje bi se moglo napisati ovako:
ALTER TABLE Kupci
ADD CONSTRAINT KupacID_Duzina_PF
CHECK (1 =
CASE
WHEN TipKupca = ‘P’ AND LEN(KupacID) = 8 THEN 1
WHEN TipKupca = ‘P’ AND LEN(KupacID) = 18 THEN 0
WHEN TipKupca = ‘F’ AND LEN(KupacID) = 8 THEN 0
WHEN TipKupca = ‘F’ AND LEN(KupacID) = 18 THEN 1
END
)
;
Testirajmo sada tabelu:
INSERT INTO Kupci VALUES (‘1XX23’,‘KUpac A’,‘P’);
INSERT INTO Kupci VALUES (‘123’,‘Export-Import Co’,‘F’);
INSERT INTO Kupci VALUES (‘12345678’,‘Export-Import Co’,‘P’);
INSERT INTO Kupci VALUES (‘123456789012345678’,‘Marko Markovic’,‘F’);
INSERT INTO Kupci VALUES (‘987654321012345678’,‘Big Brother Co.’,‘P’);
INSERT INTO Kupci VALUES (‘87654321’,‘Donald Duck’,‘F’);
Prve dve komande padaju, iz istog razloga kao i pre. Druge dve prolaze. Trece dve ne prolaze, zbog dodatnog uslova. Upotreba CASE funkcije u CHECK uslovu omogucila nam je da bolje kontrolisemo ulazne podatke. Zbog jednostavnosti postavili smo uslov po duzini niske karaktera a u realnosti mogli smo da ukljucimo sta god hocemo, pa cak i pozivanje korisnickih funkcija. Ako ste ikad napisali korisnicku funkciju koja proverava maticni broj gradjana, mozete je upotrebiti i ovde.
Sigurno je neko primetio da se zadatak moze resiti tipizacijom – razbijanjem tabele Kupci na vise tabela. Imali bismo tri tabele, Kupci, Kupci_P, Kupci_F. Tabele Kupci_P i Kupci_F sluze da razdvojimo tabelu Kupci na dva tipa, pravna i fizicka lica. Tabele Kupci_P i Kupci_F nazvacemo ‘subtip tabele’. Za svaku od subtip tabela morali smo da definisemo ceo skup ogranicenja.
IF OBject_ID(‘Kupci_F’) IS NOT NULL DROP TABLE Kupci_F
;
IF OBject_ID(‘Kupci_P’) IS NOT NULL DROP TABLE Kupci_P
;
IF OBject_ID(‘Kupci’) IS NOT NULL DROP TABLE Kupci
;
CREATE TABLE Kupci
(
KupacID int NOT NULL PRIMARY KEY
, ImeKUPCA varchar(50) NOT NULL
, TipKupca char(1) NOT NULL CHECK ( TipKupca IN (‘P’,‘F’))
, UNIQUE (KupacID, TipKupca)
)
;
CREATE TABLE Kupci_P
( KupacID int NOT NULL UNIQUE
, TipKupca char(1) NOT NULL CHECK (TipKupca = ‘P’)
, BrojPreduzeca char(8) NOT NULL PRIMARY KEY
, CHECK (BrojPreduzeca NOT LIKE ‘%[^0-9]%’)
, CHECK (LEN (KupaciD) = 8)
, CONSTRAINT fk_Kupci_P
FOREIGN KEY (KupacID, TipKupca)
REFERENCES Kupci (KupacID, TipKupca)
)
;
CREATE TABLE Kupci_F
(
KupacID int NOT NULL PRIMARY KEY
, TipKupca char(1) NOT NULL CHECK (TipKupca = ‘F’)
, MaticniBroj char(8) NOT NULL UNIQUE
, CHECK (MaticniBroj NOT LIKE ‘%[^0-9]%’)
, CHECK (LEN (KupaciD) = 18)
, CONSTRAINT fk_Kupci_F
FOREIGN KEY (KupacID, TipKupca)
REFERENCES Kupci (KupacID, TipKupca)
)
;
Primetite da je tip kupca ponovljen u subtip tabelama. Ovo je vazno jer neke knjige ne pominju da se tip entitete prenosi u subtip tabele. Tip kupca u tabeli kupci mora da se slaze sa tipom kupca u subtip tabelama. U subtip tabelama tip moze imati tacno jednu vrednost. Ovo sprecava da istog kupca upisemo u dve subtip tabele.
Metod sa subtipovima jeste relaciono ispravniji. Nema mesanja dve vrste podataka u istoj kolni. Medjutim, uvek postoji sansa da se u tabelu Kupci unese kupac, a da iz nekog razloga ne prenesemo kupca u subtip tabelu. Ima razlike i u pisanju kverija. Ako imate jednu tabelu, lako je povuci sve kupce iz nje. Sa tri tabele, pokusajte da ispisete sve kupce i njihove ID brojeve. Sta je jednostavnije:
SELECT * FROM Kupci — metod jedne tabele
ili
— metod tri tabele
SELECT
K.KupacID
, K.ImeKUPCA
, K.TipKupca
, BrojKupca = COALESCE (P.BrojPreduzeca, K.MaticniBroj)
LEFT JOIN Kupci_P AS P ON P.KupacID = K.KupacID
LEFt JOIN Kupci_F AS F ON F.KUpacID = K.KupacID
U metodu tri tabele imate KupacID i BrojKupca. Koja od te dve vrednosti u stvari odredjuej kupca?
Ispada da ponekad malkice denormalizacije moze biti i korisna stvar. Uz uslov da postavite sva potrebna ogranicenja.
Ima situacija kada se absolutno mora pribeci podtipovima. U nasem primeru subtip tabele imaju identicnu strukturu. Nije uvek tako. Ako bi za fizcka lica cuvali datum rodjena a za preduzeca ime direktora ili osobu za kontakt, onda nam ne ginu tri tabele. I tada bi mogli da koristimo jednu tabelu i napisemo uslove za validaciju nekolko kolona, ali bi to bilo mnogo komplikovanije nego u nasem primeru.
6 Responses to “Malo slozeniji CHECK constraint”
Eeee odlican tekst!
Kamo srece da cesce pises ovakve kvalitetne clanke!
By Dejan on Aug 25, 2011
Jako loše. Od ne upotrebe naših slova do katastrofalnih konvencija, grešaka u kucanju pa do ozbiljnih propusta (KupacID varchar(12!!!) NOT NULL PRIMARY KEY). Jako aljkavo i jadno…
By Dragan on Sep 2, 2011
A i ovo se može mnogo bolje napisati:
ALTER TABLE Kupci
ADD CONSTRAINT KupacID_Duzina_PF
CHECK (1 =
CASE
WHEN TipKupca = ‘P’ AND LEN(KupacID) = 8 THEN 1
WHEN TipKupca = ‘P’ AND LEN(KupacID) = 18 THEN 0
WHEN TipKupca = ‘F’ AND LEN(KupacID) = 8 THEN 0
WHEN TipKupca = ‘F’ AND LEN(KupacID) = 18 THEN 1
END
)
;
Šta će ti dva srednja reda u CASE? Sve osim true treba da bude pod else 0.
Ili još jednostavnije:
CHECK ( (TipKupca = ‘P’ AND LEN(KupacID) = 8) or (TipKupca = ‘F’ AND LEN(KupacID) = 18) )
By Dragan on Sep 2, 2011
I pored “propusta” kao što su ostavljena vrednost 12 za širinu polja ili WHEN THEN umesto ELSE ovo je odličan tekst jer mu je težište na sasvim drugoj stvari. Baš fin primer lukavosti i umerenog bežanja od konvencija. I najvažnije, ograničio si se na upotrebu ovog “trika” u određenim situacijama. Sjajno!
Pozdrav!
By Sale on Sep 9, 2011
Dragan je dobro primetio da je KupacID varchar(18) NOT NULL PRIMARY KEY pogresno, treba varchar(18). Hvala na zapazanju, izvinjavam se za gresku 🙂
Zasto je CASE napisan onako? Da bismo u potpunosti pokrili tablicu odlucivanja. Naravno da nema potrebe pisati potpun CASE, dva puta WHEN kad imamo ELSE. Ako bi kod bio optimizovan, veza sa sustinom problem bi se zamaglila. Sustina problema i resenja dati su tablicom odlucivanja, CASE …WHEN .. ELSE – to je samo mehanizam koji nam pomaze da sa logickog resenja predjemo na fizicko. Posto je ovo clanak, a ne pojekat iz prakse, odlucio sam da celu tablicu odlucivanja pokrijem kodom, bukvalno, red po red.
Istina je da se umesto CASE moze napisati iskaz sa OR, onako kako je to potuno ispravno uradio Dragan. Slucajno sam izabrao primer gde se CASE moze zameniti sa OR. U mnogo slicnih slucajeva izrazi sa OR i/ili AND uopste ne rade ili su previse komplikovani. CASE izraz je jednostavniji za pracenje logike i razumevanje. Prava poruka je “kad imate slozeni constraint, napravite tablicu odlucivanja, pokrijte je potpuno sa CASE pa onda vidite moze li se CASE pojednostaviti”
U svakom slucaju, hvala na komentarima, lepo je znati da neko cita clanke toliko detaljno. Voleo bih da moji testeri i programeri ulazu toliko truda 🙂
By Zidar on Sep 28, 2011
Ja se izvinjavam što na ovom postu postavljam jedno pitanje koje me muči već duže vreme.
Kako da prebacim sliku iz baze podataka u npr. Photoshop. Koristim Windows XP SP3 i bazu podataka 10g. Slika je ubačena u bazu pomoću jedne aplikacije. Korisim Oracle Forms 6i.
Sada mi treba jedna slika, a nemogu da je prebacim u Photoshop.
Molim za pomoć?
Unapred zahvalan!!!
By Zdravko on Oct 2, 2011