Application logic belongs in the application. Don't rely on a database to do it for you, or you're going to wind up with a giant ball of mud driven by side-effects.
Putting in sane integrity checks to a database should be a priority. You can fix broken applications, but you can't fix broken data (especially if you collected it from users, sensors).
It doesn't put a NULL there, it puts zero for a number field, or a blank (0 length) string for a string field. Date fields also get 0 (00-00-0000).
Edit: I get a downmod for this? The group think here is amazing. I suppose if I bash MySQL I'll get some upmods even if the bashing is incorrect?
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.
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.
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.)
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.
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.
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).
The easier you'll make it on him not to cause horrible corruptions in the data by forgetting to update that additional table that depends on whatever he is updating - the better life will be.
Triggers, foreign keys and constraints are all excellent ways to do just that.
I've argued for years that this is what the "sharding" hype (and to some degree the current NoSQL hype) was mostly about.
I'm not suggesting a custom DBMS can't possibly be the right answer, but it seems silly to layer it on top of something as heavyweight as a relational database.
I implied, though foolishly didn't state outright, that I believe a custom DBMS is only very rarely the best answer.
My point is not to throw the baby out with the bathwater. If you need massive scale then of course you need fresh approaches, but you are sacrificing a lot by abandoning a centralized DB.
If your data is moderately sized, then you will be trading a lot of data integrity, queryability and flexibility by giving up an SQL databases. Certainly consumer web services of the type that are en vogue in silicon valley need new approaches. However the majority of applications out there are probably still best served primarily by a relational DB, especially when you consider the value of a byte of corporate data vs the value of a byte of facebook data.