CODEWITHSUNDEEP
sqlmysqlinsert
pvinis
pvinis

Reputation: 4139

Inserting record with null in SQL when it shouldn't

I'm writing a SQL DB for a simple site, but there is a problem. I created a table 'test', with int test1 NOT NULL, and int test2 NOT NULL. No default values.

When I write 

INSERT INTO test 
  (test1, test2) 
VALUES 
  (1,2)

...all is well.

When I use

INSERT INTO test 
  (test1) 
VALUES 
  (4)

Shouldn't it throw an error? Instead, it adds a record with test1 = 4 and test2 = 0.

What is going on?

Upvotes: 0

Views: 334

Answers (3)

Jason McCreary
Jason McCreary

Reputation: 72961

This may have to do with the mode in MySQL. The default value for column i INT NOT NULL is 0 which will be inserted for you automatically when not in strict mode. If you are in strict mode, your query would fail as you expected.

http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

Upvotes: 1

Ronnis
Ronnis

Reputation: 12833

The following works just as expected on version 5.1.42. What version are you on?

create table test(
   test1 int not null
  ,test2 int not null
);

Query OK, 0 rows affected (0.05 sec)


insert into test(test1, test2) values(1,2);
Query OK, 1 row affected (0.00 sec)

insert into test(test1) values(4);
ERROR 1364 (HY000): Field 'test2' doesn't have a default value

insert into test(test2) values(4);    
ERROR 1364 (HY000): Field 'test1' doesn't have a default value

select * from test;
+-------+-------+
| test1 | test2 |
+-------+-------+
|     1 |     2 |
+-------+-------+
1 row in set (0.00 sec)

Upvotes: 1

Dan J
Dan J

Reputation: 16708

Wait. Is it inserting NULL into test2 in the second case, or 0? Those are not the same thing. Assuming it's inserting 0 as you specify in the question (as opposed to NULL, as implied by the title): I believe it's inserting the default value for the int column datatype.

Upvotes: 0

Related Questions