Mysql – phpMyAdmin Accepts NULL in the NOT NULL field

database, mysql, phpmyadmin, sql

I've created a table with some NOT NULL columns using phpMyAdmin.

CREATE TABLE `TEST` (`ID` INT PRIMARY KEY AUTO_INCREMENT,                      `Firstname` VARCHAR(20) NOT NULL,                      `Lastname` VARCHAR(20) NOT NULL)

There is no problem with INSERT operation. Database prevent to set a NULL field properly.

INSERT INTO `TEST`(`Firstname`, `Lastname`) VALUES ("Peter", null)#1048 - Column 'Lastname' cannot be null

The accepted one is:

INSERT INTO `TEST`(`Firstname`, `Lastname`) VALUES ("Peter", "Smith")1 row inserted. Inserted row id: 1 (Query took 0.0004 sec)

But after I've created a record with non-NULL fields successfully, database allows me to UPDATE these fields to NULL.

UPDATE `TEST` SET `Lastname`=NULL WHERE `ID` = 11 row affected. (Query took 0.0006 sec)

I've tried "NULL" and 'NULL' as well, but database put them in the field as a string.

I'm really confused about this issue. Is this a phpMyAdmin bug or I'm doing something wrong?

Best Solution

You must not have SQL_MODE set to strict on your installation.

Issue

SET SQL_MODE='STRICT_ALL_TABLES'

or add

SQL_MODE='STRICT_ALL_TABLES'

under [mysqld] into your my.cnf

To find my.cnf, look in the MySQL config file C:\xampp\mysql\bin\my.ini.

At the top of that file are some comments:

# You can copy this file to# C:/xampp/mysql/bin/my.cnf to set global options,# mysql-data-dir/my.cnf to set server-specific options (in this# installation this directory is C:/xampp/mysql/data) or# ~/.my.cnf to set user-specific options.

There it tells you where to find your my.cnf file.

Restart your mysql if necessary.