Diff Between "" And Null

[mahanare]

Hi, i was trying the following.. in mysql.

1)create table test(id INTEGER NOT NULL);

2)INSERT INTO TEST VALUES();

This statement is inserting a record with element "0"

3) then i saw with desc test;

mysql> DESCRIBE TEST;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | | | 0 | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql>

here y it took default value 0 .. i dont know
i didnt mention it in the query.

4)when i give INSERT INTO TEST VALUES(NULL);
then only i am getting error.


5)is there difference between "" and NULL?

if true wat is NULL exactly..
how to get error message when i give
INSERT INTO TEST VALUES();


thnx

[mahanare]

yah.. the help says both are different.

but still my question is.. why it placed default value of "0" when i created table with "NOT NULL" option..

[DelboyDee]

I believe that if a field is set up to be non-null then you have to specify a default value. zero is the default value set by access or the db engine if you do not specify one.

NULL means no variable value specified.

Empty quotes is not NULL, as you have specified an instance of a string variable that contains no text. Therefore you HAVE specified a value for this field.

Not sure how you can get INSERT INTO TEST VALUES(); to return an error as it will always use the default value in that case. Why would you want this to return an error anyway? if your not going to specify a value to the above SQL command then why call the command in the first place?

Hope this helps.

Delboy

[mahanare]

Thank you Delboy.

i was just doing some trials.