'phpMyAdmin Accepts NULL in the NOT NULL field

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` = 1

1 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?

mysqlsqldatabasephpmyadmin


Solution 1:[1]

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.

Solution 2:[2]

Maybe you have the rights to change the SQL mode via phpMyAdmin. Go the home (starting) page of phpMyAdmin, then click on Variables and enter "SQL mode" in the filter. Then you have access to some explanations via the question mark, and by clicking on this line you can edit the value of SQL mode and save it.

However this would be just for testing purposes, at the setting will revert to its original value once the server is restarted; hence the need to change the configuration file.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2

Related Questions