[PgSQL] Unique constraint op parent van inherited tabellen

[Maxonic]

Stel je maakt in PostgreSQL een tabel "voertuig". Dit ding heeft een aantal atributen. Zijn key is bijvoorbeeld de integer "vid". Hieraan hang ik een PRIMARY KEY constraint (wat impliceert dat vid unique moet zijn). De default value van "vid" in "voertuig" is een nextval in een sequence.

Nu maak ik twee andere tabellen, "voertuig_auto" en "voertuig_bus". Deze twee tabellen maak ik inherent aan "voertuig". Zei erven dus alle attributen van "voertuig" over.

Tot dusver geen probleem. Maar nu komt er een derde tabel "lading" die een foreign key in "voertuig_bus" heeft. De key van voertuig bus erft hij echter over van zijn pappie en dus zegt "lading" dat hij "vid" in "voertuig_bus" niet kan vinden.

Dit is op zich al een probleem, maar het valt op te lossen. Ik maak voor iedere child een eigen kolom "vid" aan. Deze worden toch samengevoegd met de kolom uit de parent dus zoveel problemen levert dit niet op. Ik hang ook hier als default de nextval van de sequence van de parent aan.

"lading" is nu blij met zijn foreign key en als ik nu voegtuigen in beide child insert volgen de keys elkaar netjes op. Ze gebruiken immers dezelfde sequence. Maar nu geeft ik het "vid" voor een nieuwe entry in "voertuig_auto" expliciet aan. Ik voeg bijvoorbeeld een record met het vid 10 toe. Daarna doe ik hetzelfde voor "voertuig_bus", ook met vid 10. Postgres hoort mij nu op de vingers te tikken want de parent krijgt 2 maal dezelfde waarde in een kolom waar een unique constraint aan hangt. Maar tot mijn verbazing doet Postgres dat helemaal niet. Hij voert het record vrolijk in en bij een select * op voertuig krijg ik:

vid | kleur | eigenaar
------------------------------
1 | zwart | 1
10 | wit | 1
10 | blauw | 2

(3 rows)

Is dit een bug?
Hoe voorkom ik dit?

[jochemd]

Maxonic schreef op woensdag 14 september 2005 @ 16:09:
Stel je maakt in PostgreSQL een tabel "voertuig". Dit ding heeft een aantal atributen. Zijn key is bijvoorbeeld de integer "vid". Hieraan hang ik een PRIMARY KEY constraint (wat impliceert dat vid unique moet zijn). De default value van "vid" in "voertuig" is een nextval in een sequence.
(..)
vid | kleur | eigenaar
------------------------------
1 | zwart | 1
10 | wit | 1
10 | blauw | 2

(3 rows)

Is dit een bug?

Nee, een serious limitation.

[Maxonic]

Hmm, minder. Maar dit moet toch met rules o.i.d. kunnen. Als ik bijvoorbeeld zeg dat men geen "vid"'s mag veranderen en dat bij een insert "vid" niet gespecificeerd mag zijn, dan kan (ervan uitgaande dat de sequences gewoon werken) de bovengeschetste situatie nooit voorkomen? Creëerd dit echter niet gigantisch veel overhead? Of kan het stukken makkelijker dan dit? Met een bepaalde check constraint o.i.d.

[Soultaker]

Voor de primary key kan ik me nog voorstellen dat het by design zo werkt: nu kun je een object maken dat tegelijk een auto en een bus is, wat soms nuttig is. Maar aangezien het ook mis gaat met unique constraints op de parent e.d. lijken het me gewoon onbedoelde bijwerkingen. Ik zou er niets specifieks mee doen, maar gewoon 'handmatig' zorgen dat je data consistent blijft. Je kunt toch niet alle mogelijke gevallen onderscheppen met constraints en er bestaat een kans dat een latere versie van PostgreSQL de fouten oplost.