Adding A Unique Constraint Using A Unique Index

[jvanderen1]

I have seen a lot of discussion online about the best way to add a uniqueness to a set of columns. However, there are a few places which suggest adding a unique index first, followed by a unique constraint using the index (example):

Step 1: Create Unique Index

CREATE UNIQUE INDEX CONCURRENTLY unique_index ON table_name (id);

Step 2: Create Unique Constraint Based on Unique Index

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE USING INDEX unique_index;

Do Sequel migration's support this syntax? I know a unique index can be added via add_index, but can a constraint be added using an index?

[jeremyevans]

This isn't currently supported, but is fairly simple to add.

[jvanderen1]

Thanks @jeremyevans!

I actually tried to get around this by using raw SQL:

Sequel.migration do
  up do
    run 'ALTER TABLE my_table ' \
        'DROP CONSTRAINT my_table_pkey, ' \
        'ADD PRIMARY KEY USING INDEX my_table_pkey_2;'
  end
end

But I end up with a Sequel::Error: mismatched number of keys. However, when I run the same SQL inside of psql, they run successfully. 🤔

[jeremyevans]

The only error Sequel raises that includes mismatched number of keys is an association error, it shouldn't happen during the migration (unless you are loading your models during migrations, which is a terrible idea). Do you have a backtrace showing the error? If running with bin/sequel, use the -t option to output the full backtrace.

[jvanderen1]

Apologies for missing this! It turns out it was nothing to do with Sequel, but that this constraint was actually a unique index. Thanks though!