Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Double Foreign key in postgresql

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?

like image 295
David Chan Avatar asked Feb 13 '12 20:02

David Chan


People also ask

Can a table have two foreign key in PostgreSQL?

A table can have multiple foreign keys depending on its relationships with other tables. In PostgreSQL, you define a foreign key using the foreign key constraint. The foreign key constraint helps maintain the referential integrity of data between the child and parent tables.

Can you have 2 foreign keys in a table?

A table may have multiple foreign keys, and each foreign key can have a different parent table. Each foreign key is enforced independently by the database system. Therefore, cascading relationships between tables can be established using foreign keys.

How many foreign keys can be there in a table in PostgreSQL?

A table can have more than one foreign key constraint. This is used to implement many-to-many relationships between tables.

Can one primary key have two foreign keys?

Yes, it is okay to have two fk to the same pk in one table.


1 Answers

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.

like image 151
Mike Sherrill 'Cat Recall' Avatar answered Sep 25 '22 11:09

Mike Sherrill 'Cat Recall'