Re: foreign key constraint to multiple tables

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(at)lfix(dot)co(dot)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.