1
00:00:00,150 --> 00:00:06,420
Next up, we're going to talk about one of the most important modes or settings, which is strict trans

2
00:00:06,420 --> 00:00:07,160
tables.

3
00:00:07,170 --> 00:00:12,480
Trans stands for transactional, and that should be enabled by default.

4
00:00:12,480 --> 00:00:23,550
If I do a select at at let's just do global globe all dot scale mode, we see strict trans tables.

5
00:00:23,550 --> 00:00:24,900
So what does that mean?

6
00:00:25,140 --> 00:00:28,380
Well, if you go to the docs, you read about it strict trans tables.

7
00:00:28,380 --> 00:00:34,290
It tells you to go to this other page for strict SQL mode, and it tells us that strict mode controls

8
00:00:34,290 --> 00:00:40,290
how my SQL handles invalid or missing values in a data change statement like insert or update.

9
00:00:40,290 --> 00:00:46,140
What happens when you try and insert a string into a number column or a number into a string column

10
00:00:46,140 --> 00:00:49,560
or update a string column to be a number.

11
00:00:49,560 --> 00:00:52,260
I keep saying number in string, but we're not limited to that.

12
00:00:52,350 --> 00:00:54,150
What happens when you do something invalid?

13
00:00:54,150 --> 00:00:57,870
You try and insert an invalid data or a value that's out of range.

14
00:00:57,870 --> 00:01:02,370
Well, let's see right now, when this is enabled, let me just verify.

15
00:01:02,370 --> 00:01:05,700
It's also enabled for my session SQL mode.

16
00:01:06,420 --> 00:01:08,280
Here it is strict trans tables.

17
00:01:08,640 --> 00:01:12,510
If I try and do something like we have this, what is it?

18
00:01:12,870 --> 00:01:14,640
Reviews table from earlier.

19
00:01:15,090 --> 00:01:21,570
If I try and insert into this reviews table where rating is supposed to be a decimal series ideas and

20
00:01:21,570 --> 00:01:24,600
end reviewer ideas, and both of those are foreign keys.

21
00:01:24,600 --> 00:01:29,430
So let's just worry about rating and they actually can be null.

22
00:01:29,430 --> 00:01:30,660
So all of them can be null.

23
00:01:30,660 --> 00:01:36,330
So I'm not even going to bother with series ID and reviewer ID, I'm just going to do an insert into

24
00:01:36,630 --> 00:01:42,090
reviews and I'll only enter the rating, which is supposed to be a decimal, but I'm going to insert

25
00:01:42,090 --> 00:01:44,220
a string like, Hi.

26
00:01:45,090 --> 00:01:46,620
So what happens?

27
00:01:47,130 --> 00:01:48,240
It doesn't get inserted.

28
00:01:48,240 --> 00:01:49,680
We get a full blown error.

29
00:01:49,680 --> 00:01:52,650
It says incorrect decimal value high for this column rating.

30
00:01:53,250 --> 00:01:54,870
We got a full error, right?

31
00:01:54,870 --> 00:01:59,130
If I do a select star from reviews, it won't have been inserted.

32
00:01:59,130 --> 00:02:01,320
We can see it's not down here at the end.

33
00:02:01,710 --> 00:02:07,920
But if I actually turn that mode off and just for the sake of time, I'm actually going to just set

34
00:02:07,920 --> 00:02:16,110
the session SQL mode to be an empty string, which disables all of those settings, including strict

35
00:02:16,110 --> 00:02:17,400
transactional tables.

36
00:02:17,490 --> 00:02:18,180
All right.

37
00:02:18,180 --> 00:02:23,730
So now if I go back and select my session SQL mode, it's empty.

38
00:02:23,970 --> 00:02:30,330
And if I try and reinsert my incorrect high value into reviews.

39
00:02:30,360 --> 00:02:30,840
Huh.

40
00:02:31,110 --> 00:02:33,690
We didn't get an error, but we got a warning.

41
00:02:33,690 --> 00:02:34,350
What's the warning?

42
00:02:34,350 --> 00:02:37,050
Show warnings.

43
00:02:37,530 --> 00:02:39,900
It says incorrect decimal value, blah, blah, blah.

44
00:02:40,200 --> 00:02:42,930
It's still the same message, but it was a warning this time.

45
00:02:42,930 --> 00:02:45,750
So what does that mean for our actual insert?

46
00:02:45,750 --> 00:02:50,370
If I do a select star from reviews Reviews.

47
00:02:51,220 --> 00:02:52,470
Oh, look at that.

48
00:02:52,480 --> 00:02:55,620
We got a 0.0 inserted as the decimal.

49
00:02:55,630 --> 00:02:58,200
So this behavior is very different.

50
00:02:58,210 --> 00:03:02,470
It gives us a message, but this time it's a warning instead of an error, it doesn't abort the whole

51
00:03:02,470 --> 00:03:03,280
transaction.

52
00:03:03,280 --> 00:03:09,310
The insert goes ahead, but our value is turned into a decimal in some way or coerce to a decimal,

53
00:03:09,310 --> 00:03:15,100
or we somehow get zero instead of high, which doesn't really make sense, at least in this situation,

54
00:03:15,100 --> 00:03:20,170
which is why strict transactional tables or strict mode is enabled by default.

55
00:03:20,170 --> 00:03:26,050
In my SQL eight onwards, you want to probably keep that enabled, but it's good to know what it actually

56
00:03:26,050 --> 00:03:26,350
does.

57
00:03:26,350 --> 00:03:31,300
And there's more to it than just inserting and updating a string into a decimal.

58
00:03:31,330 --> 00:03:33,460
There's quite a few other things.

59
00:03:33,970 --> 00:03:36,850
It does not affect foreign key checks.

60
00:03:36,850 --> 00:03:39,940
That is a separate mode that you can change.

61
00:03:40,570 --> 00:03:42,460
There's more you can read about here.

62
00:03:42,460 --> 00:03:49,030
I'm not going to spend too much time on it other than saying that it is a very important mode and you

63
00:03:49,030 --> 00:03:55,510
probably want to keep it on because this is very confusing that I could insert a string into a decimal

64
00:03:55,510 --> 00:03:58,330
column and then it just ends up at zero and it doesn't throw an error.

65
00:03:58,330 --> 00:04:00,310
It doesn't stop anything from happening.

66
00:04:00,370 --> 00:04:08,680
Now, if I reset the session SQL mode to be strict trends tables, I believe it is.

67
00:04:08,680 --> 00:04:10,450
Just make sure I get that correct.

68
00:04:11,110 --> 00:04:12,130
Strict trans tables.

69
00:04:12,130 --> 00:04:12,640
Yep.

70
00:04:13,810 --> 00:04:14,560
I try and do that.

71
00:04:14,560 --> 00:04:18,399
Same insert again, we get an error and it's not inserted.

72
00:04:18,430 --> 00:04:20,320
The whole transaction is aborted.

73
00:04:20,320 --> 00:04:22,600
So that is strict trans tables.

74
00:04:22,930 --> 00:04:25,450
Next we're going to look at another SQL mode.