Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    14

    Unanswered: NOT NULL field not supplied but still no error

    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)

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    From the MySQL manual http://dev.mysql.com/doc/refman/5.1/en/insert.html

    Quote Originally Posted by MySQL Manual
    Warnings indicates the number of attempts to insert column values that were problematic in some way. Warnings can occur under any of the following conditions:

    * Inserting NULL into a column that has been declared NOT NULL.
    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...

  3. #3
    Join Date
    Jul 2011
    Posts
    14
    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)

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •