The table editor shows an incorrect foreign key relation with composite foreign keys

[grind-t]

Bug report

Describe the bug

The table editor shows an incorrect foreign key relation when a table has a composite foreign key.

To Reproduce

1. Create sample tables:

drop table if exists sample2;
drop table if exists sample1;

create table sample1 (
  pk1 int not null,
  pk2 int not null,
  primary key (pk1, pk2)
);

create table sample2 (
  pk1 int not null,
  pk2 int not null,
  pk3 int not null,
  primary key (pk1, pk2, pk3),
  foreign key (pk1, pk2)
    references sample1 (pk1, pk2)
);

2. Go to the table editor and open the sample1 table or the sample2 table.
3. The information about the foreign key relation is incorrect (see screenshots).

Screenshots

System information

• OS: Windows 10
• Browser: Microsoft Edge

[joshenlim]

Hey there! Sorry it took us so long to get to your issue - we're just looking into this right now

As at the current state of the dashboard the relation descriptions under sample1 doesn't show up (which should be the correct behaviour)

but the relation descriptions under sample2 is still present - this we're looking into a fix

[haexhub]

How is this topic going?
I'm facing the same problem. The foreign keys are not created correctly, even in the SQL Editor.

[AntonEriksson978]

I have the same problem.

My composite foreign key was defined like this:
foreign key (number, course_id) references hole_information (number, course_id),

We can see in the screenshot below how it looks like both course id and number have hole_information.number as its foreign key relation.

[MildTomato]

Hey folks - me and the team here are looking at a visual solution for composite foreign keys today and I'll post an update shortly on what we might propose.

This will be so we officially support composite foreign keys in the dashboard, and issues like this won't be an issue anymore.

We would also like to solve the issue of creating composite foreign keys in the dashboard which will probably mean having some 'mapping UI' so you can easily associate the right columns together to a target table.

will update shortly.

[MildTomato]

Just to let everyone know what the team here thinks we could do:

• move foreign relations to its own section in table/column side panels in table editor
• new UI to allow composite keys for foreign constraints.

Introduce new section for foreign keys:

Introduce UI for 'column mapping' when creating a new foreign key:

Introduce UI to display clearly what the foreign keys of a table are, and what table/columns they reference:

We'll also need to change how these are displayed so they support composite:

[MildTomato]

We'll probably look at implementing this soon, we are just prioritizing some other areas right now.

Feel free for anyone else to take this though and implement it themselves, if you don't want to wait on the supabase team to implement it.

[MildTomato]

Related to #2481 perhaps, as we can maybe handle naming of FK constraints as well

[JeongJuhyeon]

Hi @MildTomato, it seems like half of this was implemented (showing/creating composite FKs) but when inserting a new record, the bug remains.

create table sample1 (
  pk1 int not null,
  pk2 int not null,
  primary key (pk1, pk2)
);

create table sample2 (
  pk1 int not null,
  pk2 int not null,
  pk3 int not null,
  primary key (pk1, pk2, pk3),
  foreign key (pk1, pk2)
    references sample1 (pk1, pk2)
);

What we see when clicking "Insert row":

"Has a foreign key relation to public.sample1.pk1" under each of pk1 and pk2 (instead of respectively __.pk1 and __.pk2). And when clicking the Select record button for the pk2 field and selecting a row, the data from the selected row's pk1 column gets taken, instead of the correct pk2 column.

Any updates on this?