Ukoliko ste vlasnik preduzeća, sigurno ste se susreli sa značajem i neophodnošću podataka u vašem poslovanju. Posedovanje resursa za čuvanje i upravljanje bazama podataka dodaje dodatnu vrednost vašem biznisu.
Baze podataka su organizovane prema određenim pravilima i omogućavaju vam da strukturirate podatke u međusobnim vezama, što nas dovodi do relacionih baza podataka. Relacione baze podataka su postale standard u upravljanju podacima od 1970-ih, a na današnjem tržištu su veoma tražene zbog svojih mogućnosti pri obradi podataka.
Iako postoji mnogo dostupnih relacionih baza podataka, MySQL se istakao kao lider i zauzima drugo mesto u svetu, prema statistici iz januara 2022.
U SQL serveru, ograničenja predstavljaju unapred definisana pravila i restrikcije koja se primenjuju na jednu ili više kolona. Ona su vezana za vrednosti u koloni i pomažu u očuvanju integriteta, tačnosti i pouzdanosti podataka u tim kolonama.
Jednostavnije rečeno, u kolonu se mogu uspešno uneti samo oni podaci koji zadovoljavaju pravilo ograničenja. Ukoliko podaci ne ispunjavaju postavljene kriterijume, operacija unosa se prekida.
Ovaj tekst pretpostavlja da ste se već sreli sa relacionim bazama podataka, posebno sa MySQL-om, i da želite da proširite svoje znanje u ovoj oblasti. Na kraju, podeliću sa vama nekoliko saveta za rad sa ograničenjima stranog ključa.
Ograničenja primarnog ključa – kratak pregled
U SQL-u, tabela sadrži kolonu ili nekoliko ključnih vrednosti koje jedinstveno identifikuju svaki red u sistemu. Kolona ili kolone koje čine primarni ključ (PK) tabele imaju zadatak da obezbede integritet entiteta tabele. Ograničenja primarnog ključa garantuju jedinstvenost podataka i često se definišu na koloni identiteta.
Nakon što definišete ograničenje primarnog ključa za vašu tabelu, mehanizam baze podataka automatski nameće jedinstvenost podataka generišući jedinstvene indekse za svaku od primarnih kolona. Primarni ključevi nude veliku prednost pri izvođenju upita omogućavajući brz pristup podacima.
Ako su ograničenja primarnog ključa definisana na više kolona, to se naziva složeni ili kompozitni primarni ključ. U tom slučaju, svaka pojedinačna kolona primarnog ključa može sadržati duplirane vrednosti. Međutim, kombinovane vrednosti iz svih kolona u primarnom ključu moraju biti jedinstvene.
Dobar primer je tabela koja sadrži kolone `id`, `naziv` i `godine`. Ako definišete ograničenje primarnog ključa na kombinaciju `id` i `naziv`, možete imati duplikate `id` ili `naziv` vrednosti. Međutim, svaka kombinacija mora biti jedinstvena da bi se izbegli duplirani redovi. Dakle, možete imati zapise sa `id=1` i `naziv=Petar`, i `godine=22`, i `id=1`, `naziv=Marko` i `godine=27`, ali ne možete imati dva zapisa sa `id=1` i `naziv=Petar` jer kombinacija nije jedinstvena.
Evo nekoliko ključnih aspekata koje treba imati na umu:
- Tabela može imati samo jedno ograničenje primarnog ključa.
- Primarni ključevi ne mogu imati više od 16 kolona i maksimalnu dužinu od 900 znakova.
- Indeksi koje generišu primarni ključevi mogu poboljšati performanse tabele. Međutim, broj grupisanih indeksa u tabeli ne može biti veći od 1, dok je broj negrupisanih indeksa ograničen na 999.
- Kada se grupisanje i negrupisanje ne navedu za ograničenje ključa, grupisanje se automatski koristi.
- Sve kolone koje su deklarisane unutar ograničenja primarnog ključa treba da budu definisane kao `NOT NULL`. Ako to nije slučaj, sve kolone povezane ograničenjem će automatski biti postavljene na `NOT NULL`.
- Kada su primarni ključevi definisani na korisnički definisanom tipu kolone za zajedničko izvršavanje (CLR), implementacija tipa mora podržavati binarni redosled.
Ograničenja stranog ključa – pregled
Strani ključ (FK) je kolona ili kombinacija kolona koja se koristi za stvaranje i uspostavljanje veze između dve tabele, kontrolišući podatke koji se čuvaju u tabeli stranog ključa.
Referenca stranog ključa predstavlja uspostavljanje veze između dve tabele, gde se kolona ili kolone u jednoj tabeli referenciraju na kolonu ili kolone koje čine primarni ključ u drugoj tabeli.
U scenariju reference stranog ključa, veza se uspostavlja između dve tabele kada se kolone koje sadrže primarne ključeve u jednoj tabeli referenciraju na kolone u drugoj tabeli.
Praktičan primer je tabela `Prodaja.Narudžbenica` koja ima strani ključ koji povezuje drugu tabelu, `Prodaja.Osoba`, jer postoji logična veza između prodavaca i narudžbenica.
U ovom slučaju, `ProdavacID` u koloni `Narudžbenica` se povezuje sa kolonom primarnog ključa u tabeli `Osoba`. Strani ključ tabele `Osoba` je kolona `ProdavacID` u `Narudžbenica`.
Ova relacija definiše pravilo da vrednost `ProdavacID` ne može postojati u tabeli `Narudžbenica` ukoliko ne postoji odgovarajuća vrednost u tabeli `Osoba`.
Tabela može referencirati do 253 druge kolone i tabele kao strane ključeve, poznate i kao odlazne reference. Od 2016. godine, SQL server je povećao broj tabela i kolona koje možete referencirati u jednoj tabeli, poznate i kao dolazne reference, sa 253 na 10000. Međutim, ovo povećanje dolazi sa određenim ograničenjima:
- Reference stranog ključa koje prelaze 253 dostupne su samo za `DELETE` DML operacije. `MERGE` i `UPDATE` nisu podržani.
- Tabele sa referencama stranog ključa na sebe imaju najviše 253 reference stranog ključa.
- Za indekse skladišta kolona, tabele optimizovane za memoriju i tabele particionisanih stranih ključeva, reference stranog ključa su ograničene na 253.
Koje su prednosti stranih ključeva?
Kao što je ranije pomenuto, ograničenja stranog ključa igraju ključnu ulogu u zaštiti integriteta i doslednosti podataka u relacionoj bazi podataka. Sledi analiza razloga zašto su ograničenja stranog ključa neophodna:
- Referentni integritet – Ograničenja stranog ključa garantuju da svaki podređeni zapis tabele odgovara zapisu u glavnoj tabeli, čime se obezbeđuje doslednost podataka u obe tabele.
- Sprečavanje napuštenih zapisa – Ako izbrišete glavnu tabelu, ograničenja stranog ključa obezbeđuju da se i vaša pridružena podređena tabela izbriše, sprečavajući slučajeve napuštenih zapisa koji mogu dovesti do nedoslednosti podataka.
- Poboljšane performanse – Ograničenja stranog ključa poboljšavaju performanse upita omogućavajući sistemu za upravljanje bazom podataka da optimizuje upite na osnovu odnosa između tabela.
Indeksi ograničenja stranog ključa
Ograničenja stranog ključa ne stvaraju automatski odgovarajuće indekse kao što to čine primarni ključevi. Indekse za ograničenja stranog ključa možete kreirati ručno i to je korisno iz sledećih razloga:
- Kolone stranog ključa se često koriste u kriterijumima spajanja prilikom kombinovanja podataka iz povezanih tabela u upitima uparivanjem kolona koje su vezane za ograničenje. Indeksi pomažu bazi podataka u pronalaženju povezanih podataka u tabeli stranog ključa.
- Ako menjate ograničenja primarnog ključa, ona se proveravaju u odnosu na strane ključeve u povezanim tabelama.
Kreiranje indeksa nije obavezno. I dalje možete kombinovati podatke iz dve tabele bez navođenja ograničenja primarnog i stranog ključa. Međutim, dodavanje ograničenja stranog ključa optimizuje tabele i njihovo kombinovanje u upit koji koristi ključeve za pronalaženje odgovarajućih podataka. Ako menjate ograničenja primarnog ključa, ona se proveravaju u odnosu na strane ključeve u povezanim tabelama.
Saveti za kreiranje ograničenja stranog ključa u SQL-u
Proveli ste dovoljno vremena razmatrajući zašto su važna ograničenja stranog ključa; sada ćemo se fokusirati na taktike za kreiranje ograničenja stranog ključa.
Polje stranog ključa u tabeli se odnosi na primarni ključ druge tabele. Tabela sa primarnim ključem je vaša glavna tabela, a tabela sa stranim ključem je podređena tabela.
Kreiranje stranog ključa prilikom kreiranja tabele
Kada kreirate tabelu, možete odmah kreirati ograničenje stranog ključa da biste podržali referentni integritet. Evo kako to možete uraditi:
CREATE TABLE narudzbine (
id_narudzbine INT PRIMARY KEY,
id_kupca INT,
datum_narudzbine DATE,
FOREIGN KEY (id_kupca) REFERENCES kupci(id_kupca)
);
Gornji kod kreira tabelu pod nazivom `narudzbine` sa primarnim celobrojnim ključem `id_narudzbine`, drugim celim brojem `id_kupca` i datumom `datum_narudzbine`. U ovom slučaju, ograničenje `FOREIGN KEY` se dodaje koloni `id_kupca` i upućuje na `id_kupca` u tabeli `kupci`.
Kreiranje stranog ključa nakon kreiranja tabele
Pretpostavimo da ste već kreirali tabelu i želite da dodate ograničenje stranog ključa; koristite naredbu `ALTER TABLE` u svom kodu. Pogledajte isječak koda ispod.
ALTER TABLE narudzbine ADD FOREIGN KEY (id_kupca) REFERENCES kupci(id_kupca);
U ovom slučaju, dodali ste ograničenje stranog ključa na kolonu `id_kupca` u tabeli `narudzbine` kako biste referencirali kolonu `id_kupca` u tabeli `kupci`.
Kreiranje stranog ključa bez provere postojećih podataka
Kada dodate ograničenje stranog ključa u tabelu, baza podataka automatski proverava postojeće podatke kako bi osigurala doslednost sa ograničenjem. Međutim, ako znate da su podaci dosledni i želite da dodate ograničenje bez provere doslednosti, evo kako to možete uraditi.
ALTER TABLE narudzbine ADD CONSTRAINT fk_narudzbine_kupci FOREIGN KEY (id_kupca) REFERENCES kupci(id_kupca) NOT VALIDATE;
Komanda `NOT VALIDATE` govori bazi podataka da ne proverava postojeće podatke. Ovaj specifičan slučaj je koristan u određenim situacijama, na primer kada imate ogroman skup podataka i želite da ubrzate proces validacije.
Kreiranje stranog ključa putem DELETE/UPDATE
Dok kreirate ograničenja stranog ključa, možete definisati radnju koja će se preduzeti u slučajevima kada se odgovarajući red ažurira ili briše. U tom slučaju, koristite kaskadna ograničenja referentnog integriteta da biste diktirali radnje koje treba preduzeti. To uključuje:
#1. NEMA RADNJE
Kao i u mnogim drugim bazama podataka, pravilo `NO ACTION` je podrazumevano ponašanje kada kreirate ograničenje stranog ključa. To znači da se neće preduzeti nikakva akcija kada se odgovarajući red izbriše ili ažurira.
Baza podataka će pokrenuti grešku ako je ograničenje stranog ključa prekršeno. Međutim, ovo se ne preporučuje jer može dovesti do problema sa referentnim integritetom. Evo primera kako se to radi:
ALTER TABLE narudzbine ADD CONSTRAINT fk_narudzbine_kupci FOREIGN KEY (id_kupca) REFERENCES kupci(id_kupca) ON DELETE NO ACTION ON UPDATE NO ACTION;
#2. CASCADE
Pravilo `CASCADE` je još jedna opcija za radnje `ON DELETE` i `ON UPDATE` prilikom kreiranja ograničenja stranog ključa. Kada je postavljeno, to znači da kad god se red ažurira ili izbriše u glavnoj tabeli, odgovarajući redovi se ažuriraju ili brišu u podređenoj tabeli. Ova tehnika je moćna u održavanju referentnog integriteta. Evo primera:
ALTER TABLE narudzbine ADD CONSTRAINT fk_narudzbine_kupci FOREIGN KEY (id_kupca) REFERENCES kupci(id_kupca) ON DELETE CASCADE ON UPDATE CASCADE;
Treba biti oprezan pri korišćenju ovog pravila, jer može izazvati neželjene posledice ako se ne koristi pažljivo. Želite izbeći slučajno brisanje previše podataka ili kreiranje kružnih referenci. Stoga, koristite ovu opciju samo kada je to neophodno i sa oprezom.
Postoje određena pravila o korišćenju `CASCADE`:
- Ne možete navesti `CASCADE` ako je kolona vremenske oznake ili deo stranog ili referentnog ključa.
- Ako vaša tabela ima okidač `INSTEAD OF DELETE`, ne možete navesti `ON DELETE CASCADE`.
- Ne možete navesti `ON UPDATE CASCADE` ako vaša tabela ima okidač `INSTEAD OF UPDATE`.
#3. SET NULL
Kada izbrišete ili ažurirate odgovarajući red u glavnoj tabeli, sve vrednosti stranog ključa se postavljaju na `NULL`. Ovo ograničenje zahteva da kolone stranog ključa mogu sadržati `NULL` vrednosti i ne može se navesti za tabele koje imaju `INSTEAD OF UPDATE` okidače. Evo primera kako se to radi:
ALTER TABLE narudzbine ADD CONSTRAINT fk_narudzbine_kupci FOREIGN KEY (id_kupca) REFERENCES kupci(id_kupca) ON DELETE SET NULL ON UPDATE SET NULL
U ovom slučaju, postavili ste kolonu stranog ključa `id_kupca` u tabeli `narudzbine` na `NULL` ako je odgovarajući red u tabeli `kupci` izbrisan ili ažuriran.
#4. SET DEFAULT
Ovde postavljate sve vrednosti koje čine spoljni ključ na podrazumevanu vrednost pod uslovom da se referencirani red u glavnoj tabeli ažurira ili izbriše.
Ovo ograničenje se izvršava ako sve kolone stranog ključa imaju podrazumevane definicije. Ako je kolona `nullable`, njena podrazumevana vrednost se postavlja na `NULL`. Imajte na umu da se ova opcija ne može navesti za tabele sa okidačima `INSTEAD OF UPDATE`. Evo primera:
ALTER TABLE narudzbine ADD CONSTRAINT fk_narudzbine_kupci FOREIGN KEY (id_kupca) REFERENCES kupci(id_kupca) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
U gornjem primeru, postavili ste `id_kupca` u tabeli `narudzbine` na njegovu podrazumevanu vrednost, što se dešava kada se odgovarajući red u tabeli `kupci` izbriše ili ažurira.
Završne reči
U ovom vodiču ste se podsetili na ograničenja primarnog ključa i detaljnije upoznali sa ograničenjima stranog ključa. Takođe ste se upoznali sa nekoliko tehnika za kreiranje ograničenja stranog ključa. Iako postoji više načina za kreiranje ograničenja stranog ključa, ovaj tekst je razjasnio neke od najvažnijih metoda.
Nadamo se da ste naučili neke nove tehnike. Metode ograničenja `CASCADE`, `SET NULL`, `SET DEFAULT` i `NO ACTION` se mogu kombinovati u tabelama sa referentnim relacijama.
Ako vaša tabela naiđe na `NO ACTION`, vraća se na ostala pravila ograničenja. U nekim slučajevima, radnja `DELETE` može pokrenuti kombinaciju ovih pravila, a pravilo `NO ACTION` će biti pokrenuto kao poslednje.
Za više informacija pogledajte SQL varalicu.