如何在Mysql中正确设置约束？

CHECKMySQL 会针对插入的行独立评估每个已定义的约束。让我们在以下上下文中检查一下每个原始约束：

1. CONSTRAINT CHK_confirmed CHECK (`confirmed`=0 OR `confirmed`=1)

• 对于插入的行，confirmed为 0。

• 条件0 = 0 OR 0 = 1计算结果为TRUE（因为0 = 0是真的）。

• 因此，该约束得到满足。

2. CONSTRAINT CHK_approved CHECK (`approved`=0 OR `approved`=1)

• 对于插入的行，approved为 0。

• 条件0 = 0 OR 0 = 1计算结果为TRUE。

• 因此，该约束得到满足。

3.CONSTRAINT CHK_emailConfirmedTime_when_notConfirmed CHECK (`confirmed`=0 AND `email_confirmed_time`=TIMESTAMP('1970-01-01 00:00:01'))

• 对于插入的行，confirmed为 0，email_confirmed_time默认为“1970-01-01 00:00:01”。

• 条件0 = 0 AND '1970-01-01 00:00:01' = TIMESTAMP('1970-01-01 00:00:01')计算结果为TRUE（两个部分都AND为真）。

• 因此，这个约束也是满足的。

4.CONSTRAINT CHK_emailConfirmedTime_when_confirmed CHECK (`confirmed`=1 AND `email_confirmed_time`>TIMESTAMP('1970-01-01 00:00:01'))

• 对于插入的行，confirmed为 0，email_confirmed_time为“1970-01-01 00:00:01”。

• 条件0 = 1 AND '1970-01-01 00:00:01' > TIMESTAMP('1970-01-01 00:00:01')计算结果为FALSE（因为0 = 1为假，导致整个AND条件为假）。

• 因此，该约束不满足。

一个解决方法是将两个检查结合起来，如下所示

CONSTRAINT CHK_emailConfirmedTime CHECK (NOT (`confirmed`=1 AND `email_confirmed_time` = TIMESTAMP('1970-01-01 00:00:01')))

这确保了仅当两者都为真时才会违反约束。

现在，当您插入一行带有confirmed = 0默认值的行时，中的email_confirmed_time条件将为假，从而使整个条件为假。然后，运算符将使约束计算为真，插入将成功。confirmed=1ANDANDNOT

当confirmed为 1 且email_confirmed_time为默认值时，AND条件为真，NOT运算符将使约束计算结果为假，从而阻止插入或更新。

我建议将默认值设置为email_confirmed_time而NULL不是1970-01-01 00:00:01，然后相应地更新约束。

检查条件应完全满足。
因此，当confirmed=1AND时email_confirmed_time`>TIMESTAMP('1970-01-01 00:00:01')，两个条件都应该成立。此条件禁止插入任何包含confirmed<>1and 的 行email_confirmed_time`<=TIMESTAMP('1970-01-01 00:00:01')

可能将您的 2 个 CHECK 合并为一个

 CHECK( (`confirmed`=0 AND `email_confirmed_time`=TIMESTAMP('1970-01-01 00:00:01'))
     or (`confirmed`=1 AND `email_confirmed_time`>TIMESTAMP('1970-01-01 00:00:01'))
      )

并可能删除 CONSTRAINT CHK_confirmed CHECK ( confirmed=0 OR confirmed=1)

CREATE TABLE IF NOT EXISTS `user` (
    `id` SERIAL PRIMARY KEY,
    `username` varchar(75) NOT NULL,
    `password` varchar(100) NOT NULL,
    `first_name` varchar(50) NOT NULL,
    `last_name` varchar(50) NOT NULL,
    `role` varchar(25) NOT NULL DEFAULT "{guest}",
    `group` varchar(50) NOT NULL DEFAULT "{read}",
    `logged_in` int NOT NULL DEFAULT 0,
    `confirmed` INTEGER NOT NULL DEFAULT 0,
    `approved` INTEGER NOT NULL DEFAULT 0,
    `email_confirmed_time` TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:01',
    CONSTRAINT CHK_confirmed CHECK (`confirmed`=0 OR `confirmed`=1),
    CONSTRAINT CHK_approved CHECK (`approved`=0 OR `approved`=1),
    CONSTRAINT CHK_emailConfirmedTime_when_Confirmed_or_notConfirmed 
        CHECK( (`confirmed`=0 AND `email_confirmed_time`=TIMESTAMP('1970-01-01 00:00:01'))
               or (`confirmed`=1 AND `email_confirmed_time`>TIMESTAMP('1970-01-01 00:00:01'))
             )
);

insert into `user` (username, `password`, first_name, last_name, `role`, `group`, logged_in
  , confirmed, approved, email_confirmed_time) values 
    ("0", "0", "joe", "dill", "{admin, user, guest}", "{create, read, update, delete}", 0
  , 0, 0, DEFAULT);
;

select * from `user`;

insert into `user` (username, `password`, first_name, last_name, `role`, `group`, logged_in
    , confirmed, approved, email_confirmed_time) values 
("0", "0", "bill", "dill", "{admin, user, guest}", "{create, read, update, delete}", 0
    , 1, 0, DEFAULT)
;

Check constraint 'CHK_emailConfirmedTime_when_Confirmed_or_notConfirmed' is violated.

insert into `user` (username, `password`, first_name, last_name, `role`, `group`, logged_in
    , confirmed, approved, email_confirmed_time) values 
("0", "0", "john", "dill", "{admin, user, guest}", "{create, read, update, delete}", 0
    , 0, 0, TIMESTAMP('1970-01-01 00:00:02'))
;

Check constraint 'CHK_emailConfirmedTime_when_Confirmed_or_notConfirmed' is violated.

insert into `user` (username, `password`, first_name, last_name, `role`, `group`, logged_in
    , confirmed, approved, email_confirmed_time) values 
("0", "0", "mike", "dill", "{admin, user, guest}", "{create, read, update, delete}", 0
    , 1, 0, TIMESTAMP('1970-01-01 00:00:02'))
;

select id,first_name,logged_in, confirmed, approved, email_confirmed_time
from `user`;

小提琴