Skip site navigation (1) Skip section navigation (2)

foreign key constraint to multiple tables

From: "Kevin McCarthy" <kemccarthy1(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: foreign key constraint to multiple tables
Date: 2007-08-14 21:26:33
Message-ID: 4178da10708141426k75f64624ob8666ac6c8c465ed@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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
);


-- 
Kevin McCarthy
kemccarthy1(at)gmail(dot)com

Responses

pgsql-novice by date

Next:From: Oliver ElphickDate: 2007-08-14 22:11:50
Subject: Re: foreign key constraint to multiple tables
Previous:From: Michael GlaesemannDate: 2007-08-14 19:56:06
Subject: Re: data storage for timestamp data type?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group