postgresql – 在数据库中强制执行“至少一个”或“恰好一个”的
假设我们有用户,每个用户可以有多个电子邮件地址
CREATE TABLE emails ( user_id integer,email_address text,is_active boolean ) 一些示例行 user_id | email_address | is_active 1 | foo@bar.com | t 1 | baz@bar.com | f 1 | bar@foo.com | f 2 | ccc@ddd.com | t 我想强制执行一个约束,每个用户只有一个活动地址.我怎样才能在Postgres中做到这一点?我能做到这一点: CREATE UNIQUE INDEX "user_email" ON emails(user_id) WHERE is_active=true; 这可以防止具有多个活动地址的用户,但我相信,它不会防止所有地址设置为false. 如果可能的话,我宁愿避免触发器或pl / pgsql脚本,因为我们目前没有任何这样的&设置起来会很困难.但我很高兴知道“只有这样才能使用触发器或pl / pgsql”,如果是这样的话.
你根本不需要触发器或PL / pgSQL.
您甚至不需要DEFERRABLE约束. 而且您不需要冗余地存储任何信息. 在users表中包含活动电子邮件的ID,从而产生相互引用.有人可能会认为我们需要一个DEFERRABLE约束来解决插入用户及其活动电子邮件的鸡与蛋问题,但使用数据修改CTE我们甚至不需要它. 这会始终为每个用户强制执行一封有效电子邮件: CREATE TABLE users ( user_id serial PRIMARY KEY,username text NOT NULL,email_id int NOT NULL -- FK to active email,constraint added below ); CREATE TABLE email ( email_id serial PRIMARY KEY,user_id int NOT NULL REFERENCES users ON DELETE CASCADE ON UPDATE CASCADE,email text NOT NULL,CONSTRAINT email_fk_uni UNIQUE(user_id,email_id) -- for FK constraint below ); ALTER TABLE users ADD CONSTRAINT active_email_fkey FOREIGN KEY (user_id,email_id) REFERENCES email(user_id,email_id); 从users.email_id中删除NOT NULL约束,使其“最多只能有一个活动的电子邮件”. (您仍然可以为每个用户存储多封电子邮件,但这些电子邮件都不是“活动”.) 您可以使active_email_fkey DEFERRABLE允许更多余地(在同一事务的单独命令中插入用户和电子邮件),但这不是必需的. 我将user_id放在UNIQUE约束email_fk_uni中以优化索引覆盖率.细节: > Is a composite index also good for queries on the first field? 可选视图: CREATE VIEW user_with_active_email AS SELECT * FROM users JOIN email USING (user_id,email_id); 以下是使用活动电子邮件插入新用户的方式(根据需要): WITH new_data(username,email) AS ( VALUES ('usr1','abc@d.com') -- new users with *1* active email,('usr2','def3@d.com'),('usr3','ghi1@d.com') ),u AS ( INSERT INTO users(username,email_id) SELECT n.username,nextval('email_email_id_seq'::regclass) FROM new_data n RETURNING * ) INSERT INTO email(email_id,user_id,email) SELECT u.email_id,u.user_id,n.email FROM u JOIN new_data n USING (username); 具体的困难是我们既没有user_id也没有email_id.两者都是从各自的SEQUENCE提供的序列号.单个RETURNING子句(另一个鸡和蛋问题)无法解决它.解决方案是nextval(),如explained in detail in the linked answer below. 如果您不知道串行列email.email_id的附加序列的名称,则可以替换: nextval('email_email_id_seq'::regclass) 同 nextval(pg_get_serial_sequence('email','email_id')) 以下是添加新“有效”电子邮件的方式: WITH e AS ( INSERT INTO email (user_id,email) VALUES (3,'new_active@d.com') RETURNING * ) UPDATE users u SET email_id = e.email_id FROM e WHERE u.user_id = e.user_id; SQL Fiddle. 如果一些头脑简单的ORM不够聪明,无法应对,那么您可以将SQL命令封装在服务器端函数中. 密切相关,有充分的解释: > How to deal with mutually recursive inserts 还有关系: > Enforcing constraints “two tables away” 关于DEFERRABLE约束: > Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED? 关于nextval()和pg_get_serial_sequence(): > PostgreSQL SELECT primary key as “serial” or “bigserial” (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |