Learn SQL – Enforce Relationships Part II
Be sure to listen to the embedded podcast episode that goes along with this blog post. You can click the PLAY button on the player above.
In this SQL tutorial episode/post we're going to learn how to enforce our SQL relationships that we've already learned about. We're going to be tackling the one-to-one and many-to-many relationships and we're going to learn how to write the code to enforce these relationships in our database.
If you're not familiar with database relationships at all, I'd suggest you go back to my previous post where I introduce the concept of the database relationship.
If you want to jump back to Part I of how to enforce relationships with SQL code, then check out the last episode.
Many-to-Many SQL Code
As outlined in the podcast, we are going to be focusing on the many-to-many relationship with the author
and book
example. Remember that one author can publish many books, and one book can be written by many authors. This indicates a many-to-many relationship and I'm going to show you how to enforce that relationship in your database.
Here's the outline of how you would create these two tables:
create table author ( author_id int(11) auto_increment primary key, first_name varchar(20), last_name varchar(20) ); create table book ( book_id int(11) auto_increment primary key, book_title varchar(255), ISBN varchar(10), version int(11) );
As you can see, there's no indication of any relationships between these two tables. The author
table only contains information about the authors and the book
table only contains information about books. So where does the enforcement of the many-to-many relationship come from? Well, it comes from a third table which is known as the join table.
When creating the join table, it's good practice to give it the name of the two combining tables… so since we're combining the author
and the book
tables, the join table should be called author_book
. Here's what the table creation code looks like:
create table author_book ( author_id int(11), book_id int(11), foreign key (author_id) references author (author_id), foreign key (book_id) references book (book_id) );
There are two things that need to be mentioned here. The first thing that you should notice is that we did not assign either an auto_increment
or a primary key
property to either of the two columns in this join table. The reason for this is that these two columns are foreign key
s and you should never assign an auto_increment
to a foreign key. It also wouldn't make sense to assign the primary key
property to either of these columns, as neither one of them on their own would make up a unique identifier for the table… you'll see why in a minute.
The second thing you should take notice of in this table definition is that we are assigning TWO foreign key
s in the same table. I don't think I explicitly said this, but it is completely legal to have more than one foreign key
in a table. What's not legal is having more than one primary key
.
Cool? Cool!
Alright, now let's take a look at what kind of data we can put into these tables. I'm going to create a scenario where we have three different authors and four books. Here's what that could look like:
insert into author (first_name,last_name) values ('Trevor','Page'); insert into author (first_name,last_name) values ('Jane','Doe'); insert into author (first_name,last_name) values ('Jack','Johnson'); insert into book (book_title, ISBN, version) values ('How to Program with Java 2nd Edition', '1857283449', 2); insert into book (book_title, ISBN, version) values ('How to Program with Java', '1841268719', 1); insert into book (book_title, ISBN, version) values ('Cooking for Dummies', '1195348425', 1); insert into book (book_title, ISBN, version) values ('How to Play Guitar', '0584237850', 3); insert into author_book (author_id, book_id) values (1,1); insert into author_book (author_id, book_id) values (1,2); insert into author_book (author_id, book_id) values (2,3); insert into author_book (author_id, book_id) values (3,4);
The inserts for the first two tables are pretty straight-forward and I don't think I need to say much about them. The only thing that you need to know is that the primary keys will be automatically assigned for the two tables with each insert. So when we insert the three authors, each will receive a unique id… if this is the first time we're doing inserts on the author
and book
tables, then they will receive ids starting with number 1
. This means that Trevor Page will receive author_id = 1
, Jane Doe will be assigned author_id = 2
, and Jack Johnson will get author_id = 3
. The same logic will apply to the book
table entries (i.e. book_id 1, 2, 3, 4
).
Knowing that each row will receive a unique identifier, we can now see what the entries in the author_book
are doing. We are assigning author Trevor Page to books How to Program with Java 2nd Edition and How to Program with Java. Author Jane Doe is being assigned to book Cooking for Dummies and author Jack Johnson is assigned to How to Play Guitar.
Make sense?
Also, for those of you who are following along with this example by creating tables and inserting this data into your own database, here's the SQL script that you would use to query the tables by joining to the join table:
select author.first_name, author.last_name, book.book_title From author join author_book on author.author_id = author_book.author_id join book on book.book_id = author_book.book_id;
One-to-One SQL Code
Okay, so let's move onto the one-to-one relationship and how to recreate this in our database.
First thing to remember is that one-to-one means exactly what it says, only one record from one table can map to one record from another. In our case we are going to use the example of a person
and a drivers_license
. A person
can either have zero or one drivers_license
, a person
cannot have MORE than one drivers_license
.
Having laid out those rules, let's take a look at what we would need to do to create these two tables and enforce the relationship:
create table person ( person_id int(11) auto_increment primary key, first_name varchar(20), last_name varchar(20) ); create table drivers_license ( person_id int(11) primary key, license_number varchar(20), issue_date datetime, expiry_date datetime, foreign key (person_id) references person (person_id) );
The most important thing to note here is the drivers_license
table's primary key
. You may have expected to see the primary key
be a “drivers_license_id”, but in our case we don't even have that as a column. This is a special property of the one-to-one relationship… to enforce this relationship, we assign a foreign key
as the primary key
.
You should also note that we didn't assign an auto_increment
to the drivers_license
table. This is the same reason as in our many-to-many example, we don't assign an auto_increment
to a foreign key
.
Let's take a look at what this would look like with some real data:
insert into person (first_name,last_name) values ('Trevor','Page'); insert into person (first_name,last_name) values ('Jane','Doe'); insert into person (first_name,last_name) values ('Jack','Johnson'); insert into drivers_license (person_id, license_number, issue_date, expiry_date) values (1, 'P7293-28745-92387', '2007-12-18', '2022-12-18'); insert into drivers_license (person_id, license_number, issue_date, expiry_date) values (2, 'D4982-12684-48795', '2009-02-28', '2015-02-28'); insert into drivers_license (person_id, license_number, issue_date, expiry_date) values (3, 'J8984-49822-32156', '2012-07-19', '2017-07-19');
Here we're using the same people as we did in the “author” example… don't worry about the fact that the names match, this was just for the sake of reusing the same names and saving myself from thinking up three new names. As far as SQL is concerned, these three people are completely different people (as they exist in a completely separate table).
What is actually interesting to note, is that these three people will be automatically assigned unique IDs (just like in our many-to-many example above). So this means that the “people” will have IDs 1, 2
and 3
assigned to them from first insert to last insert.
Knowing this, we can figure out what's going on when we look at the insert statements for the drivers_license
table. We can see that person_id
1
is receiving license_number
P7293-28745-92387
. This means that Trevor Page will be associated with the license_number
P7293-28745-92387
(and so on).
To have a look at the results of this data, just execute the following query:
select person.first_name, person.last_name, drivers_license.license_number from drivers_license join person ON person.person_id = drivers_license.person_id;