SQL mozgalice za SQL majstore

Nedavno sam dobio knjigu "Joe Celko's SQ: Puzzles and Answers" i jako mi se dopala ideja. Naime, g. Celko je pokupio interesantna pitanja sa Oracle, MS SQL, SQL, Access i slicnih foruma, stavio sve u knjigu sa resenjima i komentarima. Pa sam nesto dobio slicnu ideju. Ovako: postavimo pitanje, pitalicu na forumu Baze Podataka, i trazimo SQL odgovor. Znamo da razlicti sistemi imaju razlicit stepen postovanja SQL standarda i razlicite nadogradnje. Nista zato, ko zeli da ponudi odgovor, neka lepo napise u kom sistemu radi. Ovo ce nam pomoci da razvijemo SQL nacin razmisljanja i da ujedno vidimo kako se to moze uraditi u drugim bazama.

Namerno ne zelimo ovo da pitamo na specificnim forumima, da ne bismo dobili specificne odgovore. zelimo da vidimo akko se isti problem moze resiti na vise nacina, i u vise razlicitih okruzenja.

Ako se ovo dopadne posetiocima foruma, mozemo da zamolimo moderatore da pitalice stave na TOP, ili na neki nacin omoguce da se pitalice lako mogu pronaci.

Evo je prva pitalica:

Imam tabelu Students:
(
StudentID, int, PK,
MinistryNumber int, NULL dozvoljene
)

Ovo je zadatak: MinistryNumber ne postoji u momentu kad dodamo novog studenta u tabelu. Stoga moramo dozvoliti NULL vrednosti. Medjutim, kada kasnije dobijemo MinistryNumber, onda taj broj mora biti jedinstven. Dva student ne mogu imati isti MinistryNumber. Znaci, NULL je dozvoljeno, a ako nije NULL, mora biti jedinstvena vrdnost.

Sve je dozvoljeno, pa i promena modela - razbijanje tabela na dve ako neko msili da ce pomoci. Dozvoljeni su i kursori (ali se ne cene mnogo), temp tabele, trigeri, st god hocete.

Evo resenje, koje vazi za MS Access: kreirati unique index po koloni MinistryNumber. I to je sve. U Accessu je moguce imati kolonu gde indeks ignorise NULL vrednosti. Indeks ima property IgnoreNulls, koja je po defaultu Yes, pa mnogi Access programeri nisu ni svesni da se ovo desava u bazi. Dakle, zahvaljujuci odstupanju Accessa od SQL standarda, ovo je trivijalan zadatak. Da li je ovo dobro ili lose odstupanje od standarda, ostaje za neku drugu diskusiju.

U MS SQL na primer, ne mozete da kreitae UNIQUE constraint na kolni koja ima NULL vrednosti. Mozete, ali ce vam dozvoliti samo jedan red sa NULL, sledeci NULL bice tretiran kao duplikat. znaci, sta moze Access, MS SQL ne moze. Zanimljivo, zar ne ;-)

Moji programeri su pronasli jedno veoma originalno resenje u MS SQL, razlicto od svega sto su mi Microsoft VIP sugerisali. Resenje cu pokazati kad dobijemo nekoliko drugih ponuda.

PostgreSQL:

Edit: dodao sam proveru ispravnosti, odnosno onemogućio negativne brojeve.

_{[Ovu poruku je menjao Miloš Baić dana 09.03.2007. u 15:37 GMT+1]}

---

Hvala Milose na resenju.

Posto en radim u PostgreSQL , moram da pitam: da li PostgreSQL dozvoljava NULL vrednosti u unique constraint? Tvoje resenje mi lici potpuno na onu i z Accessa. Da li mozes da imas u tvojoj tabeli ovakvu situaciju:

StudentID, MinistryID
1, NULL
2, NULL
3, 333333
4, 4444444
5, NULL

Molim te obrazlozi resenje i posebno mogucnost vise od jednog reda sa NULL vrednoscu, ukoliko imas UNIQUE constraint/index.



Ima li neko resenje za MS SQL ili ORACLE? MySQL?

Ukoliko nekom atributu dodelimo not null, a postavimo unique constraint na njega NULL neće biti izvodljivo, u suprotnom kao u prethodnom postu biće izvodljivo. Ako je atributu dodeljena mogućnost NULL i unique constraint, moguće je imati više od jednog reda s NULL vrednošću.

BTW, isti kod važi i za MySQL 5.0 (njega imam instaliranog).




_{[Ovu poruku je menjao Miloš Baić dana 08.03.2007. u 18:05 GMT+1]}

---

OK, tako je znaci u PostgreSQL i MySQL 5.0. Kao i Access, UNIQUE constraint ignorise NULL. MS SQL server to ne dozvoljava, moze NULL ali samo jednom. Lepo je znati. Hvala Milosu na objasnjenju.

Posto Access/PostgreSQL/MySQL resenje ne moze da prodje u MS SQL 7/2000/2005, kako onda moze?

Pa postoji vise resenja, specijalizacija, trigeri, ali sumnjam da je to ono orginalno resenje
Posto je StudentID int PK, onda bi mogli to da iskoristimo za vrednost MinistryID. Naravno, treba ostaviti Unique constraint nad MinistryID. Kada se dobije MinistryID, jednostavno se upise i ako ne postoji duplikat sve ce biti dobro

Naravno da postoji vise resenja. No, nema ih bas toliko mnogo. Svrha je da vidimo sva koliko toliko razumna resenja, mkar izgledala neobicno. Necemo da ih delimo na dobar i losa, originalna i neoriginalna. Trigger, zasto da ne. I to je resenje. Zasto ne bismo videli trigger koji obezbedjuje trazeno pravilo?



Bravo. Ovo je veoma zanimljiv pogled na problem. Nije dobro kada su StudentUID i MinistryID slcini, ne mozemo sa sigurnoscu da tvrdimo da je sadrzaj kolone MinistryID upravo to ili privremeno dodeljena vrednost iz polja StudentUID. Naravno, lako je izlistati sve WHERE StudentUID = MinistryID. Ali, to nije bas dobro. Sta ako postoji mogucnost da su MisntryID i StudentUID isti brojevi? 17=17, kako god ga okrenes. I to bi bilo odstupanje od relacione teorije, i to grubo. U kolnu se upisuje sta tamo treba da stoji ili se ostavlja 'prazno' (=NULL). U kolonu 'Boja' ne treba upisivati tezinu samo zato sto je boja nepoznata. Pokusaj malo da razradis ideju o koricenju jedinstvenog StudentUID da bi se nekako NULL u drugoj koloni pretvorilo u nesto jedinstveno.

Ajde da neko pokaze kako bi izgledao trigger koji ovo moze da obezbedi. Ali da ne radi samo sa jednim redom. Triger mora da moze da pokrije insert/update vise od jednog reda u jednoj transakciji.

Da li smo culi za Indexed Views? MS SQL to ima. kako bi izgledao Indexed View koji bi obezbedio da su non-NULL vrednosti unique?

:-)

A sta je sa tabelom zvanom 'counter' koja ima jedno polje cija se vrijednost uveceva za +1 svaki put kad se u ovu tabelu unese student_id?

Sta je sa mogucnoscu da se uradi uvecavanje PK-a za, recimo 1000 ili 10000? Tako dobijas 'slicne' ali ne i iste kljuceve.

---

@ Misko: Zadata tabela ima samo dva polja
(
StudentID, int, PK,
MinistryNumber int, NULL dozvoljene
)

StudentUID ne moze da bude NULL jer je PK.

Nisam rekao da MinistryNumber ima default = 0, pa 1000 + MinistryNumber ne bi donelo nista. Ako je MinsitryNumber IS NULL (1000 + NULL = NULL). Ako sam dobro razumeo, u odredjenim okolnostima mogli bi se u MinistryNumber upisvati brojevi koji su drasticno razliciti od regularnih MinsitryNumbers. Moze, samo je problem kako generisati te brojeve i kako znati kada ih genrisati. Da li bi ih generisao sam sistem, ili front end. Sta ce se desiti kad posle 100 miliona redova taj broj poraste toliko da pocne da lici na validan MinistryNumber? I opet na ostaje problem da znamo sta je sta. Neki redovi bi sadrzali podatak 'MinistryNumber', a neki bi sadrzali recenicu 'ovo nije validan 'MinistryNumber' nego neki broj, da bismo zadovoljili UNIQUE constraint" Ovo narusava 1NF, koja kaze da se u kolonu pakuje ista vrsta podataka. Ne mozemo u neke redove da pakujemo podatak u kolonu, a u nekim drugim redovima da pakujemo u kolonu recenicu koju smo zamaskirali izmisljenim brjem. Nije relaciono, niti po SQL standardu

Ako neko kaze

INSERT INTO Students
(StudentID, MinistryNumber)
VALUES (15, 250)

kako znamo sta je 250? Da li je to validan MinistryNumber ili je to izmisljeni jedinstveni broj koji je veoma razlicit od validnog MinstryNumber? Lisi mi na ono sto je vec predlozeno, samo se umesto studentID upisuje neki broj koji treba tek generisati. Ko bi ga generisao? Front end? Moze, ali mi trazimo resenje u okviru SQL-a.

Ako bi uvodjenje countera pomoglo, ili uvecanje kljuceva za 1000, molim te da pojasnis kako bi se to izvelo. Daj nam novi dizajn tabele i objasni detalje.

Da Zidar ne gresi kad kaze da Access odstupa od SQL standarda u ovome? Ja sam ubedjenja da SQL standard dozvoljava kreiranje UNIQUE indeksa po kolonama koje imaju NULL vrednosti. Mislim da je problem sto MS SQL Server u ovom slucaju ne podrzava standard.

---

A zasto ne razbiti to na dvije tabele, pa ce cijeli koncept raditi na svim bazama?
CREATE TABLE students(
StudentID int PK,
dodatna polja koja trebaju ...
);

i jednu za Ministry:

CREATE TABLE ministries(
MinistryID int PK,
StudentID int FK,
MinistryNumber int NULL dozvoljen
);

Kasnije pri unosu uneses samo u students, a MinistryNumber ces unijeti tako sto povezes tabele preko StudentID.

---

A bas ako smo gadljivi na NULL, onda


@Dejan: Ne sprecavas da jedan student ima dva Ministri broja, a cini mi se da i ministry number moze da ima duplikate.

---

@ Chachka: Vrlo je moguce da gresim. A i nekao sam uvek osecao da je Access pametniji nego MS SQL )

Ako SQL standard dozvoljava NULL vrednosti u vise i kada kolona ima UNIQUE constraint, onda je resenje trivijalno u sistemima koji su ovo implementovali po standardu. Zadatak ostaje za sisteme koji to ne dozvoljavaju, MS SQL za sada.

Hoce li neko da napise triger koji ovo radi, a neko drugi da objasni kako bi se problem resio upotrebom Indexed Views (MS SQL to moze, ponovo ne znam da li su indexed views deo standarda)?

Evo sta su moji priogrameri uradli. Prvo su stupili u strajk kad sam pomenuo trigere. A za indexed view su rekli 'znas, to i nije nista drugo nego jos jedna tabela koju MS SQL materijalizuje, Ako hoces tako, izbacimo kolonu MinistryNumber iz tabele i stavimo je u drugu tabelu, koja isto ima (studentUID, MinistryNumber), gde NULLs nece biti dozvoljene. Onda kazemo da je u novoj tabeli StudentUID = PK, ali je i MinistryNumber UNIQUE NOT NULL. I stavimo da nova tabela ima FK na originalnu. Kad dobijes MinistryID, upises ga u novu tabelu. Ako ga tamo nema, zanci da ga nisi jos dobio. Onda su rekli - "e mi necemo tako. Znamo da ti ne volis Identity, ali cemo tebi u inat da ga iskoristimo, ovako:

- dodamo computed column,
MinstryIDComputed = CASE
WHEN MinistryID IS NULL THEN StudentUID
ELSE MinistryID + StudentUID --- ovo je vlajda jedinstveno uvek ????
END

Onda kazemo da computed column mora biti unique i gotovo. Posto su oni u vecini, tako smo i uradili. I evo radi vec dve godine, bez greske. da li bi moglo na drugim sistemima? Verovatno, ukoliko podrzavaju computed columns i imaju neku vrstu identity (a svi imaju jer svi vole identity ;-)

Sad vidim da je Dejan Topalovic uradio ono sto su moji programeri predlozili, ali nisu uradili. I Chachka je u pravu, Dejanovo resenje je OK, ali treba dodati jos jedan UNIQUE constraint, (StudentUID, MinistryNumber) mora biti unique da ne bi imao jedan student dva ministry broja i obrnuto.

Ceka se jos triger, a ne vidsmo ni indexed view.

Ko bi rekao, naoko prosta pitalica...

Ma na brzinu sam samo pogledao problem. Napisao sam odgovor i zatim sam se odmah setio da nece valjati u slucaju da nam trebaju studenti bez ministryID, ali posto radimo sa signed intiger onda sam se setio da moze da se pomnozi sa -1, tj. predznak da se promeni, ali ovo opet mi ne deluje kao elegantno resenje, licno ne bih koristio, ali me je kopkalo sta je to sto bi moglo biti orginalno a da nisu standarna resenja, pa sam pomislio da nije ovo sto sam izneo

E sad sto se tice viewa moglo bi da izgleda ovako

Auuuuuu, covece za 1 sat vise raspisate da ne mogu ni temu vise pratiti :)

Odnelo mi vremena dok sam testirao na SQL Serveru resenje (WITH SCHEMABINDING me namucio, morao sam da kopam po BOL)




Niste valjda stvarno ovako resili? Sta ako je MinistryID = 2 a StudentID = 1 i obrnuto, MinistryID = 1 a StudentID = 2.

Evo jos jedno resenje koje se zasniva na standardnom SQL-u.

Iako je ovo standardan SQL malo RDBMS-a ga podrzava. Podrzava ga FireBird 1.5 na kojem sam i testirao kod.

Od ovog CHECK CONSTRAINTA je lako napraviti triger(e) za sisteme koji ne podrzavaju SELECT u CHECK-u.

---

Nisam ulazio dublje u rjesavanje i naravno da nisam napisao sve sa constraints. Ovo je samo prijedlog ugrubo, da se razdvoji na dvije tabele, a ovaj koncept podrzavaju svi RDBMS-i. Cak nisam siguran, ni da sam procitao, da li student mora imati jedan MinistryNumber ili ih smije imati vise. Zidar?

---

Da. Posto su nam slicna resenja hteo sam samo da ukazem na razliku.

---

Ovo je bre najbolja zabava na celom forumu

Zaboravio sam da kazem - student moze imati jedan i samo jedan MinistryNumber. Chachka je absolutno u pravu kad kaze da u tabeli koju je Dejan dodao treba oba polja da budu unique. Moj komentar na to isto je potpuno pogresan - ako zahtevamo da kombinacija (StudenID, MinistryNumber) bude unique, onda upravo dozvoljavamo da ima bezbroj takvih kombinacija. Zurio sam da krenem kuci, pa isma mislio sta lupetam.

@negixo: Hvala za view, to je jos jedno moguce resenje koje lepo izgleda. U pravu si za komentar izracunatog polja, nismo ga resili onako kako sam napisao, zurba opet. Nas stvarni slucaj bio je malo drukciji, studentNumber bilo je char(9), samo cifre 0 do 9 dozvoljene, pa su za NULL stavili nesto kao 'A' + CAST(StudentUID as varchar). Ja sam dao primer sa StudentNumber INT, pa sam morao na brzinu da izmisljam resenje. Tvoja ideja se negatvnim StudentUID je prava stvar. Mi smo mogli da upotrebimo i samo StudentUID, jer nam je ministryNumber sa 9 cifara, > 600 000 000, a StudentId je jos uvek na 6 cifara, i raste brzinom od oko 500,000 godisnje, pa bi sve radilo lepo dvadesetak godina barem.

Da rezimiramo:
Zadatak:


Resenje 1 (Milos Bajic), moze u sistemima koji ignorisu NULL vrednosti pri postavljanju unuique constraints: creirati unique constraint/index po polju MinistryNumber

Resenje 2 (Dejan Toplaovic i Chachka), radi u svim sistemina: promeniti model, razbiti tabelu na dve i dovesti ih u vezu 1:1

Resnje 3, (Negyxo), indexirani view, radi naravno u sistemima koji podrzavaju indexirani view:



Moja opaska: view je mogao biti i jednostavniji, na primer



Resenje 4 (Chachka) , cisto SQL, po standardu koji nazalost ni jedan sistem osim Firebird ne podrzava:


Deo koji nije podrzan je upotreba SELECTu CHECK constraint.

Resenje 5, po idejy Negyxo, resili Zidarevi programeri na slican nacin, uvodjenjem calculated column:


pa se doda unique constraint na ovu kolonu. Negativan StudentID garantuje da se nece pomesati vrednosti iz dve kolone. Ako je kolona MinistryNumber varchar tipa, onda se jedinstvenost postize dodavanjem prefixa na CAST(StudentID as varchar), bilo kakvog, koji ce povecati duzinu stringa i tako ga uciniti razlicitim od bilo kog regularnog MinsitryNumber.

Naravno, ostaje resenje sa trigerima, ali cemu triger kad imamo ovoliki izbor dobrih resenja?

Koje je resenje najbolje? Zavisi od toga sta imate. U Accessu i PostgreSQL ocigledno je najbolje (u Accesu i jedino moguce) resenje ono oprvo, sa indeksom. Verujem da ekipa koja ima te mogucnosti ne moze da se nacudi o kakvim nebulozama mi ovde pricamo....

Licno bih izabrao ono za Firebird, ali nazalost ne radi za sada nigde drugo. Potom bih izabrao ono sa razbijanjem tabele na dve i relcijom 1:1. Malo je i iskusnih profesionalaca koji bi se drznuli da ovako razmisljaju, zaboga, kakva je to relacija 1:1, u skoli nas uce da postoji (1:vise) i (vise : jos vise) )

Zasto mi nismo isli na razbijanje tabele? Kolona MinistryNumber je naknadno dodata originalnom modelu, posle 3-4 godine u produkciji, a problem jedinstvenosti se pojavio nekoliko meseci posle dodavanja kolone, kad se ispostavilo da uprkos uveravanju ekipe iz minstarstva obrazovanja, postoje studenti koji nemaju MinistryNumber. Da stvar bude jos gora, saznali smo naknadno da to nije sve. Postoje tri vrste studenta:
- oni koji imaju MinistryNumber (mora biti jedinstven)
- oni koji ga nemaju, ali ce ga dobiti u buducnosti (emigranti tek prispeli u zemlju, doseljeni kanadjani iz druge provincije)
- oni koji ga nikad nece imati (idu u specijalne privetne skole koje ne rade po drzavniom programima). Oni svi imaju vrednost '000 000 000' (razmaci su samo da se lakse cita, u kolonu ide bez razmaka'

Ovo poslednje nas je nateralo da idemo sa kalkulisanom kolonom. Sva resenja, i koja volimo i koja ne volimo ,na neki nacin odvajaju NULL vrednosti od non-NULL vrednosti. A sta da radimo sa gomilom '000 000 000'? Najjeftinije je bilo da uradimo izracnato polje, ovako:


Ovu izracunatu kolonu korsnici niti vide, niti znaju za nju. Svi objekti u bazi i van nje ostali su isti , stored procedures, aplikacije na front endu, absolutno nista se nije promenilo. I ako nam ubace neki novi uslov, dodacemo novi OR/AND u formulu za izracunatu kolonu.

Bas su ove NULL vrednosti nezgodna stvar, zar ne

Ja sam verovatno najstariji po godinama ovde, ali mi to ne daje za pravo da samo ja postavljam mozgalice. Primeri iz prakse su dobrodosli.

Tema nije zatvorena, ako neko im jos neko resenje, sa zadovoljstvom cemo ga prihvatiti.

Hvala svima na trudu.

Edit: Dodati su kod tagovi.

_{[Ovu poruku je menjao chachka dana 02.09.2007. u 01:56 GMT+1]}

Samo jedna mala napomena. Chachka je dao jedno rješenje sa CHECK CONSTRAINT
koje radi u Firebird-u, zato što su tražena različita rješenja.
Najednostavnije je rješenje, za verzije 1.5 i 2.0, (kao i kod PostgreSQL i većine drugih):

Ako je potrebno ispoštovati i dodatne uslove (što je Zidar naknadno pomenuo),
onda se to može uraditi na drugačiji način (probano na veziji 2.0):

Na ovakav način se može iskombinovati da se UNIQUE INDEX ponaša kao i kod MS SQL-a:

U zadnjem primjeru je vrijednost '-1' rezervisana za NULL, pa ako treba dozvoliti negativne vrijednosti,
onda se može iskombinovati i nešto drugo.