Composite key foreign key issue

I created a table where the primary key is a composite key from :

CREATE TABLE lab_members {
  user_id,
  lab_id,
  PRIMARY KEY (user_id, lab_id)
  FOREIGN KEY (user_id) REFERENCES users(id)
  FOREIGN KEY (lab_id) REFERENCES labs(id)
}

CREATE TABLE lab_members {
  user_id,
  lab_id,
  PRIMARY KEY (user_id, lab_id)
  FOREIGN KEY (user_id) REFERENCES users(id)
  FOREIGN KEY (lab_id) REFERENCES labs(id)
}

I created another table:

CREATE TABLE resources {
  id,
  lab_id,
  member_in_charge_id,
}

CREATE TABLE resources {
  id,
  lab_id,
  member_in_charge_id,
}

I want to reference the lab_members as a FK in my resources table:

ALTER TABLE resources
ADD CONSTRAINT fk_resources_member_in_charge
FOREIGN KEY (lab_id, member_in_charge_id)
REFERENCES lab_members(lab_id, user_id)
ON DELETE CASCADE;

ALTER TABLE resources
ADD CONSTRAINT fk_resources_member_in_charge
FOREIGN KEY (lab_id, member_in_charge_id)
REFERENCES lab_members(lab_id, user_id)
ON DELETE CASCADE;

but when i do so and view my table as an SQL format, it shows the image #1. I also got the create table SQL from "Copy from SQL", and it shows image#2 for my resources table. For some reason, it deals with foreign keys as single-key foreign keys rather than composite. It created a reference between labs and members, which should not happen.

2 Replies

garyaustin•2w ago

I can’t look at this right now but the UI could be struggling with it. I would check with Postgres in the SQL editor or test it to see if it is working. Example https://github.com/supabase/supabase/issues/3583

GitHub

The table editor shows an incorrect foreign key relation with compo...

Bug report Describe the bug The table editor shows an incorrect foreign key relation when a table has a composite foreign key. To Reproduce Create sample tables: drop table if exists sample2; drop ...

garyaustin•2w ago

Also make sure to flush the browser cache if you had single FKs before. Was just reading a PR about invalidating the UI cache after running SQL. Not sure though it applies to more than the current page as it is AI related.

Gaming

Programming

Composite key foreign key issue

Did you find this page helpful?