Como definir restrições corretamente no Mysql?

O MySQL avalia cada uma das CHECKrestrições definidas independentemente para a linha que está sendo inserida. Vamos examinar cada uma das suas restrições originais neste contexto:

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

• Para a linha inserida, confirmedé 0.

• A condição 0 = 0 OR 0 = 1é avaliada como TRUE(porque 0 = 0é verdadeira).

• Portanto, essa restrição é satisfeita.

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

• Para a linha inserida, approvedé 0.

• A condição 0 = 0 OR 0 = 1é avaliada como TRUE.

• Portanto, essa restrição é satisfeita.

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

• Para a linha inserida, confirmedé 0 e email_confirmed_timeo padrão é '1970-01-01 00:00:01'.

• A condição 0 = 0 AND '1970-01-01 00:00:01' = TIMESTAMP('1970-01-01 00:00:01')é avaliada como TRUE(ambas as partes de ANDsão verdadeiras).

• Portanto, essa restrição também é satisfeita.

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

• Para a linha inserida, confirmedé 0 e email_confirmed_timeé '1970-01-01 00:00:01'.

• A condição 0 = 1 AND '1970-01-01 00:00:01' > TIMESTAMP('1970-01-01 00:00:01')é avaliada como FALSE(porque 0 = 1é falsa, tornando toda a ANDcondição falsa).

• Portanto, essa restrição não é satisfeita .

Uma solução poderia ser combinar as duas verificações conforme abaixo

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

Isso garante que a restrição seja violada somente quando ambas forem verdadeiras.

Agora, ao inserir uma linha com confirmed = 0e o padrão email_confirmed_time, a condição confirmed=1dentro do ANDserá falsa, tornando toda a ANDcondição falsa. O NOToperador fará com que a restrição seja avaliada como verdadeira, e a inserção será bem-sucedida.

Quando confirmedfor 1 e email_confirmed_timefor o valor padrão, a ANDcondição será verdadeira e o NOToperador fará com que a restrição seja avaliada como falsa, impedindo assim a inserção ou atualização.

Eu recomendaria definir o valor padrão de email_confirmed_timepara NULLem vez de 1970-01-01 00:00:01e, então, atualizar as restrições adequadamente.

As condições de verificação devem ser totalmente satisfeitas.
Portanto, quando confirmed=1AND email_confirmed_time`>TIMESTAMP('1970-01-01 00:00:01'), ambas as condições devem ser verdadeiras. Essas condições proíbem a inserção de qualquer linha com confirmed<>1AND. email_confirmed_time`<=TIMESTAMP('1970-01-01 00:00:01')

Possível juntar seus 2 CHEQUES em um

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

E pode remover CONSTRAINT CHK_confirmed CHECK ( confirmed=0 OU 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`;

violino