Feature: Add constraints about postgres-meta

[juanzgc]

Unique constraints seem to have been added in the view when creating a new table but not in the update a column view or create a new column view. Would be very helpful to add this feature in those two views as well.

Note: I've included a few bugs around this as well in the following issue. supabase/supabase#592 (comment)

[DocLabyrinth]

Could it be possible to allow for uniqueness across multiple columns e.g. if you have a table for storing a user's contact details/socials where the columns look something like id (int), user_id (int, foreign key), type (varchar), value (varchar). In that case you'd want to have unique(user_id, type) as a constraint so it wouldn't be possible to add duplicate values for the same type of contact info.

In the UI there's already similar functionality for marking fields as part of the primary key using check boxes. Could it be possible to provide a similar UI for selecting values to be part of a unique constraint? The only downside to that approach would be that it'd only work for one unique constraint per table when I think postgres allows more than one.

[suragch]

Here is a way to manually add a unique constraint on two columns:

1. Open the SQL editor
2. Click "New query"
3. Add the following:

alter table mytable
add constraint unique_col1_col2 unique (col1, col2);

4. Click "Run"

If there are any duplicate rows, you'll have to delete those first before the query will take effect.

[wasabigeek]

Hi! I was thinking of taking a stab at this for columns, but wasn’t exactly sure what interface would be desirable. I see there's already a few constraint options (like is_unique) in https://github.com/supabase/postgres-meta/blob/develop/src/lib/PostgresMetaColumns.ts, so would this be about allowing a user to specify a generic definition as free text when creating a column?

[kiwicopple]

Yeah, in this case we want to start with something as generic as possible, then we can add more specific functionality for anything which isn't covered 👍

would love any contributions!

[soedirgo]

UI stuff aside: yes, we can make constraints be defined on tables the same way e.g. policies and primary keys are. The idea for this feature is for it to be the solution for all kinds of table constraints, including primary keys and UNIQUEs.

[jojomatik]

UI stuff aside: yes, we can make constraints be defined on tables the same way e.g. policies and primary keys are. The idea for this feature is for it to be the solution for all kinds of table constraints, including primary keys and UNIQUEs.

I don't quite fully understand, is it planned to add an UI option to add unique constraints across multiple columns?

I'd also like to be able to do what @DocLabyrinth requests :)

[soedirgo]

This repo is decoupled from the UI - the UI can do ad-hoc queries and thus can support multi-columns constraints, but ideally this would be handled from a dedicated endpoint (e.g. /constraints).