Thread: conditional constraints
(on behalf of my colleague, Neal Todd) This question is about whether it's possible to have conditionality on a constraint, or rather (presuming it's not possible), how it can be emulated perhaps with a trigger. The scenario is this (but is fairly general anyway)... Table "P" storing projects with a project id primary key. and Table "D" storing diary entries relating to projects with foreign key constraint referencing project ids in table "P". Fine so far, we have referential integrity on the project ids in table "D". However, we need to add diary entries that are for a generic "non-project" category. Without the constraint we could just have a null or dummy (e.g. 0) entry in D's project id foreign key. But with the constraint the referential integrity is broken. We don't want to put a dummy 0 record in the P table to satisfy the constraint because that means having to add conditionality in all queries on P where we want to exclude the dummy record (i.e. "WHERE project_id <> 0"). Ideally, we also don't want to drop the constraint. Presumably it's not possible to have conditionality on a constraint? i.e. be able to say something like "enforce the referential integrity on the project id foreign key if it is not 0". I haven't seen anything in the postgresql docs to indicate it's possible. If that is the case, is there a way to emulate this fairly easily with a trigger? -----------------+ tom dyson t: +44 (0)1608 811870 m: +44 (0)7958 752657 http://torchbox.com
On Fri, Apr 11, 2003 at 12:38:21PM +0100, tom dyson wrote: > (on behalf of my colleague, Neal Todd) > > This question is about whether it's possible to have conditionality on a > constraint, or rather (presuming it's not possible), how it can be emulated > perhaps with a trigger. > > The scenario is this (but is fairly general anyway)... > > Table "P" storing projects with a project id primary key. > and > Table "D" storing diary entries relating to projects with foreign key > constraint referencing project ids in table "P". > > Fine so far, we have referential integrity on the project ids in table "D". > > However, we need to add diary entries that are for a generic "non-project" > category. Without the constraint we could just have a null or dummy (e.g. 0) > entry in D's project id foreign key. But with the constraint the referential > integrity is broken. If you put a NULL, it should do what you want. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "the West won the world not by the superiority of its ideas or values or > religion but rather by its superiority in applying organized violence. > Westerners often forget this fact, non-Westerners never do." > - Samuel P. Huntington
Attachment
tom dyson wrote: > > (on behalf of my colleague, Neal Todd) > > This question is about whether it's possible to have conditionality on a > constraint, or rather (presuming it's not possible), how it can be emulated > perhaps with a trigger. > > The scenario is this (but is fairly general anyway)... > > Table "P" storing projects with a project id primary key. > and > Table "D" storing diary entries relating to projects with foreign key > constraint referencing project ids in table "P". > > Fine so far, we have referential integrity on the project ids in table "D". > > However, we need to add diary entries that are for a generic "non-project" > category. Without the constraint we could just have a null or dummy (e.g. 0) > entry in D's project id foreign key. But with the constraint the referential > integrity is broken. Your assumption is wrong. That's the strange thing about NULL values, we can't tell what they are made of or where they are coming from, but we certainly know what they are good for :-) Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
you pretty much covered all the ways to do it, in the ways you didn't WANT to do it. Unless you add another column or another intermediate table, NULL or a value at the end of your data range are what you are stuck with, IMHO. tom dyson wrote: > (on behalf of my colleague, Neal Todd) > > This question is about whether it's possible to have conditionality on a > constraint, or rather (presuming it's not possible), how it can be emulated > perhaps with a trigger. > > The scenario is this (but is fairly general anyway)... > > Table "P" storing projects with a project id primary key. > and > Table "D" storing diary entries relating to projects with foreign key > constraint referencing project ids in table "P". > > Fine so far, we have referential integrity on the project ids in table "D". > > However, we need to add diary entries that are for a generic "non-project" > category. Without the constraint we could just have a null or dummy (e.g. 0) > entry in D's project id foreign key. But with the constraint the referential > integrity is broken. > > We don't want to put a dummy 0 record in the P table to satisfy the > constraint because that means having to add conditionality in all queries on > P where we want to exclude the dummy record (i.e. "WHERE project_id <> 0"). > > Ideally, we also don't want to drop the constraint. > > Presumably it's not possible to have conditionality on a constraint? i.e. be > able to say something like "enforce the referential integrity on the project > id foreign key if it is not 0". > > I haven't seen anything in the postgresql docs to indicate it's possible. > > If that is the case, is there a way to emulate this fairly easily with a > trigger? > > -----------------+ > tom dyson > t: +44 (0)1608 811870 > m: +44 (0)7958 752657 > http://torchbox.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
Also, (I would love to be corrected nicely if I'm wrong), a column can be NULL'able and have a foreign contraint on it, and have NULL values, right? Jan Wieck wrote: > tom dyson wrote: > >>(on behalf of my colleague, Neal Todd) >> >>This question is about whether it's possible to have conditionality on a >>constraint, or rather (presuming it's not possible), how it can be emulated >>perhaps with a trigger. >> >>The scenario is this (but is fairly general anyway)... >> >>Table "P" storing projects with a project id primary key. >>and >>Table "D" storing diary entries relating to projects with foreign key >>constraint referencing project ids in table "P". >> >>Fine so far, we have referential integrity on the project ids in table "D". >> >>However, we need to add diary entries that are for a generic "non-project" >>category. Without the constraint we could just have a null or dummy (e.g. 0) >>entry in D's project id foreign key. But with the constraint the referential >>integrity is broken. > > > Your assumption is wrong. That's the strange thing about NULL values, we > can't tell what they are made of or where they are coming from, but we > certainly know what they are good for :-) > > > Jan >
Dennis Gearon wrote: > > Also, (I would love to be corrected nicely if I'm wrong), a column can be > NULL'able and have a foreign contraint on it, and have NULL values, right? Right! That's exactly where the wrong assumption was. A FOREIGN KEY does not require a NOT NULL (a PRIMARY KEY implies that). Foreign key columns filled with NULLs are absolutely valid and are not considered a breach of integrity. Jan > > Jan Wieck wrote: > > tom dyson wrote: > > > >>(on behalf of my colleague, Neal Todd) > >> > >>This question is about whether it's possible to have conditionality on a > >>constraint, or rather (presuming it's not possible), how it can be emulated > >>perhaps with a trigger. > >> > >>The scenario is this (but is fairly general anyway)... > >> > >>Table "P" storing projects with a project id primary key. > >>and > >>Table "D" storing diary entries relating to projects with foreign key > >>constraint referencing project ids in table "P". > >> > >>Fine so far, we have referential integrity on the project ids in table "D". > >> > >>However, we need to add diary entries that are for a generic "non-project" > >>category. Without the constraint we could just have a null or dummy (e.g. 0) > >>entry in D's project id foreign key. But with the constraint the referential > >>integrity is broken. > > > > > > Your assumption is wrong. That's the strange thing about NULL values, we > > can't tell what they are made of or where they are coming from, but we > > certainly know what they are good for :-) > > > > > > Jan > > -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Hey, if you're going to send us messages and you want us to reply to you and the list, please empty your mailbox: Your mailbox says: ----------------------- Mail not delivered to phil@goatelecom.com. The mailbox of the user has exceeded the allotted limit. Try sending later.