Unable to get my answer from old Questions. MySQL is accepting null string values Even after i have declared it to be NOT NULL while creating table?

Question

CREATE TABLE abc (
    name char(20) NOT NULL, 
    age int NULL, 
    mob varchar NOT NULL UNIQUE
);

I have created Table and defined the name column as NOT NULL.. but still it is accepting the empty string like ' '

INSERT INTO abc VALUES ('',25, 8945252635);

please guide how to overcome this.?

//when i tried with NULL keyword instead of ' ' empty string then it is showing error as expected.

INSERT INTO abc VALUES (null,25, 8945252635);

From the manual - In MySQL, a NULL value means unknown. A NULL value is different from zero (0) or an empty string ''. — RiggsFolly, Sep 12 '22 at 11:25

score 0 · Answer 1 · edited Sep 12 '22 at 11:25

0

To avoid empty values while inserting a record we can have a check in the table DDL itself. Use -> Check(condition)

CREATE TABLE abc (
    name char(20) NOT NULL CHECK(name <> ''), 
    age int NULL, 
    mob varchar NOT NULL UNIQUE
);

edited Sep 12 '22 at 11:25

RiggsFolly

answered Sep 12 '22 at 11:24

Al Farabi

1 Answers1