Create Table Constraint not Working on Update.

[Bob Carpenter]

I'm using mySQL version 5.6.39.

In the SQL below, when I do an update, I'm able to set status to foo, even though I added a constraint during table creation that's suppose to limit the values to either CONFIRMED or UNCONFIRMED.

Any ideas how to fix?

Thanks,

Bob


CREATE TABLE IF NOT EXISTS person (
pid varchar(16) UNIQUE NOT NULL,
firstname varchar(32) NOT NULL,
lastname varchar(32) NOT NULL,
password varchar(32) NOT NULL,
email varchar(64) UNIQUE NOT NULL,
status varchar(12) DEFAULT 'UNCONFIRMED',
datecreated timestamp DEFAULT CURRENT_TIMESTAMP,
datelastupdate timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (pid),
CONSTRAINT chk_status CHECK (status in('CONFIRMED','UNCONFIRMED'))
)
ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;

INSERT INTO person (pid, firstname, lastname, password, email) VALUES
('111AAA', 'Demo', 'User', '1Foobar7!', '[e-mail]'),
('222BBB', 'Bob2', 'User', '1Foobar7!', '[e-mail]'),
('333CCC', 'Bob3', 'User', '1Foobar7!', '[e-mail]');

UPDATE person SET status = 'foo' WHERE pid = '111AAA';

SELECT * FROM person;

111AAA Demo User 1Foobar7! [e-mail] foo 2018-06-09 23:27:59 2018-06-09 23:28:15
222BBB Bob2 User 1Foobar7! [e-mail] UNCONFIRMED 2018-06-09 23:27:59 2018-06-09 23:27:59
333CCC Bob3 User 1Foobar7! [e-mail] UNCONFIRMED 2018-06-09 23:27:59 2018-06-09 23:27:59