I am building a small app and at this point I am creating the database schema. I use PostgreSQL with Sequel and I have the two migrations:
Sequel.migration do
change do
Sequel::Model.db.run 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp"'
create_table :user do
String :id, :type => :uuid, :primary_key => true, :default => Sequel.function(:uuid_generate_v4)
DateTime :created_at
DateTime :updated_at
index :id, :unique => true
end
end
end
Sequel.migration do
change do
Sequel::Model.db.run 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp"'
create_table :location do
String :id, :type => :uuid, :primary_key => true, :default => Sequel.function(:uuid_generate_v4)
foreign_key :user_id, :user, :type => :uuid
String :name, :unique => true, :null => false # TODO: unique, per user
Float :latitude, :null => false
Float :longitude, :null => false
DateTime :created_at
DateTime :updated_at
index :id, :unique => true
full_text_index :name, :index_type => :gist
end
end
end
As you can see the name
column on the location
table is unique, but I am in doubt about it. Because I want to establish a unique constraint on the name, but per user, so a user can have only one location with the same name but in the entire table many users can have locations with the same name (the relation between user and location is a one to many).
I suppose my unique
constraint added on the column will make column generally unique, so amongst all users the location name must be unique. If so, how do I create a constraint that makes the name
unique on a per user basis?