问题描述
我正在构建一个小型应用程序,此时,我正在创建数据库架构。我将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)
但是从它的外观来看,您真的想要另一个表 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 ,即,不应用作标识符。
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.
这篇关于为每个用户创建唯一的约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!