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