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

Re: foreign key constraint to multiple tables

From: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
To: Kevin McCarthy <kemccarthy1(at)gmail(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: foreign key constraint to multiple tables
Date: 2007-08-15 01:49:50
Message-ID: 479394.28523.qm@web31803.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: 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 )
);

In response to

pgsql-novice by date

Next:From: Markus SchaberDate: 2007-08-15 11:58:46
Subject: Re: Install two different versions of postgres which should run in parallel
Previous:From: Oliver ElphickDate: 2007-08-14 22:11:50
Subject: Re: foreign key constraint to multiple tables

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