为每个用户创建唯一的约束 [英] create unique constraints per user

问题描述

我正在构建一个小型应用程序，此时，我正在创建数据库架构。我将PostgreSQL与续集一起使用，并且进行了两次迁移：

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).

我想我在列上添加的 unique 约束将使列通常唯一，因此在所有用户中，位置名称必须唯一。如果是这样，我如何创建一个约束，使每个用户的名称唯一？

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?

推荐答案

只需在两列上创建唯一约束：

Just create the unique constraint over both columns:

UNIQUE (user_id, name)

每个文档：

指示列
中值的组合在整个表中是唯一的，尽管其中任一列需要
都不是（通常不是唯一的）。

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.

但是从它的外观来看，您真的想要另一个表 user_location 而不是在位置和用户之间实现n：m关系-在（user_id，location_id）上具有主键。

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).

不要将第一个表称为 user ，即在标准SQL和Postgres中保留的字，不应用作标识符。

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.

这篇关于为每个用户创建唯一的约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！