postgresql – 在数据库中强制执行“至少一个”或“恰好一个”的约束_sql

CREATE table emails (    user_ID integer,email_address text,is_active boolean)

一些示例行

user_ID | email_address | is_active1       | foo@bar.com   | t1       | baz@bar.com   | f1       | bar@foo.com   | f2       | 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_fkeyFOREIGN 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 ASSELECT * 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.emailFROM   uJOIN   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 uSET    email_ID = e.email_IDFROM   eWHERE  u.user_ID = e.user_ID;

SQL Fiddle.

如果一些头脑简单的ORM不够聪明,无法应对,那么您可以将sql命令封装在服务器端函数中.

密切相关,有充分的解释：

> How to deal with mutually recursive inserts
> Complex foreign key constraint in SQLAlchemy

还有关系：

> 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”
> Duplicate row with Primary Key in PostgreSQL

总结

以上是内存溢出为你收集整理的postgresql – 在数据库中强制执行“至少一个”或“恰好一个”的约束全部内容，希望文章能够帮你解决postgresql – 在数据库中强制执行“至少一个”或“恰好一个”的约束所遇到的程序开发问题。