create unique constraints per user

Question

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?

Answer 1

Just create the unique constraint over both columns:

UNIQUE (user_id, name)

Per documentation:

This specifies that the combination of values in the indicated columns is unique across the whole table, though any one of the columns need not be (and ordinarily isn't) unique.

But from the looks of it, you really want another table user_location than implements an n:m relation between locations and users - with a primary key on (user_id, location_id).

And don't call the first table "user", that's a reserved word in standard SQL and in Postgres and shouldn't be used as identifier.