SEO

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

shakahshakah@gmail.com wrote:
> 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?

From the PG SQL docs:
> The referenced columns must be the columns of a unique or primary key constraint in the referenced table.

--
Lew

On Apr 6, 1:14*pm, Lew <l...@lewscanon.com> wrote:
> shakahsha...@gmail.com wrote:
> > 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?
>
> *From the PG SQL docs:
>
> > The referenced columns must be the columns of a unique or primary key constraint in the referenced table.
>
> --
> Lew

In this case the referenced columns ("type" and "status") are the
primary key of the referenced table, unless I'm misreading that.

To put it another way, the following works just fine. I'm just
wondering if there's a syntax where I can avoid the explicit
definition of the implied status_type column:

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
,status_type char(1) NOT NULL -- always set to 'U' to allow FK
constraint?
,name varchar(50) NOT NULL
) ;

-- ...works fine now, but can I avoid the implied
-- status_type with a literal 'U' in the FK constraint DDL ?
ALTER TABLE tmpusers
ADD CONSTRAINT fk_tmpusers_status
FOREIGN KEY (status_type,status)
REFERENCES statuses(type,status) ;