Re: conditional constraints

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
>