As you know from the last video, when we're dealing with row IDs, we're fully in quirks territory. This is like IE 5 quirks mode that we're finally back to. One of the great strengths of SQLite is backwards compatibility. One of the problems is backwards compatibility. That's where we get a lot of these quirks.

So in this video, I'm gonna show you how we can change that row ID selection algorithm such that it never reuses previously deleted IDs and it won't wrap around after it reaches the final ID. Let me show you how and then we'll talk about whether this is good or bad. It's both. I'm gonna create a table called exam I'm not gonna use example. We're just gonna call it auto.

It's a lot shorter to type. And in this case, we're gonna have id integer primary key. So that integer primary key, that tells us that that is an alias for row ID, remember. And we're just gonna have some text as text and that is it. The thing that we're going to add here that is new in this lesson is instead of just leaving it as primary key, we're gonna add a keyword of auto increment, which tells SQLite, hey, you gotta change your row ID selection algorithm just a little bit.

So we're gonna do that and then we'll insert into auto, let's just insert into text values hello. And then we'll have, just for giggles, we'll have a second one. Select * from auto 12, auto incrementing. Now let's do the exact same thing that we did last time which is insert into auto. We'll do ID and text.

I didn't have to type that actually. And we're gonna put the biggest ID possible in there. And the text will just make something up. So let's clear this out for the big show. Select star from auto.

So we have hello, goodbye, and last. Now, insert into auto text values. Hopefully, this works. It's not gonna work. There you go.

Database or disc is full. So this tells us that that that table is full. You can't put anything else in there. It's not gonna go back and look for an ID that hasn't been used. It's not gonna go back and fill in any gaps from deleted IDs.

So now you're host. If you inserted if you inserted the largest ID for some reason, you're now you're you're pretty much host, because the auto increment keyword changes the row ID selection algorithm. So what happens if you do get in this situation? I don't know how you would on I don't know how you would get in this situation in a real application, but stranger things have happened, I suppose. Let's take a look at a little secret table here.

Select * from, I think it's SQLite sequence. There you go. So if you select star from SQLite sequence, it will show you the name of the table and the last, the last auto incrementing ID. So this is entering into the danger zone. If you ever do something like this, I would just be so careful.

Select * from auto. Alright. Let's, delete from auto where id is greater than 2. Select * from auto. Great.

Inserts. Insert into auto. Man, still doesn't work. Right? So we tried to insert a new one, but it didn't work because that auto increment keyword tells SQLite, hey, you set the high watermark and then you never go below it.

If you end up in this situation, select star from SQLite sequence. This is expert danger mode. Be super careful. Update SQLite sequence. You bet we're doing it.

Update SQL lite sequence. Set sequence equal to the last one that we saw. And this is, I mean, this is just I feel like this is really risky. I guess right now the table is, in a stasis because nobody can insert any data because the table is full because we've run out of keys. And so we're kinda fine, but still regardless, this makes me nervous.

Update SQLiteSequence. Set sequence equal to 2 where name equals auto, and then we'll select it back. Then we can insert. Hopefully, this works and let's make some room. Select star from auto.

We fixed it. So we created this kind of fake problem and then fixed it by doing something that you probably shouldn't ever do. But now you know that the SQLite sequence table exists should you ever need to use it. Don't know when you would need to use it, but that metadata does exist. Why would you not want to use the auto increment keyword?

It's overhead. It's slower. It has to go and look up. What was the last one? Okay.

Let me pick one that's beyond that. The row ID algorithm without auto increment is less CPU intensive. It makes inserts a little bit faster. The row ID algorithm with auto increment feels so much safer to me. We'll do some performance testing here in a little bit.

I like the auto increment keyword. It helps me sleep at night. If your application is built such that you don't mind if primary keys get reused, then you don't need the auto increment keyword. I think 99 out of a 100 or maybe even 1 out of every million, like, you're just not going to end up reusing keys that often. I just don't think that that's a scenario that is terribly possible, but it is possible if you're deleting a bunch of rows.

You're gonna leave gaps in those keys and the normal row ID algorithm will potentially fill those in. I just don't know if that's a big deal. I personally like to feel safe with the auto increment keyword. If you feel that way too, that's great. We'll do some performance testing later to see if it actually makes a difference.

I honestly, I think it's gonna be so minimal that it doesn't make a difference. And in those cases, I always choose safety.