Bravo, odgovori su stigli brze nego sto sam ocekivao (vikend, eh). Ima li neko ideju kako se ovo moze resiti na jos neki nacin, pomocu FK i/ili dekompozicije? Ili jos neki nacin da se napisu constraints?
Licno mi se resenja sa CHECK dopadaju u ovakvim slucajevima, iako ih je nekad tesko razumeti. Igor i Djoka su obavili odlican posao, ali za one koji nisu mogli sami da dodju do resenja citanje ponudjenih resenja nece doneti mnogo pomoci i uspeha u buducnosti. Pokusacu da dam generalnu ideju kako se ovakvi slucajevi resavaju, kako i zasto, i nadam se da ce CHECK constraints biti bar malkice citljiviji.
Ovde imamo uslove topa "Ako P onda Q", matematicki kazano P => Q
Tri uslova kja smo zadali, mogu s eiskazati ovako:
Ako je nek vegatarijanac, onda dobija Meal = 'Veg'
Ko nije vegatarijanac, ne moze dobiti Meal = 'Veg'
Ko ima religiju 'A' ili 'B', ne sme dobiti Meal = 'Pork'
To mozemo da napisemo preqo P i Q, ovako
P = "Osobe je vegeterijanac", Q = "Meal je 'Veg'", P=> Q postaje (Osobe je vegeterijanac) => (Meal je 'Veg')
P = "Osoba nije vegatarijanac", Q = "Meal ne sme biti 'Veg'", P=>Q postaje "Osoba nije vegatarijanac" => ( "Meal ne sme biti 'Veg'")
P = "Religija IN ('A','B')", Q = "Meal ne sme biti 'Pork'", P=> Q postaje "Religija IN ('A','B')", => "Meal ne sme biti 'Pork'"
Sva tri uslova su dakle P => Q. Kako napsiati CHECK za P=>Q? Ne zanm ni za jedan RDBMS gde postoji operator =>. Postoji AND , OR i NOT. Veliko pitanje je "Kako naisati P => Q pomocu AND, OR i NOT?" Na srecu, odgovor postoji, moze se naci cak i na vikipediji.
(P => Q) <=> ( (NOT P) OR Q)
Evo ga dakle resenje, P=> izrazeno preko NOT i OR! Ovo s enije predavalo u skoli kad sam ja to ucio, davne 1974 godine i prvi put sam video ovu tautologiju u knjizi Joe Celko's SQL for Smarties, pod nazivo, 'Smistru rule'. Posle sam to video kao deo matematicke logike u knjizi Applied MAthematics for Database professionals, de Haan/Koopelars, a onda i na wikipediji.
Ovako su napisane cinstraints:
Code:
" Vegetarian = 1 => Meal = 'Veg'" mose da se napise kao:
NOT (Vegetarian = 1) OR Meal = 'Veg'
-- i dobijamo:
ALTER TABLE Guests ADD CONSTRAINT ckVeggies
CHECK ( NOT (Vegetarian = 1) OR Meal = 'Veg')
-- " Vegetarian <> 1 => Meal <> 'Veg'" moze da se napise kao
NOT Vegetarian <> 1 OR meal <> 'Veg', ili ovako
Vegatarin = 1 OR Meal <> 'Veg', pa dobijamo
ALTER TABLE Guests ADD CONSTRAINT ckVegForVeetariansOnly
CHECK ( Vegetarian = 1 OR Meal <> 'Veg' )
-- Takodje,
" Religion IN ('A','B') => (Meal <> 'Pork') " mozemo da psiemo
NOT (Religion IN ('A','B')) OR (Meal <> 'Pork')
sto je isto sto i
NOT (Religion IN ('A','B') AND Meal = 'Pork')
pa dobijamo ovo:
ALTER TABLE Guests ADD CONSTRAINT ckNoPork
CHECK ( NOT (Religion IN ('A','B') AND Meal = 'Pork'))
Ja sam izabrao da uprostim konacan izraz unotar CHECK, a mogao sam da napisem bilo koji od izraza kroz koje sam prosao. Tako sam dosao do 3 CHECK izraza, z a3 uslova. Primetita da dva uslova za vegatarijance us tvari kazu:
Ako i samo ako je neko vegetarijanc, ta osoba dobija Meal = 'veg'
ili
Vegetarijanac = 1 <=> Meal = 'Veg'
sto se opet prevodi na dve imlikacije:
Vegetarijanac = 1 => Meal = 'Veg'
i
Vegetarijanac = 1 <= Meal = 'Veg'
Ako pogledata sada pazljivo CHECK koji je dao Igor, prepoznacete ova tri uslova. Primetita da u mojim uslovima ne pominjemo NULL nigde. Ako kolona dozvoljava NULL vrednosti, onda su sve CHECK constraints tacne po definiciji za sve NULL vrednosti, pa ih ne moramo navoditi u specifikaciji CHECK uslova.
Na kraju, lepo je sto znamo da predstavimo P=>Q, ali treba biti obazriv. Ako imate puno P=>Q u strukturi tabele, onda verovatno nesto nije u redu sa dizajnom tabele. U konkretnom slucaju, deluje da je sve normalizovano OK, ali nije. Ko moze da vidi kako bi se ovo resilo uz pomoc FK i mozda razbijanja nekih kolona na subtipove? U tom slucaju nam ne trebaju CHECK uslovi, barem teorijski, a mozda i prakticno.
Za svaki slucaj, evo ponovo kod za kreiranje tablele i testiranje:
Code:
IF Object_ID('Guests') IS NOT NULL DROP TABLE Guests
;
CREATE TABLE Guests
(PersonID int NOT NULL PRIMARY KEY
, Vegetarian tinyint NOT NULL CONSTRAINT ckVegetarian CHECK (Vegetarian IN (0,1))
, Religion varchar(1) NOT NULL CONSTRAINT ckReligion CHECK (Religion IN ('A','B','C','D'))
, Meal varchar(7) CONSTRAINT ckMeal CHECK (Meal IN ('VEG','PORK','BEEF','CHICKEN'))
)
;
INSERT INTO Guests (PersonID, Vegetarian, Religion, Meal)
SELECT 1,1,'A', NULL
UNION
SELECT 2,0,'A', NULL
UNION
SELECT 3,1,'B', NULL
UNION
SELECT 4,0,'B', NULL
UNION
SELECT 5,1,'C', NULL
UNION
SELECT 6,0,'C', NULL
UNION
SELECT 7,1,'D', NULL
UNION
SELECT 8,0,'D', NULL
;
ALTER TABLE Guests ADD CONSTRAINT ckVeggies
CHECK ( NOT (Vegetarian = 1) OR Meal = 'Veg')
;
ALTER TABLE Guests ADD CONSTRAINT ckVegForVeetariansOnly
CHECK ( Vegetarian = 1 OR Meal <> 'Veg' )
;
ALTER TABLE Guests ADD CONSTRAINT ckNoPork
CHECK ( NOT (Religion IN ('A','B') AND Meal = 'Pork'))
;
SELECT * FROM Guests
PersonID Vegetarian Religion Meal
----------- ---------- -------- -------
1 1 A NULL
2 0 A NULL
3 1 B NULL
4 0 B NULL
5 1 C NULL
6 0 C NULL
7 1 D NULL
8 0 D NULL
-- TESTIRANJE
-- This should work:
UPDATE Guests SET Meal = 'Veg' WHERE PersonID = 1 -- Person 1 is a vegetarion
-- It did work, (1 row(s) affected)
-- Thsi should fail:
UPDATE Guests SET Meal = 'Beef' WHERE PersonID = 1
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "ckVeggies". The conflict occurred in database "zzz", table "dbo.Guests".
The statement has been terminated.
-- This should work:
UPDATE Guests SET Meal = 'Beef' WHERE PersonID = 2 -- Person 2 hs relihgion A
-- Beef is OK for person 2, so (1 row(s) affected)
-- This should fail:
UPDATE Guests SET Meal = 'Pork' WHERE PersonID = 2
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "ckNoPork". The conflict occurred in database "zzz", table "dbo.Guests".
The statement has been terminated.
-- This should work:
UPDATE Guests SET Meal = 'Beef' WHERE PersonID = 6 -- religion C, PORK OK, not a vegatarian
-- (1 row(s) affected)
UPDATE Guests SET Meal = 'Veg' WHERE PersonID = 6
-- (1 row(s) affected)
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "ckVegForVeetariansOnly". The conflict occurred in database "zzz", table "dbo.Guests".
The statement has been terminated.