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?

Erwin Brandstetter · Accepted Answer · 2014-05-29 13:27:03Z

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.

edited May 29, 2014 at 13:27

answered May 29, 2014 at 12:57

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

7 Comments

Roland Over a year ago

I do not want a n:m relation, a user can have many locations, but a location (or many locations) cannot belong to many users. As for the reserved word, I know that is reserved, but this is not a SQL database (not a Microsoft SQL DB), and I was aware only for those DB this table name is reserved, or am I wrong?

Roland Over a year ago

Yes, I found a list of reserved identifiers too. It seems it is. Do you have a suggestion on what I could rename the table to?

Erwin Brandstetter Over a year ago

SQL is the structured query language and has nothing to do with MS, they just use the term in their product like others do, too. I added a link for reserved words in Postgres above. You still can use "user", but then you have to double-quote it. Always. And that is error-prone. Name it "usr" or "users" or anything else.

Roland Over a year ago

I know that SQL is a language, but there are certain specific aspects of it depending on the database used. I found the same link too and I checked my other table's names for any reserved words and I found a few :) Thanks for pointing it out, I would have never discovered it ...

Erwin Brandstetter Over a year ago

You may be interested in this chapter of the manual about identifiers, too.

|

Collectives™ on Stack Overflow

create unique constraints per user

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related