[SQL] Foreign key

[Verwijderd]

Ik heb in mijn applicatie een optie waar de gebruiker ja of nee moet aangeven.

Als hij ja heeft gekozen worden er 2 andere invoervelden actief. Een veld van deze 2 is een foreign key.

Maar het probleem is dat je geen tabel kunt maken dat er op basis van 1 veld een ander veld gedefineerd wordt.

Dus als de gebruiker toch nee heeft gekozen (en dus die 2 velden niet actief zijn) zou er toch een waarde ingevuld moeten worden voor dat FK veld. Dus ik dacht er aan om in die refererende tabel maar een record te maken die speciaal hiervoor gebruikt kan worden.

Is dit de enige manier of kan het ook anders (beter)?

[Feyd-Rautha]

Is het niet mogelijk om een bepaalde standaardwaarde in dit 'ForeignKey' veld te zetten.

BV:

code:

tblPrim        tblForeign
=======     ==========
PrimID   Naam      ID  PrimID   Naam

1    Leeg      1   2    Blaat
2    Iets      2   1    Blaat2
3    Anders

Als er niets in dat beruchte ForeignKey-veld wordt ingevuld (als je dus eigenlijk het vakje NEE hebt aangevinkt), dat dan gewoon de waarde 1 wordt ingevuld.

.... of ben ik nu verkeerd bezig ?

[Feyd-Rautha]

Misschien een minder mooie oplossing zou zijn dat je Referentiele Integriteit aflegt (in het geval dat je een Access-Database hebt).

Dan kan er, maar ben het niet zeker, een NULL-waarde in dat Foreignkey-veld ingevuld worden dacht ik

[ACM]

Foreign key-velden mogen ook wel NULL zijn als je dat graag wilt.

[Verwijderd]

Forgein key velden mogen niet null zijn dacht ik.

(Ik gebruik trouwens Interbase)

[ACM]

Op maandag 22 juli 2002 14:14 schreef tokkie het volgende:
Forgein key velden mogen niet null zijn dacht ik.

(Ik gebruik trouwens Interbase)

Waarom niet?

code:

create table tester (blaat integer primary key, 
bladiebla integer references enquete(enqueteid));

acm=# insert into tester VALUES (1, NULL);
INSERT 1439603 1
acm=# insert into tester VALUES (2, NULL);
INSERT 1439604 1
acm=# insert into tester VALUES (3, 1);
INSERT 1439605 1
acm=# select * from tester;
 blaat | bladiebla
-------+-----------
     1 |
     2 |
     3 |       1
(3 rows)

[Feyd-Rautha]

Op maandag 22 juli 2002 14:14 schreef tokkie het volgende:
Forgein key velden mogen niet null zijn dacht ik.

(Ik gebruik trouwens Interbase)

'k Heb net eventjes geprobeerd in Access (met Interbase weet ik het dus niet, maar dat zal ook wel zo zijn ) om een NULL-waarde in te vullen in een Foreign key.

Verrassend, maar dat is dus wel degelijk mogelijk.

[Verwijderd]

Mooi,

het kan dus wel gewoon null invullen.

Thnx.

[Feyd-Rautha]

Op maandag 22 juli 2002 14:21 schreef tokkie het volgende:
Mooi,

het kan dus wel gewoon null invullen.

Thnx.

Ik vind dat je toch zoiets eerst eens moet proberen en pas dan posten op het forum.

[ACM]

Op maandag 22 juli 2002 14:23 schreef -Avalanche- het volgende:
Ik vind dat je toch zoiets eerst eens moet proberen en pas dan posten op het forum.

Of wat beter inlezen in de materie van databases...

[Zoijar]

Een foreign key mag wel null zijn, maar het brengt wel wat vervelende dingen met zich mee bij joins.

Wat niet mag is een primairy key die null is.

[Greyfox]

Als je NULL stopt in een foreign key, wat is dan nog het nut van een foreign key?
Dan kun je dat niet meer afdwingen en is de integriteit van je data in het gedrang!
Je moet hier een hele goede reden voor hebben.
Beter is het, naar mijn mening, om in de referentietabel een record op te nemen voor dit doel.

[Verwijderd]

Op maandag 22 juli 2002 15:13 schreef Greyfox het volgende:
Als je NULL stopt in een foreign key, wat is dan nog het nut van een foreign key?

Die heeft op dat moment dan ook geen nut. Het gaat er alleen om als de gebruiker bij een veld True heeft gekozen dat er dan 2 andere velden ingevuld moeten worden en 1 veld is dan een FK.

[Zoijar]

Je referentiele integriteit is dus niet in gedrang met een null foreign key, want het "mag", dwz het is niet in overtreding van de basis integriteit constraints.

null kan meerdere betekenissen hebben, bv. bestaat wel maar is niet bekend, of is niet van toepassing (zoals hier).

Ook kan je gewoon perfect joinen, zoals je verwacht.

[ACM]

Op maandag 22 juli 2002 15:13 schreef Greyfox het volgende:
Als je NULL stopt in een foreign key, wat is dan nog het nut van een foreign key?

Dan is het 'een verwijzing, of niets' ipv 'een verwijzing, eventueel naar een ding dat weer niets representeert'.
Null-foreign-key velden zijn altijd wel om te schrijven naar dingen met altijd een waarde, maar is dat altijd wel nuttig?

Dan kun je dat niet meer afdwingen en is de integriteit van je data in het gedrang!

Neuh, waarom?

Je moet hier een hele goede reden voor hebben.

Dat wel

Beter is het, naar mijn mening, om in de referentietabel een record op te nemen voor dit doel.

Hangt er dus vanaf
Zolang je design goed in elkaar steekt en er dus blijkt dat een NULL waarde toegestaan is, zou ik niet teveel moeite gaan doen het weer om te bouwen...

[dvdhoek]

Let wel op dat, indien je NULL toestaat, een OUTER join nodig hebt (of een aparte check op NULL) om de gegevens uit de twee tabellen in een query te combineren. Anders raak je alle records met NULL kwijt.

[zneek]

MMm, volgens mij is een NULLABLE FK geen probleem. Het is gewoon het gevolg van een optionele relatie, een 0,1 -> 0,n of omgekeerd .

SQL Server 2000 doet er niet moeilijk over. Zodat ik rustig ARTIKELEN kan toevoegen zonder direct de SUBGROEP waar het artikel in hoort direct aan te hoeven geven. Handig als het ontstaan en koppelen van ARTIKELEN niet op dezelfde plek, en door dezelfde persoon gedaan worden.

[dvdhoek]

Op maandag 22 juli 2002 20:08 schreef zneek het volgende:
MMm, volgens mij is een NULLABLE FK geen probleem. Het is gewoon het gevolg van een optionele relatie, een 0,1 -> 0,n of omgekeerd .

SQL Server 2000 doet er niet moeilijk over. Zodat ik rustig ARTIKELEN kan toevoegen zonder direct de SUBGROEP waar het artikel in hoort direct aan te hoeven geven. Handig als het ontstaan en koppelen van ARTIKELEN niet op dezelfde plek, en door dezelfde persoon gedaan worden.

Veelal zie je dan constructies waarin een veld wordt gevuld met een speciale code, bijvoorbeeld:

KEY VALUE
-1 "Nog niet bekend"

Het bouwen van queries is namelijk eenvoudiger als er geen optionele relaties zijn.

[zneek]

Op maandag 22 juli 2002 21:05 schreef dvdhoek het volgende:

[..]

Veelal zie je dan constructies waarin een veld wordt gevuld met een speciale code, bijvoorbeeld:

KEY VALUE
-1 "Nog niet bekend"

Het bouwen van queries is namelijk eenvoudiger als er geen optionele relaties zijn.

En dat wil alleen als je in de gerelateerde tabel een PK met id -1 opneemt. Dat vind ik ook geen nette oplossing. Dan moet je die er weer uitzuiveren in queries.

Mmm, toch wel een interessant probleem. Heeft iemand wat goede boeken over het onderwerp? Wat is database technisch gezien de correcte oplossing? Of is het een kwestie van smaak?

[drm]

-1 zou ik sowieso niet zo snel gebruiken. Dat halveert de capaciteit van een integer veld

<sub>signed / unsigned bedoel ik dus

edit:</sub>
Ik denk dat de mooiste oplossing is om een relatie te leggen naar een default record met id X (0 ofzo), waarvan alle velden op default zijn ingevuld. Bij de meeste velden zal dit toch NULL zijn.

[Zoijar]

Je kan natuurlijk twee tabellen maken. Eigenlijk als het zo is dat dat veld waar dat andere veld van af hangt geen primary key is, dan staat je schema niet in 3NF (dus ook geen BCNF) en misschien zelfs niet in 2nf. Dat is niet echt wenselijk, je moet het dan sowieso in 2 tabellen opsplitsen.

Wat voor relaties heb je nu dan?

[ACM]

Op dinsdag 23 juli 2002 09:47 schreef drm het volgende:
-1 zou ik sowieso niet zo snel gebruiken. Dat halveert de capaciteit van een integer veld

Er is, imho, geen enkele reden om geen negatieve getallen als id's te gebruiken, dat het zelden gedaan wordt is wat anders

_{signed / unsigned bedoel ik dus}

Dat is dus typisch mysql-only en zou je moeten vermijden in je db-ontwerp.

Hoevaak komt het voor dat je 4Miljard ipv 2Miljard records nodig hebt?
En als je daar tegenaan loopt kan je sowieso al beter voor de bigint gaan...

[drm]

ACM:
Hoevaak komt het voor dat je 4Miljard ipv 2Miljard records nodig hebt?

Over MySQL gesproken, 't komt vaker voor dat je 128 ipv 127 records nodig hebt

[ACM]

Op dinsdag 23 juli 2002 14:03 schreef drm het volgende:
Over MySQL gesproken, 't komt vaker voor dat je 128 ipv 127 records nodig hebt

Dan doe je al wat fout
Want met een tinyint kan je ook prima 128 dingen maken
(0 - 127)

[drm]

Tsja, maar helaas begint de autoincrement van MySQL op 1

[ACM]

Op dinsdag 23 juli 2002 14:07 schreef drm het volgende:
Tsja, maar helaas begint de autoincrement van MySQL op 1

Als je van te voren weet dat je er 128 nodig hebt...
Waarom gebruik je dan auto_increment?

En trouwens, je kan net zo goed een signed integer gebruiken als een tinyint.
Performance technisch maakt gaat dat soms zelfs sneller (postgres is bijv sneller met indices op int4 dan op int2...) en als je over die 512-128 bytes al gaat klagen kwa opslag...

[drm]

ACM:
Als je van te voren weet dat je er 128 nodig hebt...
Waarom gebruik je dan auto_increment?

Dat weet je pas na de 127^e

En trouwens, je kan net zo goed een signed integer gebruiken als een tinyint.

signed, zei je? *kuch*Dat is dus typisch mysql-only en zou je moeten vermijden in je db-ontwerp.*kuch*

Performance technisch maakt gaat dat soms zelfs sneller (postgres is bijv sneller met indices op int4 dan op int2...) en als je over die 512-128 bytes al gaat klagen kwa opslag...

Was ik ook niet van plan, maar 't is niet helemaal onzin om gewoon unsigned te kiezen imo

^{</slap geneuzel>}

[ACM]

Op dinsdag 23 juli 2002 14:11 schreef drm het volgende:
Dat weet je pas na de 127^e

Maar dan had je er ook misschien wel 129 of 257 nodig kunnen hebben?
Dus was de keus voor een tinyint al onverantwoord...

signed, zei je? *kuch*Dat is dus typisch mysql-only en zou je moeten vermijden in je db-ontwerp.*kuch*

in oracle:
create table test ( iets integer );
En de iets kolom is een signed integer (number(38) geloof ik zelfs)

DAT is dus niet typisch mysql

Was ik ook niet van plan, maar 't is niet helemaal onzin om gewoon unsigned te kiezen imo

Mja, maar in de meeste gevallen is het ook niet nodig.
Standaard kiezen voor unsigned omdat je dan 2x zoveel op kan slaan is imho niet zo verstandig.
Je verliest namelijk ook de kans op speciale gevallen, anders dan 0...
(als in, de default teller loopt van 1 - MAXINT/2, maar soms wil je een niet default ding die duidelijk zichtbaar is, dan kan je dat natuurlijk met een boolean doen (opzich het netst) maar ook met een negatief getal)

[drm]

hmm. nou dan verschillen we gewoon van mening

[ACM]

Op dinsdag 23 juli 2002 14:23 schreef drm het volgende:
hmm. nou dan verschillen we gewoon van mening

slap excuus hoor

Kom dan gewoon met tegenvoorbeelden

[drm]

ACM:
slap excuus hoor

Kom dan gewoon met tegenvoorbeelden

Goed, wat betreft die tinyint heb je wel gelijk, ik neem zelf praktisch ook nooit een tinyint. Maar ik heb er als gewoonte in laten sluipen dat ik primary keys altijd unsigned maak, omdat ik praktisch altijd van te voren al weet dat ik geen negatieve primary key ga gebruiken. Als ik een speciale waarde in de tabel wil zetten, zal ik dat nooit af laten hangen van de waarde van de primary key, maar eerder een extra veld (een boolean zoals je al zei... dwz. een " boolean ", want we hadden het over MySQL ) of gewoon een NULL in het foreign key veld van de andere tabel.

't Is dan imo ook niet netjes om de PK van een tabel meer semantische waarde te geven dan een puur identificatie veld van het record.

dus.

[ACM]

Op dinsdag 23 juli 2002 14:32 schreef drm het volgende:
't Is dan imo ook niet netjes om de PK van een tabel meer semantische waarde te geven dan een puur identificatie veld van het record.

Goedzo
Maar we hadden het niet perse over primary key's

Magoed, kortweg: we zijn het gewoon eens, maar pakken het anders aan.

Ik probeer mijn tabel-designs altijd zo standaard mogelijk te houden en tik dus ook nooit 'unsigned integer' in, maar dan altijd 'integer'.
Ik heb nog nooit meegemaakt dat ik meer dan 2Miljard records nodig had, laat staan 4Miljard
Mocht dat wel zo zijn dan zou ik toch al voor bigint gekozen hebben.

Op dinsdag 23 juli 2002 14:32 schreef drm het volgende:
Als ik een speciale waarde in de tabel wil zetten, zal ik dat nooit af laten hangen van de waarde van de primary key, maar eerder een extra veld (een boolean zoals je al zei... dwz. een " boolean ", want we hadden het over MySQL ) of gewoon een NULL in het foreign key veld van de andere tabel.

Lol
Maak daar dan "foreign key" veld van

[drm]

ACM:
Lol
Maak daar dan "foreign key" veld van

He bah, ben ik 't alweer met je oneens . Iets kan in MySQL prima een foreign key veld zijn, 't staat alleen nooit zo in de tabel-definitie. Offe... Weet je wat:

"Iets kan in MySQL prima een foreign key veld zijn, 't staat alleen nooit zo in de tabel-definitie."

zo.

[ACM]

Op dinsdag 23 juli 2002 14:44 schreef drm het volgende:
"Iets kan in MySQL prima een foreign key veld zijn, 't staat alleen nooit zo in de tabel-definitie."

Ik vind dat als je de term foreign key gebruikt, je eigenlijk het volgende moet bedoelen:

code:

Create table blaat(
blabla integer not null,
verwijzer integer not null,
primary key(blabla),
foreign key(verwijzer) references brontabel(bronkolom));

Je kan wel zeggen dat iets een foreign key is in mysql, maar aangezien je niet kan garanderen wat er met een foreign key bedoelt wordt is het imho niet een foreign key, maar op zijn best een "foreign key".

[drm]

Je kan toch een ontwerp van een database maken, en die vervolgens implementeren in MySQL?

Dan is de feitelijke database onderhevig aan het ontwerp, niet andersom. Als je dan foreign keys hebt gedefinieerd in je ontwerp, heb je dus automatisch ook in de database, of ze daar nou "fysiek" vastliggen of niet.

imho

[ACM]

Op dinsdag 23 juli 2002 14:57 schreef drm het volgende:
Je kan toch een ontwerp van een database maken, en die vervolgens implementeren in MySQL?

Dan implementeer je iets dat niet je ontwerp is.

Dan is de feitelijke database onderhevig aan het ontwerp, niet andersom. Als je dan foreign keys hebt gedefinieerd in je ontwerp, heb je dus automatisch ook in de database, of ze daar nou "fysiek" vastliggen of niet.

Die foreign keys zaten dan in je ontwerp, maar zijn niet meer terug te vinden in je database...
Volgens mij klopt er dan iets niet

[zneek]

Op dinsdag 23 juli 2002 15:15 schreef ACM het volgende:

[..]

Dan implementeer je iets dat niet je ontwerp is.
[..]

Die foreign keys zaten dan in je ontwerp, maar zijn niet meer terug te vinden in je database...
Volgens mij klopt er dan iets niet

Jawel, want je datamodel kun je op verschillende niveaus afdwingen. Ik geloof zelfs dat tools als Cool:GEN niet anders doen dat constraints op applicatieniveau afdwingen. Het hoeft dus niet betekenen dat je datamodel niet goed geimplementeerd is, het betekent alleen dat je meer werk in je applicatie zal moeten doen. En dat is jammer geneog het gevolg van het gebruik van een "beperkt" RDBMS als MySQL.

[dusty]

wil je over relaties hebben, zal je toch eerst een echte RDBMS moeten nemen, je kan moeilijk over relaties beginnen laat staan Foreign Keys als je MySQL gebruikt.