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?
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.
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.
A table can have more than one foreign key constraint. This is used to implement many-to-many relationships between tables.
Yes, it is okay to have two fk to the same pk in one table.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With