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
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 keys 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 keys 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
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
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.
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
person can either have zero or one
person cannot have MORE than one
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
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
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
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
1 is receiving
P7293-28745-92387. This means that Trevor Page will be associated with the
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;