Thread: foreign key constraint to multiple tables

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@gmail.com

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

There is another that that uses candidate keys instead of autonumber keys:

CREATE TABLE Types (
  type_name varchar( 100 ) NOT NULL UNIQUE,
  type_code varchar( 5 ) NOT NULL
            CHECK ( type_code IN ( 'TypeA', 'TypeB', 'TypeC' )),

  CONSTRAINT Types_Primary_key
  PRIMARY KEY ( type_name, type_code )
);

CREATE TABLE TypeA (
  name_A  varchar( 100 ) NOT NULL UNIQUE,
  type_code varchar( 5 ) NOT NULL DEFAULT 'TypeA'
                         CHECK ( type_code = 'TypeA'),
  attribute_of_A text NOT NULL,

  CONSTRAINT Types_Primary_key
  PRIMARY KEY ( name_A, type_code )
  REFERENCES Types ( type_name, type_code )
);

CREATE TABLE TypeB (
  name_B varchar( 100 ) NOT NULL UNIQUE,
  type_code varchar( 5 ) NOT NULL DEFAULT 'TypeB'
                         CHECK ( type_code = 'TypeB'),
  attribute_of_B numeric(10,4) NOT NULL,

  CONSTRAINT Types_Primary_key
  PRIMARY KEY ( name_A, type_code )
  REFERENCES Types ( type_name, type_code )
);

CREATE TABLE TypeC (
  name_C varchar( 100 ) NOT NULL UNIQUE,
  type_code varchar( 5 ) NOT NULL DEFAULT 'TypeC'
                         CHECK ( type_code = 'TypeC'),
  attribute_of_C TimeStamp With Time Zone NOT NULL
                 DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT Types_Primary_key
  PRIMARY KEY ( name_A, type_code )
  REFERENCES Types ( type_name, type_code )
);