Feature: Add constraints

[kiwicopple]

Feature request

Is your feature request related to a problem? Please describe.

It would be nice to be able to specify constraints within bot a table and a column

For example:

create table profiles (
  username text,
  
  unique(username)
)

alter:

alter table profiles  
   add constraint check_username 
   check (username ~* '^[a-zA-Z0-9_]*$' );

Describe the solution you'd like

It may need a similar treatment as the default values:

postgres-meta/test/integration/index.spec.js

Line 352 in f35c38f

it('/columns default_value with expressions', async () => {

Additional context

• Specify that a column should be unique supabase#770

[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!

[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.

[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).

[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.