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
>
