Mandatory fields in database

Question

I have created a database table like this:

CREATE TABLE test(
  name VARCHAR(30) NOT NULL, 
  password VARCHAR(40) NOT NULL);

I then added to it like this, which failed as expected.

INSERT INTO test(name, password) VALUES ('test', NULL);

But then when I tried this it inserted without a problem:

INSERT INTO test(name) VALUES ('test');

I tried to create the table differently but it didn't create:

CREATE TABLE test(
 name VARCHAR(30) NOT NULL, 
 password VARCHAR(40) NOT NULL DEFAULT NULL );

So is there a way to get that to create an error when inserting?

Perhaps associated I think I will need to do something else to be able to validate data input into the database anyway and I think I saw something about constraints but I don't think these are supported in mysql? So is the null check and validation something I can't do the database with mysql?

Answer 1

By default, MySQL replaces implicit NULL values with zeroes (or empty strings for string datatypes).

You can work around this by enabling strict mode for your session or server:

SET sql_mode='STRICT_ALL_TABLES'

or add

sql_mode='STRICT_ALL_TABLES'

under [mysqld] in your my.cnf.