UNIQUE constrain using Rails Migrations

Question

Is possible to add UNIQUE constrain, to a column for a new table, using Rails migrations?

I tried like to add the constrain, while creating the table, like this:

t.string :username, unique: true

And it doesn't work

And the other option was:

add_index :authors, :username, unique: true

but it this will create a index, and will not add a unique constrain on the column.

I test it using Rails 5.2 and SQlite.

To clarify, what I trying to archive, is do same as this SQL querie:

CREATE TABLE authors(
    username TEXT UNIQUE
);

but using Rails Migration, if possible.

Note that add_index option will NOT add a unique constrain on the column, even if we get a similar behavior.

Using an index with a uniqueness constraint is the correct way of doing this. If that doesn't work for some reason that differentiates your question from the highly upvoted duplicate question with its many highly upvoted answers then please explain why the linked answers are not working. — anothermh, Apr 29 '20 at 23:11
if you open the database, you will see there the column still not with the UNIQUE constrain, so doesn't solved. Index are not UNIQUE constrain, even if they have a similar behavior. — Sevila, Apr 29 '20 at 23:20
There may be a misunderstanding here; uniqueness constraints are not set on the column, they are set on the index. If it's set on the index then you cannot create a duplicate record. (because the index disallows it) — anothermh, Apr 30 '20 at 00:45
So a SQL querie like this: CREATE TABLE authors(username TEXT UNIQUE); Is not possible with Rails migration? — Sevila, Apr 30 '20 at 01:56
[They're functionally the same thing](https://dba.stackexchange.com/a/147), but I'd use an index over a field constraint. — anothermh, Apr 30 '20 at 06:29
That's true for SQL server, but we can assure that for all others databases? If is the case, so the unique way to have a unique constrain, is having a internal hidden index column? My point is, for small databases, I would prefer to check if the value is unique, just looping over the values, instead of storing a index column, since this verification will be done a very few times, and will be running on client side, and I was hopping to do that on database, not on application validation. — Sevila, May 01 '20 at 17:52
It's safe to assume for all other databases, otherwise you're doing a full table scan to check for uniqueness. (which is what you say you'd prefer to do, which doesn't make sense) In either case (full table scan or using an index) it happens on the database side and is separate from your application. (any query of the database from any application would have the restriction in place) — anothermh, May 01 '20 at 18:09
So the difference is just in case you want to remove the uniqueness, for index we can do this querie: ```DROP INDEX ;``` but for UNIQUE constrain, we can't just drop it, need to erase all the table and create it again, is that? At least SQLite not allow alter table, to remove the unique constrain. — Sevila, May 01 '20 at 18:43
There are other differences but that's a huge one. As for changing it, let's assume you have `COL_1` and it has a UNIQUE constraint: you could create a migration where you add `COL_2` with no constraint, copy the data from `COL_1` to `COL_2`, then remove `COL_1`, then rename `COL_2` to `COL_1`, though you'd have to make sure it was all completed [within a transaction](https://stackoverflow.com/q/37820740/3784008). Even then, locks can be wonky depending on the type of SQL database being used, so be careful! — anothermh, May 01 '20 at 19:00

score -1 · Answer 1 · answered Apr 30 '20 at 00:37

-1

You can do a uniqueness check for the user's input field validation.

With that functionality already been given on the application side, I believe Active Record Migration does have its own limitation on the scope regarding the Backend Relational Database side.

https://guides.rubyonrails.org/active_record_migrations.html#active-record-and-referential-integrity

answered Apr 30 '20 at 00:37

kmnguy25

1
2

Like you say, on validation we can check if the value is unique, but still the column doesn't have UNIQUE constrain. – Sevila Apr 30 '20 at 02:00
The backend logic on unique constraint should not be matter if the only way of data input is from the frontend is what I mean. Hope it helps – kmnguy25 Apr 30 '20 at 04:30
1

Note that you can also Add custom SQL to migration file if Helpers aren't enough https://guides.rubyonrails.org/active_record_migrations.html#when-helpers-aren-t-enough – kmnguy25 Apr 30 '20 at 04:36
So we don't have a helper of migrations to that, but we can use a SQL querie inside migrations, if you make a post I will mark as accept. – Sevila Apr 30 '20 at 15:01

UNIQUE constrain using Rails Migrations

1 Answers1