Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

Video Transcription

00:00
I welcome back to Mosul. Five. This is less than 5.3 composite key composite keys are commonly found in commercial databases.
00:10
Composite key is when two or more keys indicate a unique record, and by keys I mean columns.
00:16
It's similar to a unique constraint.
00:20
A typical implementation you will see in commercial databases is the presence of a company I d. Column. You then have to use that company. I d in many of your queries to make sure that your tarting targeting the right company.
00:37
This allows software users to implement multiple departments, divisions or companies.
00:42
If you're right inquiries for a company that uses such a database,
00:47
you should write your queries in a way that uses the composite key, even if the database application currently does not have another company in it, because the users haven't
00:58
decided to use that future yet.
01:00
And the reason you want to do this is that if the decision is made later to add a company to the application and all your queries are written in a way where the assumption was made, that there would never be another company added,
01:12
they will avoid all your queries
01:15
so shown below is a simple query that respects a composite key.
01:21
So this works with one company or if multiple companies are in use, and in this example, it joins two tables by both the company i D and the B account I d.
01:33
Now. With that said, let's take a look at a diagram, often implementation of a school table that we're going to use in our database to implement a composite key. Here is the update of the ER de diagram. You can see this getting more complicated.
01:49
We have added a school table that will now be related to all the other tables by implementing a foreign key constraint on a school i d column.
01:57
And when we write queries against this database, we will want to write many of our queries to just include the school i D. Even if the school ideas not in use by the table connections.
02:08
Now it is common for er de diagrams to get more and more complicated as the database grows, and typically to deal with that, What you would do is you would exclude relationships that you're not investigating or you're not working with. For example,
02:24
once we know that school relationship is there. We may remove it from the RD diagram
02:29
so that we could get a cleaner look at the specific relationship that we're interested in looking at now. With that said, let's go ahead and implement this table. OK, I have a shell session open to the vag machine. I'm in the correct directory and I have my previous make model command, but I need to change it to the
02:47
the new model, which is school.
02:51
I'm gonna go ahead and go back to building and change it to school,
02:57
and we believe this dash dash migration on because that's going to create the model and the database migration. Our primary interest will be the database migration, but we might use the model later, so I'll go ahead to make that part of the command
03:15
so successfully created that table
03:17
and let's go
03:20
and find it.
03:23
So in our database migrations holder, I can see that we've added the school's table.
03:28
Now we're gonna have a foreign key constraint coming back from all the other tables, so we're going to need to move it up all the way to the front so that it's the first table that gets created, and we'll do that by changing the date on these migration files. So 10
03:43
05 and you will have to change the date
03:46
to the day and times that are reflective of when you created them.
03:53
So 033403
03:59
go
04:00
and we need to add the columns
04:05
and we don't have that many columns.
04:13
We have a name
04:17
and we have a description
04:29
you can.
04:32
I had to make that text.
04:35
I think that's the right one.
04:41
When you go ahead and save that
04:43
now, on the other tables, we need to add the foreign key constraint.
04:47
So first I'll start with the users table. Actually, I will go to a table that has a foreign key.
04:54
I'll start with the users table.
04:58
Andi, I will need to change this.
05:03
Let's see here so foreign.
05:08
Actually, we also need to add a
05:11
We need to add the calm.
05:13
So
05:15
we need one of these big energy on sign columns from borrowed that from here,
05:20
and we will call this school idea
05:25
unsigned
05:26
and then foreign school I D
05:34
preferences. I d on
05:38
schools
05:40
on delete Cascade. That should work.
05:43
Go ahead and copy they used to together so I can just paste into my other files.
05:53
That should work
05:58
this right here
06:03
on pace. This right here,
06:10
Andi
06:13
pace. That's right here
06:16
and
06:18
paste it right here.
06:23
Okay? And then I want to save all.
06:26
And I think we are ready to run the migration and make sure that it works Are db cedar?
06:33
I don't believe we'll work, so but we're gonna fix that in the next lesson.
06:38
B c
06:43
my great fresh.
06:48
Okay, It says I have a duplicate column. May about. I already had that somewhere.
06:55
School I d
07:00
great table users. Okay.
07:01
Yep.
07:03
I need to delete this one.
07:10
Can't have two columns with the same name
07:13
and hit up.
07:14
Rerun the migration,
07:19
and it looks like it worked. I'm going to D beaver.
07:24
We're going to refresh it
07:28
on going to double click on this on the database name.
07:30
Go to the er de diagram
07:33
on. We can see.
07:44
Well,
07:45
we didn't need that on school, So let's go back into these schools table.
07:49
So I just want to my files and connected it
07:53
so it's actually unnecessary on this table.
07:57
Let's go ahead and save that and rerun the migration
08:07
and we'll go over here and refresh.
08:13
There we go
08:16
and we can see all our foreign key constraints lining up.
08:20
And in the next lesson, we're going to fix our devi cedar so that we see data in different schools.
08:26
So let's go ahead and head over to the summary.
08:31
So that brings us to the 5.3 composite key summary.
08:33
We discussed composite keys in this lesson.
08:37
They are commonly found in commercial databases.
08:39
We implemented a new school table to implement our own composite key, and the next lesson. We are going to fix our database cedar so that we can cede data at different schools. And we also reviewed and updated the ER de diagram, which will, of course, be available for download as well.
08:58
And that completes this lesson. I hope to see you in the next thank you

Up Next

Intermediate SQL

This free course introduces the student to intermediate concepts found in the implementation and application of Structured Query Language (SQL) within professional business environments.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor