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

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