Hi,

I have set field 'name' as NOT NULL , but still INSERT INTO command given below did not display any error where is have missed 'name' filed.

Could you please explain how to make sure that error gets generated if 'name' filed. is not filled.

Thanks,
newbielgn
mysql> describe student;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| name | varchar(20) | NO | | | |
| sex | enum('F','M') | NO | | | |
| student_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
+------------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO student (sex,student_id) VALUES('M',NULL);
Query OK, 1 row affected, 1 warning (0.00 sec)

From the MySQL manual http://dev.mysql.com/doc/refman/5.1/en/insert.html

You need to issue a SHOW WARNINGS (http://dev.mysql.com/doc/refman/5.1/...-warnings.html) to see the actual error message.

If you change your SQL_MODE to strict (??) than this produce an error and not a warning if I'm not mistaken...

Thank you very much.

After setting sql_mode , error was displayed when 'name' filed was not input.

mysql> set session sql_mode=STRICT_ALL_TABLES;
mysql> insert into student (sex) values ('M');
ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql> insert into student (name,sex) values ("newname",'M');
Query OK, 1 row affected (0.00 sec)

Indeed sql_mode settings need to be changed:

mysql> create table students (name varchar(20) not null, sex enum('F','M') not null, student_id int primary key);
Query OK, 0 rows affected (0.02 sec)

mysql> set sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO students (sex,student_id) VALUES('M',1);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1364 | Field 'name' doesn't have a default value |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> set sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO students (sex,student_id) VALUES('M',2);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

Just to complete the picture: in case the INSERT succeeds (with the warning) an empty string is stored in that column (not a NULL value).