Search code examples
mysqlinsertnotnull

Do I need to add a constraint in MySQL to specifically not allow empty values from appearing in a not null column?

I wrote schema:

CREATE TABLE user
(
user_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
fname VARCHAR(20) NOT NULL,
lname VARCHAR(20) NOT NULL,
email VARCHAR(30) NOT NULL,
password VARCHAR(60) NOT NULL,
gender ENUM('M', 'F'),
CONSTRAINT pk_user_id PRIMARY KEY (user_id)

)

But when inserting only two values, lets say the users first name and last name, the email and password are left blank, meaning the not null is not working as expected. I do get 2 warnings when I show them, but the behavior is not as expected. Do I have to specifically make constraints to fail an insert to make sure this never happens?

Solution

  • Unfortunately, mysql does not support CHECK constraints.

    They are "valid syntax" in mysql only for compatibility reasons, so you can code them, and the create table SQL will execute OK, but they are ignored thereafter.