Foreign key relations page

[mbos2]

Hello.

While adding a foreign key relations between 2 tables, I get the message that FK relations will be added between those 2 tables. But, when I go to the schema I don't see any data about existing relation. It's like it doesn't exist, while in the background it might be there.

UPDATE on upper part: I see that relations are currently not added between table 2 column and table 1 column that is not a primary key.
In addition, could you add an index attribute for all other table columns (that would be optional) so those could be used for foreign key relations?

Also, along with that, it would be nice to have whole section of a list of all FK relations between all tables.
It could be as simple as this:

Relations: table_one - table_two
Those could also be links to open those columns for editing if needed. Furthermore there could be a button to add new relations, a form which would contain 4 fields. Same like when you add FK relation while editing a column.

1. Select table 1
2. Select table one column
3. Select table 2
4. Select table 2 column

[kiwicopple]

HI @mbos2 thanks for dropping in

While adding a foreign key relations between 2 tables, I get the message that FK relations will be added between those 2 tables. But, when I go to the schema I don't see any data about existing relation.

This sounds like a bug. Can you please open an issue for this, but also provide some steps to replicate + screenshots?

it would be nice to have whole section of a list of all FK relations between all tables.

Interesting idea. How would you use this information? Perhaps it's better if we generate this in your code using our (upcoming) CLI?

[mbos2]

Hello.

This sounds like a bug. Can you please open an issue for this, but also provide some steps to replicate + screenshots?

I thought it was an issue, but it is just how SQL databases operates. You need to set FK between PK of table1 and column of the same datatype on table 2. I was trying to set FK between non PK.

Now, that seems like not a bug, but a missing feature. For example, if you look on MySQL database, it offers foreign key constraints between indexed columns that don't have to be primary key. I will post a set of images to show you on this.

• Images show that user_id on both tables are not primary key. They are set as UNIQUE which also set them as some sort of index columns (grey key icon) that can be used for foreign key constraints. That is currently what supabase is missing.
  I would like to propose that to be added as a feature either by this post or a new post.
  Images are from my local test of MySQL powered by XAMPP.

Interesting idea. How would you use this information? Perhaps it's better if we generate this in your code using our (upcoming) CLI?

Well, you could generate it with your CLI, but let me refer to an image I posted from MySQL database.
Constraint shown in one of the image is a separate page, but it is only contraints page for that one table (user_profile).
By having FK relations page( or pages of FK constraints swapped by tab navigation) would be easier for database maintenance of FK relations, especially on large databases with 30+ tables where relations between most tables are a common thing, and since supabase is object-relation database, features I described are something that should exist with a priority to a first one.

• To drop FK constraint you have to delete data from one or both tables
• To change FK constraint name - it's just a name, should be easy enough
• To update data types of constrain columns, I think both columns should be update through same query

Those are some things to consider as an UI action buttons if you consider implementing the feature.

[mbos2]

Hello again.
Did you get the chance to consider this? :)

[kiwicopple]

I guess technically these little up arrows indicate a FK:

Also editing the column specifies that there is a FK:

Were you looking for it in another section that we've missed perhaps @mbos2 ?