Talk About Network

Google


Register and Login
Nick
Password
Register create new account Sign up is FREE and you can post replies, new topics, bookmark posts and more!
Recover lost password


Data Bases > Pgsql Sql > foreign key to ...
Latest [ Topics | Posts ] Archive Post A New Topic Post a Reply
<< Topic < Post Post 1 of 3 Topic 3390 of 3596
Post > Topic >>

foreign key to "compound foreign key table"

by "shakahshakah@[EMAIL PROTECTED] " <shakahshakah@[EMAIL PROTECTED] > Apr 4, 2008 at 08:30 AM

Is it possible via a FOREIGN KEY constraint to constrain a single
column value to a value in another table that's really unique on a
compound key?

I'm redesigning a schema which uses a single status table to hold
several distinct sets of statuses, simplified case below:

BEGIN ;
CREATE TABLE statuses (
  type     char(1) NOT NULL
 ,status   integer NOT NULL
 ,meaning  varchar(64) NOT NULL
) ;
ALTER TABLE statuses ADD PRIMARY KEY(type,status) ;

INSERT INTO statuses(type,status,meaning) VALUES ('U',0,'created') ;
INSERT INTO statuses(type,status,meaning) VALUES ('G',1,'created') ;
INSERT INTO statuses(type,status,meaning) VALUES ('G',2,'closed') ;

-- ...skeletal tables for this example
CREATE TABLE tmpusers (
  status integer NOT NULL
 ,name   varchar(50) NOT NULL
) ;
CREATE TABLE tmpgroups (
  status integer NOT NULL
 ,name   varchar(50) NOT NULL
) ;

-- ...following does not work (is there syntax that does?)
ALTER TABLE tmpusers
  ADD CONSTRAINT fk_tmpusers_status
  FOREIGN KEY ('U',status)
  REFERENCES statuses(type,status) ;

ALTER TABLE tmpgroups
  ADD CONSTRAINT fk_tmpgroups_status
  FOREIGN KEY ('G',status)
  REFERENCES statuses(type,status) ;
 




 3 Posts in Topic:
foreign key to "compound foreign key table"
"shakahshakah@[EMAIL  2008-04-04 08:30:53 
Re: foreign key to "compound foreign key table"
Lew <lew@[EMAIL PROTEC  2008-04-06 13:14:13 
Re: foreign key to "compound foreign key table"
shakahshakah <shakahsh  2008-04-07 04:53:40 

Post A Reply:
  Go here to Signup

AddThis Feed Button


About - Advertising - Contact - Frequently Asked Questions - Privacy Policy - Terms of Use - Signup

Contact
tan13V112 Thu Jul 24 6:43:19 CDT 2008.