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.

Supabase•8mo ago•

2 replies

firepolo (Josh)

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;

Composite key foreign key issue

Similar Threads

Composite key foreign key issue

Similar Threads

Similar Threads

Similar Threads