Обсуждение: Multiple foreign keys

Hi,

I've just started playing with SQL and postgres, and I have a question
about multiple foreign keys.

Let's say we have three tables "student", "teacher" and "staff", plus a
table "comment" with attributes like "date" and "text". We want zero or
N comments to be associated with each row of the other three tables.

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?

If this is not the correct way to solve the problem, what alternative
solutions are there?

Best,

P.

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)
          )

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "If anyone has material possessions and sees his
      brother in need but has no pity on him, how can the
      love of God be in him?"
                                    I John 3:17

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)
>           )
>

On Fri, 2003-01-24 at 10:54, Prokopis Prokopidis wrote:
> 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 ...

In that case, create a new table fkeys, for example:

create table fkeys (
   id text primary key,
   table TEXT NOT NULL
};

Then each id in students, teachers, etc. has a foreign key reference to
fkeys and each comment similarly has a foreign key on fkeys.

You would need to add your own triggers to drop items from fkeys when
they are deleted from the other tables.


The ideal would be to use inheritance and have a direct fk reference
from comments to the parent table, but that won't work at the moment.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "If anyone has material possessions and sees his
      brother in need but has no pity on him, how can the
      love of God be in him?"
                                    I John 3:17