Implement Constraints

00:01

Welcome back to Muzzle four. This is less than 4.4 implement constraints. This lesson. We're gonna add the constraints to our migration files. We already have some constraints. The primary keys are a type of constraint. The primer key is again required to be unique.

00:16

We're going to add the foreign key constraints which will help protect referential integrity.

00:21

And we will cascade changes. So when a user is deleted, then their references on their pivot table will also be deleted.

00:29

We're also going to add a unique constraint. We're gonna have this on the pivot table. This is going to prevent teachers and students from enrolling in the same course because the combination of the course and the user I d. Will be required to be unique. So why do we use constraints? Well, it protects referential integrity, it

00:48

clearly indicates, designed to other programmers.

00:51

When you when I see a foreign key constraint on two tables, I clearly see that relationship. I clearly see that there's intention there when it's not there. I kind of

01:02

depending on the naming of the columns might have to guess,

01:04

and depending on the size of the database, you could end up doing way too much guessing.

01:10

And, of course, and including these prevents technical debt.

01:15

Now, with that said, let's go ahead and get started.

01:19

Okay, here we are. I have my migrations folder area open, and that's in my shared database migrations area. These are the migration falls to get created when you create the model with the migration. And I'm looking at my er de diagram to see where I need to add those foreign key constraints.

01:36

I can see that I'm gonna need them on grade, user

01:38

essentially anywhere where I see this little fork connecting to a table. So let's go ahead and start with the grade table.

01:46

Gonna edit that,

01:49

and then I'm gonna paste in

01:52

the foreign key constraint that a copy from the documentation

02:01

You can see that this isn't correct because we aren't on. We don't have a user. What we do have a user. I d.

02:07

So maybe this one is correct. Use your I d

02:10

references. I d on users

02:14

Cascade. Okay. Yes, that is correct. So it just happens that the example I copied from the label documentation also happens to be the exact foreign key constraint I need, But we need another one to the course I d

02:29

So

02:37

So we'll change. User, I d to course I d

02:40

references. I d on course this

02:46

this is in the plural. The table names are plural while on the rd diagram, it's singular.

02:53

So foreign course A D references I d on courses on delete Cascade. Okay, go ahead and save that.

03:01

Now, the one thing here is that great is gonna try to connect to the course table. So that means the course table will have to exist before that happens.

03:10

And the way PHP are Titian migration files work is that they run in the order of the date stamp,

03:19

and that's in ascending order. So if I move this or if I changed the date to before

03:23

the great table that I should be OK,

03:28

so 33

03:30

for 06 Now, this is in my situation. It's very, very likely that you created your files with different date and time steps, so you will need to change the date and time

03:42

to one that places it right before your users table.

03:46

So now I have courses and users,

03:50

so you probably will not be able to copy exactly what I types will be sure to pay attention to those time stamps and name that, respectively, so that it occurs before your users table.

04:05

So we have courses, users,

04:09

grades. So when we get to grades,

04:12

the user table will exist and the course table will exist. Okay, that's going to run that migration just to make sure that works correctly.

04:20

So PHP are Titian migrate fresh.

04:27

We got an exception. Let's see what we have.

04:32

Incompatible. Okay, so we're not using the right data type on one of these, So let's check the grades.

04:41

Big energy. Okay? They're not unsigned. They need to be unsigned

04:48

both of these

04:51

because we can't match an unsigned against a

04:55

regular one because the big increments

04:58

is unsigned.

05:00

It's going and run that again.

05:09

No, you gotta spell it right.

05:19

Okay,

05:24

there we go.

05:26

So, once I added the unsigned command to the migration file, it worked correctly.

05:34

Okay, let's move on to the other tables. Let's go ahead and take care of

05:42

user course. That's gonna be the probably the most complicated one.

05:51

So I'm gonna copy the foreign key from my other one and eminent rename

05:57

to the correct column names,

06:02

so we have a course I d references I d. On courses that's actually correct

06:10

on, uh, let's go ahead and copy and paste again.

06:17

Then we have We're gonna need the user. I d

06:24

represents

06:25

references. I D on users.

06:29

Cascade again

06:32

On next. We need the type I D

06:42

purposes. I d on

06:44

user types.

06:47

Cascade wouldn't save that.

06:50

Now. This one has three other tables that will need to exist before it's created. So let's make sure that that's true. Users Table does exist

06:59

courses Table is true.

07:01

User type is true.

07:04

That looks like it will work. Let's go ahead and run that migration.

07:09

And if it's different for yours because you created them in a different order, you will want to make sure that they are in the correct order.

07:15

Let's go ahead and run this

07:21

and I made a spelling air here, so it's going to fix that

07:27

and rerun it again,

07:31

and we are good to go.

07:33

Let's move on to the next table,

07:36

go ahead and take care of the course table.

07:40

Well, that's going to be this one,

07:42

and I know we're gonna have to change one of the tables, the room.

07:46

So

07:48

copy, pace

07:51

and room I d

07:55

purpose since I d on rooms.

08:03

All right, so we will need the rooms to occur before that.

08:09

So it looks like weaken or I can change this to

08:13

405

08:16

That should work

08:20

on. Let's go ahead and run the migration just to make sure

08:30

we're good to go.

08:31

Let's move on to the room file.

08:37

So it's gonna need another one before that one,

08:41

and it's gonna be the building.

08:45

So

08:46

building I d

08:50

or Mrs I d on buildings

09:03

so well, it's gonna fail. We don't have buildings in the correct place.

09:09

So

09:11

it's like I could change this to six

09:16

on a 33

09:20

404

09:22

That should put everything in the right order. Run it again.

09:28

Okay, Looks like that worked. So we've added all our constraints. Let's go ahead and take a look in d Beaver and see their e. R D. Diagram is looking correct,

09:37

right? Click Refresh.

09:39

I want to go into the most complicated one, which is user courses. Look at the E. R. D diagram

09:46

and there we go. We could see

09:48

that those are constraints have been added incorrectly.

09:52

And the nice thing about constraints is that as a if you are a different program or a new program are coming in,

09:58

you'll be able to look at this and say, OK, user courses is related to all this stuff,

10:03

which, you know is somewhat apparent just going by names. But as databases get more and more complicated, it's very helpful to have these in place.

10:13

So let's go ahead and head over to the summer and talk about what we went over in this lesson.

10:18

Oh, I do have one last thing to implement before we go over to the summary.

10:22

I almost missed this, but we need to implement our unique constraint, and that's just table arrow unique. And then the square brackets don't forget that. That indicates an array

10:35

and then user I d in combination with Type I D. In combination with course, I d is required to be unique.

10:41

I'm going to save that,

10:45

and I'm gonna run the migration again.

10:52

And if we look at our d beaver, we can see that

10:56

on the user courses table within constraints, we have that unique constraint indicated by the key

11:01

so that completes that on Let's move on to the summary.

11:07

And that brings us to the 4.4 implement constraints summary. And this less than we implemented foreign key constraints. We implemented a unique composite key constraint that was on the pivot table that required that the user i d and the course I d be unique in combination.

11:24

We fixed some errors that we encountered my gracious need to occur in the correct order.

11:30

A migration will drop all the tables, and all the tables will be recreated, so you cannot implement a foreign key on a table that does not exist yet. So, for example, if you have five files and in file to you are

11:43

implementing a foreign key constraint to a table that is created in File three, that's going to fail because File three has not run yet.

11:52

Unique command requires a list in and Ray indicated by the brackets

11:56

and data types of must match. Being imager will not match with big imager unsigned, and that is because big imager without the unsigned call means that it could be native or positive, whereas big energy or unsigned can Onley be positive. So there's the possibility

12:15

that you would have some integrity loss in your data. If you are able to match those against each other,