Re: foreign key constraint to multiple tables - Mailing list pgsql-novice

On Mon, 2007-08-13 at 09:14 -0700, Kevin McCarthy wrote:
> I have a table with a column that will join with one of several tables
> based on the value of another column. I'm not sure if this column can
> be set as a foreign key to any of these tables, may be simply bad
> design on my part. The item_type_id column of a record of 'A' will
> reference one of the 'type' table records depending upon what the
> value of the record's item_type column is.
>
> Is there a way to set the item_type_id column in Table A as a foreign
> key to the other tables depending? Or would there be a better way to
> design this schema?
>
> TIA
>
> CREATE TABLE ABC(
>  id SERIAL PRIMARY KEY,
>  item_type character varying(10) NOT NULL,
>  item_type_id INTEGER
> );
>
> CREATE TABLE typeA(
>   typeA_id SERIAL PRIMARY KEY
> );
>
> CREATE TABLE typeB(
>   typeB_id SERIAL PRIMARY KEY
> );
>
> CREATE TABLE typeC(
>   typeC_id SERIAL PRIMARY KEY
> );


You can't use a foreign key directly here, since it has to specify a
single table.

If you only need to check for the existence of a row in one of the three
type* tables, you could create a function returning a boolean result and
call it in a CHECK constraint:

CREATE FUNCTION check_type(varchar(10), integer)
  LANGUAGE plpgsql
  RETURNS boolean
  AS $$
     DECLARE
       tp   ALIAS FOR  $1;
       id   ALIAS FOR  $2;
     BEGIN;
       IF tp = 'A' THEN
          PERFORM * FROM typea WHERE typea_id = id;
       ELSIF tp = 'B' THEN
          PERFORM * FROM typeb WHERE typea_id = id;
       ELSE
          PERFORM * FROM typec WHERE typea_id = id;
       END IF;
       RETURN FOUND;
     END;
  $$;

(Note that a table that you call typeA will be called typea in the
database unless you double-quote the name when creating it.)

CREATE TABLE ABC(
 id SERIAL PRIMARY KEY,
 item_type character varying(10) NOT NULL,
 item_type_id INTEGER,
 CONSTRAINT "correct type"
    CHECK (check_type(item_type, item_type_id))
);


If an update of a type* table has to update rows in ABC, you will need
to create an index table whose primary key is type and id and make a
foreign key from ABC to that.  You would have to create triggers to
update the index table when you insert, update or delete a row in type*.
Alternatively you could set up such triggers directly, without creating
a foreign key reference.


Schema redesign
===============

Is it necessary to have three type tables?  If you have a single id
table, with a type field, you could have a foreign key from ABC to type
and eliminate the type field from ABC.  (If you already have data with
duplicate type ids, you would have to change the data.)
--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.