NULL meaning in MySQL

[maccatalan]

Hi.

I'm just trying to understand what NULL means as property of a column in MySQL.

For example, when I do :

CREATE TABLE `toto` (
`c1` INT NOT NULL,
`c2` INT NULL
);

what's the difference beetwen c1 and c2 (excluding the name) ?

I think that means I can put an empty field into c2 and not in c1, but am I right ?

Thank you,
Pierre.

 

[iconara]

try the DESCRIBE function:

> DESCRIBE toto;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) |      |     | 0       |       |
| c2    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

The first field cannot be null (the "Null" column is not "YES") and the second column is null by default (the "Default" column...)



theo

 

[maccatalan]

thank you but I found the answer. Here it is since your last is not the answer I was waiting for (but thank you anyway) :

NULL -> the field in an Insert can be null (no value)
NOT NULL -> the field must contain some data when Insert is called.

Pbl : with some bases NULL can implies some problems with Select methods.


Sorry for this question, it was so easy (maybe too much easy )
Pierre

 

[iconara]

I think you got it wrong

c2 INT NULL

is (most probably) the same as

c2 INT DEFAULT NULL

which means that the default value of the field should be NULL
which is the same as

c2 INT

(since any field will, by default, be NULL if not explicitly given a value)


this site needs a webprogramming board (I have made the suggestion to the admin... but no response)


theo

 

[maccatalan]

you're right. the three works, but it was just to be explicit. Writing NULL in the definition (even if it is by default null) I do insist on it (for the reader, not the MySQL server who knows it is the same thing )

-> good idea of creating a Web Programming board

 

[scooby]

Also note that: According to the SQL standard, "NULL" is the default.

BTW, avoid NULLs if at all possible. It makes logic confusing because certain axioms don't work.

For example, p OR NOT p is always TRUE, right? Not if you allow NULLs: NOT NULL is NULL, so NULL OR NOT NULL is NULL.

In theory, you can always get rid of NULLs even if you don't have a valid DEFAULT value. Remember that a field with a NULL is a repeating field that can repeat 0 or 1 times.

So it's not really in "first normal form." (1NF dictates that, among other things, there should be no repeating fields.)

What you can do is break that field off into a little table of its own, just copying the primary key over.

Now, this is all in theory. In practice this can make queries difficult to express or hurt performance because of limitations with the DBMS and the SQL standard. However, be forewarned that it is poor logical design to use NULLs because you might make logical assumptions that don't hold up, and three-valued logic (logic with TRUE, FALSE, NULL) is so obscure it's not even taught in school. You have to work it all out by hand, which is tedious and error-prone.

So if you do use NULLs and get wierd behavior, that's going to be the first thing you should check for bugs.