WEBVTT 1 00:00:00.350 --> 00:00:01.760 Hi guys, and welcome back. 2 00:00:01.760 --> 00:00:03.080 In this video we're going to learn 3 00:00:03.080 --> 00:00:05.553 about autoincrementing IDs. 4 00:00:06.610 --> 00:00:09.200 Why do we want to use autoincrementing IDs 5 00:00:09.200 --> 00:00:10.320 and what are they? 6 00:00:10.320 --> 00:00:12.090 Well, we often want a way to refer to 7 00:00:12.090 --> 00:00:13.800 a specific row in a table. 8 00:00:13.800 --> 00:00:15.850 We've seen this with the ID columns 9 00:00:15.850 --> 00:00:18.550 in the tables that we saw earlier on. 10 00:00:18.550 --> 00:00:21.180 An autoincrementing column means that every time 11 00:00:21.180 --> 00:00:23.112 we create a new row in the table, 12 00:00:23.112 --> 00:00:25.510 a new value is created for us 13 00:00:25.510 --> 00:00:27.870 that we don't have to specify ourselves. 14 00:00:27.870 --> 00:00:29.170 So we don't have to think about it. 15 00:00:29.170 --> 00:00:30.760 This is useful because, for example, 16 00:00:30.760 --> 00:00:32.190 if you create a new movie, 17 00:00:32.190 --> 00:00:33.890 you maybe don't wanna check how many 18 00:00:33.890 --> 00:00:35.530 other movies you've got in the table, 19 00:00:35.530 --> 00:00:38.070 so that you can come up with a new unique ID. 20 00:00:38.070 --> 00:00:41.270 So the autoincrementing IDs can be pretty useful for that. 21 00:00:41.270 --> 00:00:42.450 So how do we use them? 22 00:00:42.450 --> 00:00:44.560 Well, when we create a column that is an 23 00:00:44.560 --> 00:00:47.360 INTEGER PRIMARY KEY in SQLite, 24 00:00:47.360 --> 00:00:50.500 that will automatically be autoincrementing. 25 00:00:50.500 --> 00:00:52.200 In PostgreSQL, we're gonna have to specify 26 00:00:52.200 --> 00:00:54.450 this behaviour ourselves, but it's very easy, 27 00:00:54.450 --> 00:00:57.510 just a different type of data that we'll use there. 28 00:00:57.510 --> 00:01:00.369 So, why are primary keys in SQLite 29 00:01:00.369 --> 00:01:02.990 autoincrementing by default? 30 00:01:02.990 --> 00:01:04.870 Well, it's because of a couple of things 31 00:01:04.870 --> 00:01:06.653 that happen behind the scenes. 32 00:01:07.510 --> 00:01:12.053 All SQLite tables have a hidden column called rowid, 33 00:01:13.560 --> 00:01:16.273 and that is an autoincrementing column. 34 00:01:17.300 --> 00:01:20.270 So when we create an INTEGER PRIMARY KEY column, 35 00:01:20.270 --> 00:01:24.060 that new column becomes an alias for rowid. 36 00:01:24.060 --> 00:01:26.480 So it takes the same value essentially. 37 00:01:26.480 --> 00:01:28.620 Therefore, there's no performance loss 38 00:01:28.620 --> 00:01:31.210 by creating an INTEGER PRIMARY KEY column, 39 00:01:31.210 --> 00:01:32.830 because it just uses the same value 40 00:01:32.830 --> 00:01:35.420 that was already generated anyway. 41 00:01:35.420 --> 00:01:38.910 Let's learn more about this rowid column. 42 00:01:38.910 --> 00:01:39.790 Something interesting to note 43 00:01:39.790 --> 00:01:44.440 is that SQLite may reuse rowid values. 44 00:01:44.440 --> 00:01:45.720 So, like we saw earlier, 45 00:01:45.720 --> 00:01:48.230 reusing primary key values can be dangerous. 46 00:01:48.230 --> 00:01:50.770 For example, if you have users and accounts, 47 00:01:50.770 --> 00:01:53.790 and you delete a user but you don't delete the account, 48 00:01:53.790 --> 00:01:54.920 then you create a new user, 49 00:01:54.920 --> 00:01:56.800 and it takes the same value as the old one, 50 00:01:56.800 --> 00:01:59.520 the account now becomes associated with the new user. 51 00:01:59.520 --> 00:02:02.270 We saw this earlier on in another presentation. 52 00:02:02.270 --> 00:02:04.140 And so that can be dangerous, right? 53 00:02:04.140 --> 00:02:06.770 In PostgreSQL, autoincrementing values, 54 00:02:06.770 --> 00:02:09.780 as we will learn later on, only ever go up. 55 00:02:09.780 --> 00:02:13.230 So it will never reuse old values, 56 00:02:13.230 --> 00:02:18.060 but SQLite might reuse old deleted values, 57 00:02:18.060 --> 00:02:19.510 although it normally doesn't. 58 00:02:19.510 --> 00:02:23.330 So it just depends on how large your IDs get 59 00:02:23.330 --> 00:02:25.380 that it might end up reusing old ones. 60 00:02:25.380 --> 00:02:27.980 It normally doesn't though, so it's not a big concern, 61 00:02:27.980 --> 00:02:30.140 but it's just something to be aware of 62 00:02:30.140 --> 00:02:32.480 because in SQLite we have 63 00:02:32.480 --> 00:02:36.070 the AUTOINCREMENT keyword as well that you may encounter 64 00:02:36.070 --> 00:02:38.730 when you are learning more about SQLite. 65 00:02:38.730 --> 00:02:40.840 And you can define a column as 66 00:02:40.840 --> 00:02:44.230 INTEGER PRIMARY KEY AUTOINCREMENT in SQLite. 67 00:02:44.230 --> 00:02:46.440 You can do that, and that means that SQLite 68 00:02:46.440 --> 00:02:48.087 will only ever increase the rowid 69 00:02:48.087 --> 00:02:50.610 and will never reuse old numbers, 70 00:02:50.610 --> 00:02:54.690 but this requires much more computing power 71 00:02:54.690 --> 00:02:57.670 due to the algorithm and how it's implemented in SQLite. 72 00:02:57.670 --> 00:03:02.670 So if you are concerned about using rowid values, 73 00:03:02.810 --> 00:03:06.520 and primary key values, because they are getting very large, 74 00:03:06.520 --> 00:03:10.560 and we're talking thousands of millions in terms of numbers, 75 00:03:10.560 --> 00:03:12.040 then do look into these. 76 00:03:12.040 --> 00:03:14.800 Read the reference that we've got in the resources section 77 00:03:14.800 --> 00:03:16.840 of this lecture for more information, 78 00:03:16.840 --> 00:03:18.780 but you normally don't have to worry about that. 79 00:03:18.780 --> 00:03:20.770 The main purpose for me showing you the 80 00:03:20.770 --> 00:03:23.930 AUTOINCREMENT keyword in this lecture is precisely to 81 00:03:23.930 --> 00:03:26.160 tell you that you don't need it most of the time. 82 00:03:26.160 --> 00:03:26.993 So don't worry about it. 83 00:03:26.993 --> 00:03:28.530 You don't need the AUTOINCREMENT keyword. 84 00:03:28.530 --> 00:03:30.060 Just using the primary key will 85 00:03:30.060 --> 00:03:33.610 automatically give you an autoincrementing value, 86 00:03:33.610 --> 00:03:37.280 and that is an alias for the rowid column. 87 00:03:37.280 --> 00:03:38.640 All right, thank you guys for watching. 88 00:03:38.640 --> 00:03:40.403 I'll see you in the next video.