why mysql accept null for non-nullable fields

Question

I am learning laravel 5.1 through laracast fundamentals series and in episode 7 that talk about migrations, i built a table with migration like this:

public function up()
{
    Schema::create('articles', function (Blueprint $table) {
        $table->increments('id');
        $table->string('title');
        $table->text('body');
        $table->timestamps();
        $table->timestamp('published_at');
    });
}

and as it says in the episode 10, when i add an article to database through a form that don't have a field for published_at timestamp, then this should not work and should through an exception that Not Null constraint failed But incredibly the article added to table. When i look at the table in phpmyadmin structure for the published_at column it didn't checked the null checkbox, so it shouldn't accept null? is this true?
But then i noticed that the migration sets a default value to 0000-00-00 00:00:00 for published_at column. why?! and will this override the NULL condition? Then i tried to set the default value to none and still the null is not checked. and again i tried to add an article without published_at fields But again a new row added to table with published_at value set to 0000-00-00 00:00:00. any explanation?

I am using wampserver with mysql v5.6.17 , php v5.5.12

Marcin Nabiałek · Answer 1 · 2015-12-13T21:56:30.413

3

All database types have default values. When you create record and fill only title, then body will be set to empty string, and timestamps to "empty" date what is exactly 0000-00-00 00:00:00. If you made those fields nullable and set ->default(null) for them you will have null values for them in this case.

You can also read about Default values for types in MySql

edited Dec 13 '15 at 21:56

answered Dec 13 '15 at 21:49

Marcin Nabiałek

109,655
42
258
291

Thanks Marcin, I dont have a textbox for `published_at` in my form so when i submit that wouldn't that be `null` in the query? or will that be an empty string? – osyan Dec 13 '15 at 21:56
And also i **did not** set that field to `nullable` – osyan Dec 13 '15 at 22:03
1

@osyan If you `published_at` is not nullable, when you don't fill it you will get `0000-00-00 00:00:00` because you haven't set any other default value. You could set it using `->default(DB::raw('CURRENT_TIMESTAMP'))` and then when you don't fill it you will get by default current date and time instead of zeros – Marcin Nabiałek Dec 13 '15 at 22:06
i got the point, but i am still Confused about why in the same situation it through an exception in that video. can this be because of Differences between sqlite and mySql ? – osyan Dec 13 '15 at 22:17
@osyan I see but I don't know why is that. I use almost all the time MySql and unfortunately have no more time to test this. – Marcin Nabiałek Dec 13 '15 at 23:09

score 1 · Accepted Answer · answered Dec 14 '15 at 02:21

SELECT @@SQL_MODE;.

If you don't see one of the Strict SQL Modes enabled, then MySQL is using its older behavior, and coercing invalid values into something valid to allow the row to be inserted.

If strict mode is not in effect, MySQL inserts adjusted values for invalid or missing values and produces warnings.

Default configuration files shipped with official MySQL Server 5.6 had STRICT_TRANS_TABLES enabled. WAMP may not have followed that same convention, perhaps for backward-compatibility reasons... but the configuration can be changed.

thanks, as you said i have this for mine `@@SQL_MODE = NO_ENGINE_SUBSTITUTION` and seems that the same things happen here: http://stackoverflow.com/a/26035507/1333160 — osyan, Dec 14 '15 at 18:21

why mysql accept null for non-nullable fields

2 Answers2