Re: foreign key to "compound foreign key table"

On Apr 6, 1:14=A0pm, Lew <l...@[EMAIL PROTECTED]
> wrote:
> shakahsha...@[EMAIL PROTECTED]
 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?
>
> =A0From the PG SQL docs:
>
> > The referenced columns must be the columns of a unique or primary key
co=
nstraint 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) ;