Izvinjavam se za pauzu,mora covek nekad i na poslu nesto da radi... A onda mi je odgovor otisao 'u vazduh'. sva sreca, sacuvao sam kopiju u text fajlu. Da probamo sada:
Izvinjavam se za nejavljanje, bilo je mnogo obaveza, pa eto.
Problem sa uvodjenjem vestackog Id, autoinkrement ili ne jeste sto se cesto zaboravi na prirodni, realni kljuc, koji naravno moze biti sastavljen od vise od jedne kolone. Moja verzija sa upotrebom ID u tabeli Zaglavlje pati od upravo takve greske. Ovo sam bio predlozio:
Code:
IF OBject_ID('MKI_Stavke') IS NOT NULL DROP TABLE MKI_Stavke
;
IF OBject_ID('MKI_Zaglavlje') IS NOT NULL DROP TABLE MKI_Zaglavlje
;
CREATE TABLE MKI_Zaglavlje
(
Id int identity PRIMARY KEY
, Firma nvarchar(50) NOT NULL
, Vrsta_Dokumenta nvarchar(50) NOT NULL
, Broj_Dokumenta nvarchar(20) NOT NULL
)
GO
Evo sta se moze disti:
Code:
INSERT INTO MKI_Zaglavlje (Firma, Vrsta_Dokumenta, Broj_Dokumenta)
VALUES ('PKB','Faktura','2015-152')
, ('PKB','Faktura','2015-152')
, ('PKB','Faktura','2015-152')
, ('TTU','Otpremnica','2015-007')
;
-- (4 row(s) affected)
SELECT * FROM MKI_Zaglavlje;
Id Firma Vrsta_Dokumenta Broj_Dokumenta
----------- -------- ----------------- --------------
1 PKB Faktura 2015-152
2 PKB Faktura 2015-152
3 PKB Faktura 2015-152
4 TTU Otpremnica 2015-007
(4 row(s) affected)
Uneo sam dakle 3 puta iste podatke - 'PKB','Faktura','2015-152', bez ikakvih problema. Jedino se razlikuju vrednosti za ID. To nije dobro i to ne mozemo ostaviti front-end programeru da resava. Programi i programeri dolaze i odlaze, a baza ostaje, godinama i nikad se ne zna ko ce i kada da zaboravi da na front endu postavi neophodne uslove integriteta (jedinstvenost je uslov integriteta podataka). Da bi se ti problemi izbegli, uslovi integriteta se deklarisu na nivou tabele u bazi podataka. Ako pokusam da uspostavim uslov UNIQUE (Firma, Vrsta_Dokumenta, Broj_Dokumenta) nece mi uspeti, jer tabela vec sadrzi duplikate (triplikate :-). Zato moram da obrisem sta imam u tabeli i da probam ponovo:
Code:
ALTER TABLE MKI_Zaglavlje ADD CONSTRAINT
[U tabeli Zaglavje kombinacija (Firma, Vrsta_Dokumenta, Broj_Dokumenta) mora biti jedinstvena!]
UNIQUE (Firma, Vrsta_Dokumenta, Broj_Dokumenta)
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MKI_Zaglavlje' and the index name 'U tabeli Zaglavje kombinacija (Firma, Vrsta_Dokumenta, Broj_Dokumenta) mora biti jedinstvena!'. The duplicate key value is (PKB, Faktura, 2015-152).
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.
Zato oram prvo da uradim ovo
Code:
DELETE FROM MKI_Zaglavlje
-- (4 row(s) affected)
ALTER TABLE MKI_Zaglavlje ADD CONSTRAINT
[U tabeli Zaglavje kombinacija (Firma, Vrsta_Dokumenta, Broj_Dokumenta) mora biti jedinstvena!]
UNIQUE (Firma, Vrsta_Dokumenta, Broj_Dokumenta)
GO
-- Command(s) completed successfully.
Da probamo ponovo da unesemo duplikate:
Code:
INSERT INTO MKI_Zaglavlje (Firma, Vrsta_Dokumenta, Broj_Dokumenta)
VALUES ('PKB','Faktura','2015-152')
, ('PKB','Faktura','2015-152')
, ('PKB','Faktura','2015-152')
, ('TTU','Otpremnica','2015-007')
;
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'U tabeli Zaglavje kombinacija (Firma, Vrsta_Dokumenta, Broj_Dokumenta) mora biti jedinstvena!'. Cannot insert duplicate key in object 'dbo.MKI_Zaglavlje'. The duplicate key value is (PKB, Faktura, 2015-152).
The statement has been terminated.
Ovaj put unos je odbacen, kako i treba. Da probamo korektan slucaj:
Code:
INSERT INTO MKI_Zaglavlje (Firma, Vrsta_Dokumenta, Broj_Dokumenta)
VALUES ('PKB','Faktura','2015-152')
, ('TTU','Otpremnica','2015-007')
;
-- (2 row(s) affected)
Id Firma Vrsta_Dokumenta Broj_Dokumenta
----------- -------- ----------------- --------------
7 PKB Faktura 2015-152
8 TTU Otpremnica 2015-007
(2 row(s) affected)
Unos je uspeo ovaj put. Ali, ima jedna ruzna stvar - Id je poceo od 7, a ne od 1, iako smo obrisali sve iz tabele pre unosa. To je drugi problem sa autoinkrement Id vrednostima - nemate nikakvu kontrolu nad njima. Kako ce programer na front endu da zna koji je Id sistem dodelio novom redu? Naravno da postoje funkcije da se to uradi, unutar DBMS, ali nazalost, ne zna ih svako. U MS SQL, da li upotrebiti @@Identity, Scope_Identity(), Ident_Current()? Ili nesto trece? A to nam treba da bi dodali stavke u tabelu MKE_Stavke. Ako radite u MS Access, ovo sve nije problem jer Access zna da prenese identity iz roditelj tabele u dete tabelu, bez ikakvog koda. Ali, access je zastareo, sada je u mdi .NET, jeste da zahteva oko 10 puta vise vremena da se isti posao odradi, ali je bar moderno :-)
Ali hajde, neka nam Id u MKE_Zaglavlje, uz uslov da smo postavili UNIQUE ogranicenje na tri kolone koje cine prirodni kljuc. Da predjemo na dete tabelu, MKE_Stavke. ja sam predlozio:
Code:
CREATE TABLE MKI_Stavke
(
Id int NOT NULL
, Broj_Stavke int NOT NULL
, NazivArtikla nvarchar(50) NOT NULL
, Kolicina decimal (12,4) NOT NULL
, PRIMARY KEY (Id, Broj_Stavke)
)
GO
ALTER TABLE MKI_Stavke
ADD CONSTRAINT [FK1 Stavke: (Id) mora da ima odgovarajuci red u tabeli Firme]
FOREIGN KEY (Id)
REFERENCES MKI_Zaglavlje (Id)
GO
Da probamo nekoliko stavki, sve korektne, bez duplikata:
Code:
INSERT INTO MKI_Stavke (Id, Broj_Stavke, NazivArtikla, Kolicina)
VALUES (8,1,'Cokolada',2)
, (8,2,'Mleko',1)
, (8,3,'Hleb',1)
GO
-- (3 row(s) affected)
INSERT INTO MKI_Stavke (Id, Broj_Stavke, NazivArtikla, Kolicina)
VALUES (7,1,'Cokolada',2)
, (7,2,'Mleko',1)
, (7,3,'Cokolada',1)
;
-- (3 row(s) affected)
SELECT *
FROm MKI_Stavke
ORDER BY Id, Broj_Stavke
;
Id Broj_Stavke NazivArtikla Kolicina
----------- ----------- ------------ ---------
7 1 Cokolada 2.0000
7 2 Mleko 1.0000
7 3 Cokolada 1.0000
8 1 Cokolada 2.0000
8 2 Mleko 1.0000
8 3 Hleb 1.0000
(6 row(s) affected)
Da probamo duplikat, cisto zbog dokumentacije:
Code:
INSERT INTO MKI_Stavke (Id, Broj_Stavke, NazivArtikla, Kolicina)
VALUES (8,1,'Kisela voda',2)
, (8,2,'Jogurt',1)
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__MKI_Stav__7F385C6B8161C3FF'. Cannot insert duplicate key in object 'dbo.MKI_Stavke'. The duplicate key value is (8, 1).
The statement has been terminated.
Vratimo se na unose koji su prosli. Pogledajte otpremnicu za zaglavlje Id = 7. Cokolada se pojavljuje dva puta, pod brojem 1 i pod brojem 3. da li je to u redu? Zavisi od zadatka. Ako se radimo kasu za samouslugu, onda se moze dozvoliti, jer se cokolada moze naci na pocetku, a nesto kasnije i druga, ista takva cokolada. To je jedini slucaj gde ima smisla dozvoliti da se isti artikl pojavi vise nego jednom, na istom dokumentu - racun koji dobijete na kasi. U nasem slucaju, dokument koji modelujemo je otpremnica, pa sumnjam da bi se dozvolilo da isti artikl bude pomenut vise puta. U svakom slucaju, ako se isti artikl ne sme pojaviti dva puta na istom dokumentu, onda nam treba jos jedna UNIQUE constraint, UNIQUE (ID, NazivArtikla). I naravno da nam treba i FK koji referencira nekakvu tabelu Artikli, po NazivArtikla (ili nekakav kod artikla, bar code ili slicno, ne utice na princip).
Ako bismo uradili ovo, po predlogu Nikolinom:
Code:
CREATE TABLE MKI_Stavke
(
IdZaglavlja int NOT NULL FOREIGN KEY REFERENCES MKI_Zaglavlje (ID)
, IdStavke int NOT NULL PRIMARY KEY
, NazivArtikla nvarchar(50) NOT NULL
, Kolicina decimal (12,4) NOT NULL
)
GO
onda bi imali potnecijalni problem sa ponavljanjem NazivArtikla za isto IdZaglavlja i kada je to zabranjeno, a zabranjeno je cesce nego sto je dozvoljeno. Da bi to sprecili, treba nam ponovo UNIQUE (IdZaglavlja,NAzivArtikla), kao i u mom resenju. Posto je to unique, zasto bismo uvodili novi IdStavke, autoincement ili ne? JOIN ce biti po Zaglavlje.ID = Stavke.IdZaglavlja, sta jos ima u unikue kombinaciji u Stavkama. Ako pak prihvatimo da (SaglavljeID,NazivArtikla) bude PK, mozemo da ga napravimo da bude CLUSTER INDEX te da nemamo da odrzavamo zasebni ineks, ma kako bio mali i brz.
Da rezimiramo, dodavanje vestackog int kljuca, autoinkrement ili ne, ne donosi nikakve prednosti osim sto se JOIN pise lakse. Posto prirodni kljuc moramo definisati, ne stedimo na prostoru, jer umesto jednog indexa za prirodni kljuc, sada imamo i dodatni, za vestacki kljuc. U roditelj tabelama, makar i imali trokolonski PK, cesto mu je struktura takva da se moze realizovati kroz CLUSTER INDEX cime sama tabela postaje index file, na fizickom nivou, a od toga nista brze nema. Ako prenesemo trokolonski kluc u tabelu dete, (Stavke), onda ima jos vise smisla da kompozitni kljuc (koji ce sad imati 4 kolone) bude baziran na CLUSTER INDEX, pa nemate problema sa brzinom, ako je brzina u pitanju. Argument tipe 'stedi se na disk prostoru' danas naprosto ne vazi, prostor na disku je skoro besplatan.
Ako su kveriji ili aplikacije spore, moze biti mnooogo razloga, a 'velicin' i 'slozenost' kljuca igraju najmanju ulogu u svemu tome. Mozete da imate sve kljuceve jednokolonske, integer, a da vam front end zapinje i skripi zato sto pri unosu nesvesno lokirate tabele, pa transakcije cekaju na red da se izvrse. A pogotovo kad na front endu otvorite transakciju, pa zaboravite da COMMIT na primer. Ili osim PK/FK nemate druge indekse na tabelama, a imate protrebu da vezujete tabele po drugim kolonama. Ili upotrebite na front endu omiljenu taktiku za sprecavanje duplikata - svaki unos se trazi u bazi da li postoji, pa ako ne postoji, ide transakcija, a ako postoji, onda se naravno zaustavlje. Problem je u tome sto je procenat potencijalnih duplikata veoma mali, recimo 1%, a vi 100% puta trosite vreme na search koji ne vraca nista. takodje, nacin na koji je front end povezan sa bazom igra oromnu ulogu, kako povlacite podatke, mnogo ili malol, na koji nacin filtrirate ono sto ce se prikazati i slicno.
Ako postujete pravila projektovanja relacionih baza ( a to nije samo normalizacija ), baza ce sama sebe stititi od gluposti na ulazu, pa cete imati vise vremena da se bavite otklanjanjem problema, nego da ih pustite u bazu, pa ih otkrijete prekasno, pa ne znate sta je uzrok, i tako dalje. Vestacki kljucevi nisu nikad bili niti ce biti deo relacione teorije. Oni igraju ulogu pointera u proceduralnim jezicima, a pointeri u relacionoj teoriji ne postoje. Dodavanjem pointera na bazu, vi na neki nacin 'unapredjujete i dogradjujete' relacionu teoriju. Da podsetim, relaciona teorija je grana matematike, kao bulova algebra ili geometrija i trigonometrija. Sumnjam da bi neko prilagodjavao Pitagorinu teoremu u praksi, zato sto se koren i kvadrat tesko racunaju. Zasto oda prepravljamo relacionu teoriju kad ne preprsavljamo ostale grane matematike?
:-)