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


|