Foreign Keys

[00:00:00]
>> Brian Holt: This, is this fair to say? Yeah, this is my least favorite part about SQLITE versus other databases. This is the thing that drives me absolutely bananas about it. And there's like not unless someone has some magic wand that they know what to tell me. There's no better way to do this.

[00:00:18]
So let's first talk about foreign keys. Foreign Keys are basically what you and I have been looking at already, where we have the album ID in the track, and that refers to the track, right? It's a foreign key. So let's look at schema. Let's let's do album that might be a little bit more clear.

[00:00:40]
So you had this in the album table, you have an artistid that refers to the artist. It is a foreign key, because it's a key that refers to another table. That's the foreign part of it, right? Very useful and you can see here there's actually a constraint on this, that it's a foreign key and that it references a different table.

[00:01:01]
Which is cool. The constraint is here ON DELETE NO ACTION which is basically saying if this foreign key, let's see, if the artist gets deleted without deleting their albums out of the album table, it's just gonna fail the query, right? That's what the no action is. You could have this be set null, you could have this be set default.

[00:01:26]
You could have it be a bunch of other things. But no action just means, before you let the other table delete that particular thing that this is referencing, make sure that this is deleted first, right? So, if I want to go delete Led Zeppelin from the artist table, I have to go delete all their albums first, and then I can delete Led Zeppelin, which I think is good.

[00:01:48]
It promotes data hygiene, is what you would call that, right? That's what a foreign key is. It's a very common thing to do in databases, not that this is way beyond the scope of this class but there's some people that would say like there's performance implications. And maybe at scale you don't want to do foreign keys and so companies like extremely large companies like Facebook, sometimes don't use foreign keys even though when they're you it's a foreign key, right?

[00:02:18]
I'm gonna say that I don't believe that I think just use foreign keys, and then wait until you're as big as Facebook to solve those problems. So that's kind of up to you but [COUGH] If you don't have an opinion yet, please use foreign keys. Let's go with that and then Brennan's problems with foreign keys and then you can tell me that I'm wrong when you're a billionaire.

[00:02:39]
Just give me a couple million as a compensation for my opinion. Okay, so foreign keys, I'm a fan. I think they're a good idea. I use them in all of in professional and personal contexts, as you can see here this is a foreign key that references the artist.

[00:02:57]
I think it's useful that you have to go delete things out of artists before you can delete them in album. Anything that helps keep your data organized, I think is very positive because not all of us will have Facebook scaling problems, but all of us will have data hygiene problems, right?

[00:03:13]
That's just a universal thing when you have tables. What's my problem, right? Why am I complaining about SQLITE? Because SQLITE by default does not respect foreign keys. So despite the fact that this knows, and I have set this to not delete something from my album or yeah, from my album table, if there's an artist that references it all the way around.

[00:03:38]
If there's an artist that references the album table, it will. It doesn't care, which drives me bananas, because why do we have this if you're not gonna respect it, right? And you can obviously see that it accepted it. Let's even go prove my point.
>> Brian Holt: Artist where name equals, I don't know.

[00:04:04]
Is it AC/DC, all right, so it's one so and then if I say SELECT star FROM ALBUM WHERE ARTIST. I don't know why I'm doing this all caps, probably because I'm angry right now, where artistid equal one, right? What did I do here? I had to wear twice.

[00:04:42]
>> Brian Holt: Select star from album where artistid equal 1. Okay, so I should not be able to delete AC/DC right now, right?
>> Brian Holt: Artist where artistid = 1.
>> Brian Holt: So those are still there,
>> Brian Holt: And that is gone. Are you upset? I'm upset. I am viscerally upset right now that this database let me do this despite the fact that it knows it should not do that, why?

[00:05:24]
Why does it do this? Well, SQLITE is aggressively backwards compatible. And so back, way back when it didn't, I don't even know if it had foreign keys. And if it did, it didn't really respect them, right? And so if you wrote an app way back then you had foreign keys, and it wasn't respected.

[00:05:46]
They didn't wanna break those apps by failing queries that were not failing previously. And so, they made this the default practice that now you have to say PRAGMA foreign-keys=on. Okay, now that I've typed this now, it will respect foreign keys. But, what happens if I just disconnect from my database and reconnect?

[00:06:22]
Well, now it's not respecting fragments of my foreign keys anymore, right? It's on a per connection basis. You can't even set it for, like, the whole file, right? I can't even just, like, set it once and say, like, okay. Anyone reading from this file has to respect foreign keys.

[00:06:37]
It's per, yeah, poor connection, upset. I'm still upset. So let's just go ahead and do another delete from artistid2. It should now tell me, yeah, I got a foreign key constraint that's gonna fail this for you.
>> Brian Holt: Pretty silly, right? Pretty dumb, let me fix my database now.

[00:07:04]
Now, since I deleted AC/DC, git status. I just need to get checkout data.db. If you did what I did, then you can just go re-download it again. That also works. All right, so I'm back in here. I'm gonna say PRAGMA foreign_keys=on. And let's do
>> Brian Holt: This, cuz this should fail a foreign key constraint.

[00:07:32]
It does why does it fail that? Is because I'm trying to tell it what album ID it is, and because 9999 and media type, all these aren't real. It's like you can't reference a foreign key that doesn't exist. If again, if I tried to do this without the PRAGMA foreign_key=on it would just be like cool.

[00:07:53]
Here you go. Here's a, what is this a track that references no one, right? And same thing here I can't delete from genre. WHERE GenreId = 24 because this is a foreign key as well, right? So what I'm telling you here is anytime that you're inserting or deleting from your database, your first query that you have to send to SQLITE is this one.

[00:08:24]
Just do it, get into a habit, make it automatic, just always send it. You're just always gonna do it every time you open that connection.
>> Brian Holt: So there is something that you can do by a, there's like a computer-level. Automatically do this on my computer. Anytime I open any SQLITE file, put the PRAGMA = foreign key on.

[00:08:52]
I'm gonna argue that's actually worse because as soon as your someone else gets cloned into their computer, they're not gonna have that and they're gonna have a different problem. Or you spin up a new server that doesn't have that config file, right? Just don't try and solve it just suffer, is what I'm trying to tell you just suffer and complain that's what I do.

[00:09:15]
Cool PRAGMAs are basically like policies that you can tell SQLITE to respect we're saying for this it's always on a per connection or per open session maybe per session is a better way of saying it. You have to do it for every connection to SQLITE Cloud. There's a bunch of PRAGMAs, but this is basically the only one that I use on a consistent basis, which is the foreign keys one.

[00:09:42]
>> Brian Holt: Yeah, you can set it on the connection. I think I read this somewhere. I didn't actually put this into the course, but, yeah, apparently that's a thing. So we did constraints here. We're not going to talk anymore about constraints. Again, I talked more about it in the Postgres one, constraints.

[00:10:06]
There's some other stuff in there. They can basically make enumerated types where basically it has to be one of these things, right? We're not gonna talk too much about it. That's not that interesting for the sake of this class. We did a no action. We talked about that.

[00:10:21]
There's also one called restrict, which is essentially the same thing and actually don't fundamentally understand the difference between to do no action and restrict. I guess there's some minute differences there, but I see everyone do no action, so that's what I do. There's a pretty cool one called On DELETE CASCADE.

[00:10:37]
If I delete something from the artist table, then go ahead and delete all their albums automatically. That actually, that behavior would make sense to me. So you could do that, just being aware that you can accidentally cascade deletes into things, because that's gonna then delete all of your tracks as well, right?

[00:10:53]
If you did like multiple on cascade deletes, and then you can do ON DELETE SET null. So if this gets deleted then and there's a foreign key reference, right, then you can do a null and set a default as well.