Yes, but "3 tables" was just an example. What if I have 10 or 1000
tables that need a comment? This CHECK solution becomes "ugly" very
quickly. There must be another way ...
Thanks Oliver.
P.
Oliver Elphick wrote:
> On Fri, 2003-01-24 at 08:25, Prokopis Prokopidis wrote:
>
>>I was thinking of creating three foreign keys in the "comment" table,
>>say "fk_student", "fk_teacher" and "fk_staff". Now, is there a way to
>>impose a constraint that a comment row must have one and only one NOT
>>NULL fk value?
>
>
> Add this table constraint:
> CONSTRAINT "only one not null"
> CHECK ((fk_student IS NOT NULL AND fk_teacher IS NULL AND fk_student IS NULL)
> OR (fk_student IS NULL AND fk_teacher IS NOT NULL AND fk_student IS NULL)
> OR (fk_student IS NULL AND fk_teacher IS NULL AND fk_student IS NOT NULL)
> )
>