create unique constraints per user in tables with n:m relation

Question

I have asked a question a few moments ago, create unique constraints per user and the answer was very simple.

For creating a unique index on a column, but on a per user basis, all I have to do is:

unique [:user_id, :name] # SQL syntax: UNIQUE (user_id, name)

But the relation between the user table and the table that references the user_id is a 1:n (user to location), so I have a foreign_key inside the location table which references user_id. This question is about a n:m relation between two tables and adding a unique constraint on one of the tables.

Sequel.migration do
    change do

        Sequel::Model.db.run 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp"'

        create_table :customer 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 :role do
            String :id, :type => :uuid, :primary_key => true, :default => Sequel.function(:uuid_generate_v4)


            String :name, :unique => true, :null => false # TODO: unique, per customer


            DateTime :created_at
            DateTime :updated_at


            unique [:customer_id, :name]

            index :id, :unique => true
            full_text_index :name, :index_type => :gist
        end
    end
end

The above code illustrates the two tables I mentioned have a n:m relation (customer to role), the following table illustrates the join table with the foreign keys for both the tables:

Sequel.migration do
    change do

        Sequel::Model.db.run 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp"'

        create_table :customer_role do
            String :id, :type => :uuid, :primary_key => true, :default => Sequel.function(:uuid_generate_v4)

            foreign_key :customer_id, :customer, :type => :uuid
            foreign_key :role_id, :role, :type => :uuid

            index :id, :unique => true
        end
    end
end

What I would like to do is to declare a unique constraint such as UNIQUE (customer_id, :name) on the role table. But I cannot do that, so how do I achieve that in another way?

Answer 1

What I would like to do is to declare a unique constraint such as UNIQUE (customer_id, :name) on the role table. But I cannot do that, so how do I achieve that in another way?

The essence of your problem seems to be that the column role.customer_id doesn't exist. In fact, I'm pretty sure that column shouldn't exist, so that part's actually good.

At the very least, you need

unique [:customer_id, :role_id]

in "customer_role".