Next up, we're going to talk about one of the most important modes or settings, which is strict trans
tables.
Trans stands for transactional, and that should be enabled by default.
If I do a select at at let's just do global globe all dot scale mode, we see strict trans tables.
So what does that mean?
Well, if you go to the docs, you read about it strict trans tables.
It tells you to go to this other page for strict SQL mode, and it tells us that strict mode controls
how my SQL handles invalid or missing values in a data change statement like insert or update.
What happens when you try and insert a string into a number column or a number into a string column
or update a string column to be a number.
I keep saying number in string, but we're not limited to that.
What happens when you do something invalid?
You try and insert an invalid data or a value that's out of range.
Well, let's see right now, when this is enabled, let me just verify.
It's also enabled for my session SQL mode.
Here it is strict trans tables.
If I try and do something like we have this, what is it?
Reviews table from earlier.
If I try and insert into this reviews table where rating is supposed to be a decimal series ideas and
end reviewer ideas, and both of those are foreign keys.
So let's just worry about rating and they actually can be null.
So all of them can be null.
So I'm not even going to bother with series ID and reviewer ID, I'm just going to do an insert into
reviews and I'll only enter the rating, which is supposed to be a decimal, but I'm going to insert
a string like, Hi.
So what happens?
It doesn't get inserted.
We get a full blown error.
It says incorrect decimal value high for this column rating.
We got a full error, right?
If I do a select star from reviews, it won't have been inserted.
We can see it's not down here at the end.
But if I actually turn that mode off and just for the sake of time, I'm actually going to just set
the session SQL mode to be an empty string, which disables all of those settings, including strict
transactional tables.
All right.
So now if I go back and select my session SQL mode, it's empty.
And if I try and reinsert my incorrect high value into reviews.
Huh.
We didn't get an error, but we got a warning.
What's the warning?
Show warnings.
It says incorrect decimal value, blah, blah, blah.
It's still the same message, but it was a warning this time.
So what does that mean for our actual insert?
If I do a select star from reviews Reviews.
Oh, look at that.
We got a 0.0 inserted as the decimal.
So this behavior is very different.
It gives us a message, but this time it's a warning instead of an error, it doesn't abort the whole
transaction.
The insert goes ahead, but our value is turned into a decimal in some way or coerce to a decimal,
or we somehow get zero instead of high, which doesn't really make sense, at least in this situation,
which is why strict transactional tables or strict mode is enabled by default.
In my SQL eight onwards, you want to probably keep that enabled, but it's good to know what it actually
does.
And there's more to it than just inserting and updating a string into a decimal.
There's quite a few other things.
It does not affect foreign key checks.
That is a separate mode that you can change.
There's more you can read about here.
I'm not going to spend too much time on it other than saying that it is a very important mode and you
probably want to keep it on because this is very confusing that I could insert a string into a decimal
column and then it just ends up at zero and it doesn't throw an error.
It doesn't stop anything from happening.
Now, if I reset the session SQL mode to be strict trends tables, I believe it is.
Just make sure I get that correct.
Strict trans tables.
Yep.
I try and do that.
Same insert again, we get an error and it's not inserted.
The whole transaction is aborted.
So that is strict trans tables.
Next we're going to look at another SQL mode.