How can I add DB level Unique constraint to a column in a table in Rails using the migration file?

Question

How can I add DB level unique constraint to a column in a table in Rails using its migration? I googled but those answer seems to involve index, and I don't want to touch index because I'm not sure if it will have bad side effect (because stackoverflow.com/a/3370333/6359753 has a comment saying it will have storage influence). Do I must have it and will it have bad side effect?

This is why even though I have read A migration to add unique constraint to a combination of columns but still asking this question.

" I don't want to touch index because I'm not sure if it will have bad side effect". What side effect are you anticipating? The answer you referenced should work for you... — Mark Merritt
– Mark Merritt, Commented Jul 31, 2018 at 3:39
stackoverflow.com/a/3370333/6359753 has a comment saying it will have stroage influence — Henry Yang
– Henry Yang, Commented Jul 31, 2018 at 4:34

matthewd · Accepted Answer · 2018-07-31 05:50:24Z

1

Yes, the unique index is a necessary part of a unique constraint: the index is how the constraint is enforced [efficiently].

Creating a unique index on its own doesn't always technically create a constraint, but that is almost always an irrelevant distinction.

answered Jul 31, 2018 at 5:50

matthewd

4,43518 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Henry Yang Over a year ago

Thank you, this is really helpful, but I'm concerning about the part where you said "Creating a unique index on its own doesn't always technically create a constraint". If I do add_index :my_table, :my_column, unique: true, will that guarantee my my_column values to be all unique to each other?

matthewd Over a year ago

Yes, it will. Be less concerned: you're doing a simple common thing, and the popular answer will do what you want.

Henry Yang Over a year ago

Thank you, that's all I need to know for now. For the technical part, can you point me to some resource for further reading?

Collectives™ on Stack Overflow

How can I add DB level Unique constraint to a column in a table in Rails using the migration file?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related