### Introduction to SQL

Course
Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

### Video Transcription

00:00
I welcome back the module Three sequel programming were in Listen. Five tables were moving into sub Listen, 5.5 constraints. Now we have worked some with previous types of constraints. For example, if we take a look at the dogs table will see that the i d and the person i d. Columns are not know, which means that when a record is added,
00:19
they must have a value. So that isn't not no constraint.
00:23
The database will refuse any records where
00:27
a programmer makes us a mission without any values in those fields.
00:31
Another constraint is the i d calm. We made it a primary key. So not on Lee. Must it have a value? But it must be a unique value. So, for example, if a record has an idea of five and somewhere down the road, someone tries to add another record with an idea five. The database again will not accept it, and we'll throw in there.
00:52
So what other types of constraints are there? Well, there's a few others. We have the foreign key constraint that we talked about in the previous lesson as well.
01:00
Um, and this lesson we're gonna talk about the unique constraint.
01:03
A unique constraint allows you to take a single or multiple columns and require that their values be unique, either by itself or in combination. If you've used more than one column and the really useful on pivot tables, for example,
01:17
so let's say we have a new future that we need to add to this application that has to deal with a person owning dogs, and that is that
01:25
a person can own many dogs and a doll can log can belong to many people. So that would be a many too many relationship. And the way we enforce that is a pivot table.
01:34
Now. It doesn't make sense for us tohave multiple records of the person owning the same dog in that table so we can enforce that constraint so that that will not happen. So let's go ahead and start. Let's go ahead and start doing that.
01:51
So if I create a new table,
01:55
I'm just gonna call it person underscored Dog.
02:00
02:02
First column I'm going to add is an I. D column because that's a very common
02:07
calm to add. We're gonna go ahead and make that like it's the primary key. Not know. Okay,
02:14
now I'm gonna add the columns that we need
02:16
now. You could get away with just the person I D and the dog idea. Not an idea calm, but
02:23
it's a good idea just to have a primary key.
02:25
So big ant unsigned,
02:29
Not know. Okay,
02:30
create a new column.
02:32
Let's see. That's one of the dog idea
02:37
not known.
02:38
Make sure you get the data type right. Otherwise, you'll have problems when you try to
02:44
create, ah, foreign key constraints.
02:47
So there are the three columns that we need. I'm gonna go ahead and click, Save,
02:53
persist.
02:54
You get an air there and you've come back to D Beaver. After some time, it's possible the connection dropped, and what you may need to do is just right. Click on the connection and select invalidate, reconnect, and that will establish the connection again.
03:09
Case you're having issues there, So let's go ahead and add a constraint that
03:15
the idea is the primary key. Let's go ahead and do that one
03:21
great new constraint.
03:23
Primary key. I d. Okay,
03:25
go ahead and save that. Persist.
03:30
Let's go back to the columns and make that auto increments so we don't have to worry about it. Take it. It takes care of itself.
03:37
All right, Now let's add our unique constraint.
03:39
So we're gonna create a new constraint.
03:43
This would have worked without adding a primary key is just it's its own thing that we can add. So I'm gonna say, unique
03:47
key. We're gonna say Person, dog, dog I d. So that means the same person can show up in this table. The same dog can show up in this table, but on Lee won
03:55
The combination of the person and a dog must be unique.
04:00
So we're gonna save that
04:01
persists.
04:04
Okay,
04:06
so now the idea here would be the dog's, and persons goes through the person dog table
04:13
to find out who owns which dog. So, for example, if I owned a dog in this table,
04:18
let's look at the columns.
04:20
And my idea was one. I'd be I'd have person idea of one and a filed by own dog idea of two than in the other column would be dog idea of two
04:30
and then have owned another dog. That's a dog. Three. There'd be another record
04:33
where the person I D is one and the dog ideas three.
04:38
Now let's go ahead and insert
04:41
a record and see if our constraint works correctly.
04:45
Copy that. We don't have to insert the I D because that's auto incriminating by itself.
04:51
So a certain to example person, dog person, i d dog idea. So there's no four constraints, so it's not going to check,
05:00
um, that that person actually exists. And that dog actually exists would be a very good idea to put those constraints in if you were doing this in a database that you plan to use in production. And that would ensure that you don't have
05:15
problems with having
05:18
records in pivot tables that don't belong to anything. Because again, that's another situation that's confusing. And the more of those confusing situations that you have in your application, you just the worst that it gets. And the more more people just start to dislike the application. So it's always a good idea and force those constraints where they make sense.
05:38
But in this case, we're just gonna go ahead and start this record with a person idea of one dog idea to they don't exist because I haven't put anything in those tables. This is just for an example.
05:47
So let me go ahead and run this.
05:51
Now let's change one value. What's changed this to a 13
05:57
running again? It worked again. Now, if we run it again, the database is going to throw in a row and stop us.
06:03
And sure enough,
06:04
we got a duplicate entry air, which is what we expected because we added that unique constraint. So when I tried to add it to the second time, the database rejected it and said, Hey,
06:15
you can't do this is against the rules And that's exactly what we want it to happen.
06:19
And that is constraints. I hope you were able to get your constraint working. I hope you enjoy this lesson and I'll see you in the next one. Thank you.

### Introduction to SQL

This introductory SQL training teaches SQL core concepts that can be applied in professional environments. Once students complete this course, they will be able to query and interact with an SQL database, and know how to design database schemas.

Kitt Parker
Instructor