Čudna mi čuda – CHECK constraint
Wednesday, 16.01.2008 – ZidarPre neki dan dobio sam zadatak da kreiram jednu prilično flat (nenormalizovanu) tabelu i da obezbedim integritet podataka upotrebom CHECK constraints. Pošto ja u stvari nisam DBA, je ovo u praksi retko radim, mislim, da zaista kreiram tabele i slično. Moja priča se završi obično sa ER dijagramom, posle normalizacije. Ali eto, zalomilo se. Na svoj užas i javnu sramotu, otkrio sam da ja to u stvari više ne ume da uradim. Odem u Help, tamo nema baš mnogo pomoći. Većina mojih knjiga je o logičkom modelitranju i normalizaciji i o specijalno zakukuljenim kverijima. Takoda sam morao da eksperimetišem. Mnogo mi se dopalo, pa sam poželeo da podelim iskustvo. Ako se nekom učini da je ovaj članak suviše trivijalan i jednostavan, unapred se izvinjavam.
Znam od ranije da se constraint može definisati pri definiciji kolone, a i posle. Zapitao sam se šta je bolje, definisati CHECK prilikom definicije kolone, ili nakon kreiranja tabele, komandom ALTER table.
Izabrao sam prvi način, jer sam pretpostavio da ima manje kucanja. I krenuo sam ovako:
CREATE TABLE Test(ProjectId int NOT NULL CHECK(ProjectId = 51))
Onda sam pokušao da unesem pogrešan podatak, da testiram CHECK. Evo šta se desilo:
INSERT INTO Test VALUES (52)
Server me je pozdravio porukom:
The INSERT statement conflicted with the CHECK constraint "CK__Test__ProjectId__4222D4EF". The conflict occurred in database "ScrapBook", table "dbo.Test", column 'ProjectId'. The statement has been terminated.
Lepo, CHECK radi. Jedino šta mi se ne dopada je što me je na prvi pogled ime CK__Test__ProjectId__4222D4EF bilo malo nečitljivo. Sad vidim da CK znaši CHECK Test je ime tabele a ProjectID je ime kolone. Meni je sve to promekolo i pažnju mi je odvuklo ono 4222D4EF . Interesantno akko najmanje važan podatak upada u oči. Onda sam probao drugi način, sa ALTER TABLE ADD CONSTRAINT i desilo se ovo:
DROP TABLE TestGO CREATE TABLE Test(ProjectId int NOT NULL ) ALTER TABLE Test ADD CONSTRAINT ProjectId_must_be_51 CHECK(ProjectId = 51)
Pokušaj da unesem 52 umesto zahtevanih 51 dao je ovakvu poruku:
Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "ProjectId_must_be_51". The conflict occurred in database "ScrapBook", table "dbo.Test", column 'ProjectId'.The statement has been terminated.
Interesantno, odmah sam video o čemu se radi – ProjectID mora da bude 51.
Onda sam “pronašao” i treći način da sagradim jednostavni CHECK – u definiciji kolone, sa zadavenjem imena.
DROP TABLE TestGO CREATE TABLE Test (ProjectId int NOT NULL CONSTRAINT ProjectId_must_be_51 CHECK(ProjectId = 51) , ProjectName varchar(50) NULL)
Pokušaj da unesem šta ne sme da se unese:
INSERT INTO Test (ProjectID) VALUES (52)
Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "ProjectId_must_be_51". The conflict occurred in database "ScrapBook", table "dbo.Test", column 'ProjectId'.The statement has been terminated.
I još jedan način. Sada se CHECK definiše na nivou tabele, posle svih unetih kolona. Ovako:
CREATE TABLE Test
(ProjectId int NOT NULL
, ProjectName varchar(50) NULL, CONSTRAINT ProjectId_must_be_51 CHECK(ProjectId = 51))
Uočite zarez (zapetu) ispred reči CONSTRAINT. Tim zarezom smo odvojili definiciju kolone ProjectName od definicije CHECK CONSTRAINT za kolonu ProjectID. Ovo će se kasnije pokazati kao jako korisan način.Eto dakle četiri načina da se postavi jednostavan CHECK za individualnu kolonu. Pa birajte koji vam se dopada. Za proste slučajeve verovatno je svejedno.
Moj zadatak je bio teži, treblo je postaviti ne baš jednostavne CHECK koji su nad više od jedne kolone. Naravno, u propisno normalizovanoj tabeli po pravilu ne bi trebalo da imamo kolone koje zavise od drugih kolona u istoj tabeli, ali kako rekoh na početku, radi se o nenormalizovanoj tabeli.
U mojoj tabeli bile su dve povezane kolone, PkgType, integer i PackageCode varchar(6), isto što i PkgType ali sa nulama ispred. Dakle, PkgTzpe = 1 postaje PackageCode ’01’. Pokušao sam ovako:
CREATE TABLE Test ( ProjectId int NOT NULL CONSTRAINT ProjectId_must_be_51 CHECK(ProjectId = 51), PackageCode varchar (2) NOT NULL , PkgType int NOT NULL CONSTRAINT PkgType_is_PackageCode CHECK(PkgType = CAST(PackageCode AS int)))Odgovor je bio:
Msg 8141, Level 16, State 0, Line 1 Column CHECK constraint for column 'PkgType' references another column, table 'Test'. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors.Pošto su u pitanju dve kolone, to ne može biti deo definicije kolone, to mora da je na nivou tabele. Ovo je prošlo bez problema:
CREATE TABLE Test ( ProjectId int NOT NULL CONSTRAINT ProjectId_must_be_51 CHECK(ProjectId = 51), PackageCode varchar (2) NOT NULL , PkgType int NOT NULL , CONSTRAINT PkgType_is_PackageCode CHECK(PkgType = CAST(PackageCode AS int)))Razlika je u jednom zarezu kojim smo CHECK odvojili od definicije kolona. Zbog čitljivosti, opredelio sam se za format
CREATE TABLE Test ( -- Kolone ProjectId int NOT NULL , PackageCode varchar (2) NOT NULL , PkgType int NOT NULL -- Uslovi , CONSTRAINT PkgType_is_PackageCode CHECK(PkgType = CAST(PackageCode AS int)), CONSTRAINT ProjectId_must_be_51 CHECK(ProjectId = 51))Na kraju smo dobili izraz koji radi šta nam treba, lepo formatiran i jasan.
U sledećem javljanju pokazaćemo kako iz CHECK constraints mogu da se pozivaju druge tabele. Da, tačno je, ima i u Helpu, CHECK može da poziva i druge tabele. Ovo naravno ne znači da treba da zaboravimo na FOREIGN KEY i da sve prebacimo na CHECK. Medjutim ima situacija kada nam baš odgovara da kroz CHECK vidimo druge tabele.