Double Foreign key in postgresql

Question

I am trying to use a double primary key as a foreign key.

Create table AAA (
   AAA_id int primary key
);

create table BBB (
   AAA_id int,
   BBB_name character varying(20),
   primary key (AAA_id, BBB_name)
);

create table CCC (
    AAA_id,
    BBB_name,
    DDD_id,

    ... ???
);

table AAA is an object

table BBB is many to one with AAA, and holds aliases of AAA

I am trying to create a pivot table, CCC which holds a many to one between DDD and BBB.

I guess I want something like

create table CCC (
    AAA_id,
    BBB_name,
    DDD_id,
    foreign key (AAA_id, BBB_name) references BBB(AAA_id, BBB_name) on update cascade
);

where both AAA_id and BBB_name are foreign keys, but they are also always referring to the same row in BBB.

but of course that's not valid. what is the best way to produce this type of behavior in postgreSQL?

It's perfectly valid! But it's not called a "double" key, it's a "compound" key. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Feb 13, 2012 at 20:54

Mike Sherrill 'Cat Recall' · Accepted Answer · 2012-02-13 20:37:12Z

21

Create temp table AAA (
   AAA_id int primary key
);

create temp table BBB (
   AAA_id int not null references AAA (AAA_id),
   BBB_name character varying(20) not null,
   primary key (AAA_id, BBB_name)
);

create temp table CCC (
    AAA_id int not null,
    BBB_name character varying(20) not null,
    DDD_id integer not null,
    -- Guessing at the primary key.
    primary key (AAA_id, BBB_name, DDD_id),
    foreign key (AAA_id, BBB_name) references BBB (AAA_id, BBB_name) 
        on update cascade
);

Since {AAA_id, BBB_name} uniquely identify a row in BBB, the foreign key {AAA_id, BBB_name} in CCC will also reference one unique row in BBB.

answered Feb 13, 2012 at 20:37

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Double Foreign key in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related