phpMyAdmin Accepts NULL in the NOT NULL field

Question

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?

possible duplicate of Why does MySQL allow to update a NOT NULL column to NULL? — Mark
– Mark, Commented Sep 2, 2013 at 1:18
Does this answer your question? A constraint to prevent the insert of an empty string in MySQL — user1844933
– user1844933, Commented Dec 18, 2023 at 2:34

Mark · Accepted Answer · 2013-09-02 09:23:21Z

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.

edited Sep 2, 2013 at 9:23

answered Sep 2, 2013 at 1:19

Mark

8,47115 gold badges52 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alireza Sobhani Over a year ago

I've tried the command on phpMyAdmin. It's executed properly, but still accept NULL on update. the problem hasn't solved.where should I run this code?

Alireza Sobhani Over a year ago

Yes, I've already read the article. thanks for the link. but I'm using online phpMyAdmin and I don't have access to any .cnf or .ini file to change them.

Parth Kapadia Over a year ago

@ChristianMark even in my case the change does not work, instead it stops my MySQLDatabase from starting completely

Marc Delisle · Accepted Answer · 2013-09-02 16:43:51Z

0

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.

edited Sep 2, 2013 at 16:43

answered Sep 2, 2013 at 16:05

Marc Delisle

9,0253 gold badges32 silver badges29 bronze badges

1 Comment

Alireza Sobhani Over a year ago

I've tried SET SQL_MODE='STRICT_ALL_TABLES', but it didn't work.also tried SET @@global.sql_mode='STRICT_ALL_TABLES', and this one required SUPER privilege(s), Unfortunately I don't have.

Collectives™ on Stack Overflow

phpMyAdmin Accepts NULL in the NOT NULL field

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related