Not null confusion

Question

In phpMyAdmin, as we create table there is not null constraints by default for all fields...and as per my knowledge when we set the constraint to not null...it doesn't allow user to remain field empty which are not null as per this link.....

http://www.techopedia.com/definition/27370/not-null-constraint

now my question is..according to this link, not null means every row of data must contain a value - it cannot be left blank during insert or update operations.....but when i insert data programatically like insert into, i am able to insert data in just two fields and other remains blank although there is not null constraints on that fields ...and still not generates any error....so i don't understand how not null works???

for example, i create table with lets say 5 fields...

   create table myTable 
   (
      Column1 int not null,
      Column2 int not null,
      Column3 int not null,
      Column4 int not null,
      Column5 int not null,
    )

and insert values in just two fields like

 "INSERT INTO myTable (column1,column2) VALUES(10,20)";

but other fields i don't give any '' so it takes 0 as value....and still i am able to insert data with no error...how is that possible??

possible duplicate of [I set a MySQL column to "NOT NULL" but still I can insert an empty value](http://stackoverflow.com/questions/7264702/i-set-a-mysql-column-to-not-null-but-still-i-can-insert-an-empty-value) — Tim Biegeleisen, Apr 28 '15 at 05:25

score 0 · Answer 1 · answered Apr 28 '15 at 05:27

Everything that has the NOT NULL constraint set needs to contain data. If you insert data programmatically and you do not insert data for a NOT NULL cell, then you will get an SQL Error.

e.g. you have this table:

CREATE TABLE test (
id INTEGER PRIMARY_KEY AUTO_INCREMENT,
some_value INTEGER NOT NULL,
some_other_value INTEGER);

Then some_value will contain data in every data set returned, some_other_value may or may not contain data in every data set returned. The only thing to work around this would be this:

CREATE TABLE test (
id INTEGER PRIMARY_KEY AUTO_INCREMENT,
some_value INTEGER NOT NULL DEFAULT 0,
some_other_value INTEGER);

If you now set data programatically and do not set data for some_value, some_value will default to 0 (or to whatever data you set the default to on table creation).

score 0 · Answer 2 · answered Apr 28 '15 at 05:29

Maybe you can refer to this link:

For multiple-row INSERT statements or INSERT INTO ... SELECT statements, the column is set to the implicit default value for the column data type. This is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types. INSERT INTO ... SELECT statements are handled the same way as multiple-row inserts because the server does not examine the result set from the SELECT to see whether it returns a single row. (For a single-row INSERT, no warning occurs when NULL is inserted into a NOT NULL column. Instead, the statement fails with an error.)

score 0 · Answer 3 · answered Apr 28 '15 at 05:34

0

If a column definition includes no explicit DEFAULT value and it is defined as "Not Null" then Mysql will automatically assign default value to the column based on datatype. e.g. 0 for integer and "" for varchar

If you create a unique index on a column, the default value will be accepted with the first row but will give an error with subsequent inserts.

answered Apr 28 '15 at 05:34

mseifert

5,390
9
38
100

look at the edit...i just insert values for 2 columns and other i don't even mention...so for other field it automatically generates 0 when i not mention '' or "" – Bhavin Bhadani Apr 28 '15 at 05:36
Exactly, 0 is the default value (because it is of type int) - so it is implied with an insert that does not set a value. – mseifert Apr 28 '15 at 05:38

Not null confusion

3 Answers3