Uniq index with one optional column, which can be NULL
When you want to do it, Rails allows creating this index.
In this case, table_invitation_id
column is optional.
Example below:
add_index :orders, [:creator_id, :brunch_series_id, :table_invitation_id], unique: true
But then we are able to create two the same records and it’s obscure.
Example below doesn’t raise ActiveRecord::RecordNotUnique: PG::UniqueViolation
2.times { Order.create(creator_id: 1, brunch_series_id: 10, table_invitation_id: nil }
Conclusion
We are not able to do it, Postgres doesn’t have support for it “[…] Null values are not considered equal” - https://www.postgresql.org/docs/current/indexes-unique.html
We just have to create two indexes one when a value is not null and the second when is null.
add_index :orders, [:creator_id, :brunch_series_id, :table_invitation_id],
unique: true,
where: 'table_invitation_id IS NOT NULL',
name: 'uniq_order_with_table_invitation_id'
add_index :orders, [:creator_id, :brunch_series_id],
unique: true,
where: 'table_invitation_id IS NULL',
name: 'uniq_order_without_table_invitation_id'
Then index works fine and code below exists only one Order
2.times { Order.create(creator_id: 1, brunch_series_id: 10, table_invitation_id: nil }
Tweet