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/
Show quoted text
"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
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:
Show quoted text
(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:
Show quoted text
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.
Import Notes
Resolved by subject fallback