[FIXED] How to make table not to take null values in not null set field in phpmyadmin?

Issue

I have created 1 table in phpmyadmin with not null set values but still its taking null values when I am inserting data using insert query.
how to solve this?

sql used to create table:

CREATE TABLE exmp.student ( id INT( 12 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,   
name VARCHAR( 200 ) NOT NULL ,  
 user_name VARCHAR( 200 ) NOT NULL ,  
 branch VARCHAR( 200 ) NOT NULL )

insert query:

INSERT INTO exmp.student( name ) VALUES ( 'harjeet' ) 

it's taking value into table not showing error.

Solution

When you run the insert query, it is inserting empty strings for the user_name and branch columns. I was able to replicate this.

If you run this query, explicitly setting the non-null columns to be null, then it will return an error:

INSERT INTO student( name, user_name, branch ) VALUES ( 'harjeet', null, null ) 

See the MySql 5.0 reference for Data Type Default Values:

As of MySQL 5.0.2, if a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. This is the same as before 5.0.2.

If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. Exception: If the column is defined as part of a PRIMARY KEY but not explicitly as NOT NULL, MySQL creates it as a NOT NULL column (because PRIMARY KEY columns must be NOT NULL), but also assigns it a DEFAULT clause using the implicit default value. To prevent this, include an explicit NOT NULL in the definition of any PRIMARY KEY column.

For data entry into a NOT NULL column that has no explicit DEFAULT clause, if an INSERT or REPLACE statement includes no value for the column, or an UPDATE statement sets the column to NULL, MySQL handles the column according to the SQL mode in effect at the time:

If strict SQL mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.

If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.

So it looks like your MySql does not have strict mode enabled. The implicit default value for a varchar column is am empty string, so that is what is used. If you want it to return an error, then turn on string mode.

Answered By - Yaakov Ellis