[ars]

What?

Have you ever programmed SQL? Because what you wrote doesn't make sense.

No data is inserted wrongly - you are simply leaving out a field. There is no mess. Just an unused field.

Are you thinking it's like csv where if you leave out a column all the others are shifted? It's not like that.

The standard says if you leave out a column that does not have a default the SQL should return an error. Instead MySQL puts in a default (but only if you tell it too in the configuration). Putting a NULL in a non-NULL field would be much worse.

[dagheti]

Oh I agree they are both very bad. Putting NULL in a non-NULL field is totally wrong.

However I really do think that inserting an unexpected default value is worse than inserting NULL into a NON-NULL field. The NULLs will cause problems, but they are problems you can see and resolve.

The default values are silent errors that will corrupt your data and be very difficult to recover from in the future. You can only guess which data was wrongly inserted.

[ars]

I still don't see how it's possible to insert wrong data, or corrupt it??

There IS no data. How do you corrupt something that doesn't exist?

And NULL doesn't help either. NULL is valid data, NULL is not a replacement for programming errors (which is what this is).

This argument is pointless. People love to bash on MySQL, they look for the silliest things. The more popular something is the more people bash on it.

I understand that, but at least bash on real problems? Like the transaction DDL - that's a real problem. This? This is nonsense. (It's actually a very useful - and optional - feature BTW.)

[Lewisham]

I'm not sure you understand the use of NULL.

NULL is not "default value" or "I don't care", NULL signifies "this might have a value, I just don't know what it is".

There is a very significant difference between a payroll record which states your pay is "0" vs. NULL. If the database is putting in default values, you have no way of knowing whether the employee really did have a salary, but it was incorrectly inserted as NULL, or whether the employee is unpaid.

[ars]

I understand NULL very well, and that's not the only use for a NULL.

NULL also means "value does not exist", not just "value is unknown". For example if a student is not in a class, put NULL in the class id.

NULL is perfectly valid data, and is not a replacement for a programming bug.

And with mysql if your salary field is defined as accepting NULL then you will get a NULL in there.

And to use your example if the field accepts NULL, you would also have no way of knowing if the salary was not negotiated vs a programming bug.

If you want to argue the insert should fail, then fine, no problem. (And MySQL can do that.)

But arguing that putting in NULL is better (in a field that does not accept NULL), is simply wrong. I'll say it again: NULL is not a replacement for a programming bug - NULL is valid data, and should not be used to find programming errors.

[anthonyb]

> For example if a student is not in a class, put NULL in the class id.

I think you mean "don't insert a row in the student_class table, which is a many-to-many join between student and class".

As a general rule of thumb, if your data schema requires NULLs for things like that, then your schema is wrong, for most of the reasons that people are trying to point out. NULLs are the absence of data, and should really only be used for exceptional circumstances - hence the reason that silently inserting NULLs into NOT NULL fields is a Bad Thing(tm).

[AlisdairO]

Pretty sure he's arguing that it should be an error.