Understanding Declarative Referential Integrity and the FOREIGN KEY Constraint

You learned in Chapter 1, "Understanding SQL and Relational Databases," about the relationships that can exist between data tables; Declarative Referential Integrity (DRI) is what makes sure that the rules aren't broken.

You saw that both one-to-many and many-to-many relationships are physically implemented as one-to-many relationships (a many-to-many relationship is composed of two one-to-many relationships). By "declaring" these relationships to the database, you ensure the database will take care of itself and enforce those rules.

You can enforce a one-to-many relationship in the database through foreign keys, which tie the referencing table to the referenced table. To test foreign keys, you'll add something to the Friend scenario: You'll assume that each friend can have any number of phone numbers, thus creating a simple one-to-many scenario.

To implement this, you'll need to store the phone numbers in a separate table named Phone. Phone will have three columns: an ID column named PhoneID, which will be the primary key; a FriendID column, which links to the friend that has the phone number; and a PhoneNo column, which contains the actual phone number. Figure 12-1 shows the relationship between Phone and Friend.

Figure 12-1: The relationship between Phone and Friend

The referential integrity in this case is between the FriendID column in the Phone table and the FriendID column in the Friend table. This ensures that each record in the Phone table belongs to a real record of the Friend table.

The arrow in Figure 12-1 shows the nature of the relationship that happens between Friend and Phone. It points to the one table of the one-to-many relationship, which is the table whose primary key field is being referenced.

The FOREIGN KEY constraint applies on the column in the referencing table— in this case, the FriendID field in Phone. So, let's build the two data tables.

If you're using SQL Server, Oracle, DB2, or Access, create Friend and Phone like this:

CREATE TABLE Friend (FriendID INT PRIMARY KEY NOT NULL,
                      Name VARCHAR(50));

CREATE TABLE Phone (
   PhoneID INT PRIMARY KEY NOT NULL,
   FriendID INT,
   PhoneNo VARCHAR(20),
   CONSTRAINT FID_FK FOREIGN KEY(FriendID)
      REFERENCES Friend(FriendID));

Let's examine how you create the constraint in Phone. First, you use the CONSTRAINT keyword to supply a name for the new constraint. FOREIGN KEY(FriendID) specifies that you're placing a FOREIGN KEY constraint on the FriendID column in the current table (Phone). You then use the REFERENCES keyword to specify the column that the foreign key points to: In this case, you're referencing Friend(FriendID), which means the FriendID column in the Friend table.

This command creates a FOREIGN KEY constraint named FID_FK, which enforces the one-to-many relationship between Friend and Phone. As usual, if you're not interested in providing a name for the constraint, you can let the database take care of this by not supplying the CONSTRAINT keyword:

CREATE TABLE Phone
   (PhoneID INT PRIMARY KEY NOT NULL,
    FriendID INT,
    PhoneNo VARCHAR(20),
    FOREIGN KEY(FriendID) REFERENCES Friend(FriendID));

Because SQL is flexible, each database usually supports even more ways to create a FOREIGN KEY constraint. For example, if you're using SQL Server, here's a faster way to create Phone:

CREATE TABLE Phone (
   PhoneID INT PRIMARY KEY NOT NULL,
   FriendID INT FOREIGN KEY REFERENCES Friend(FriendID),
   PhoneNo VARCHAR(20));

MySQL supports foreign keys only with the InnoDB table type—it's not the first time you've encountered this table type because it's the most advanced table type supported by MySQL (as you saw in Chapter 10, "Transactions," it's the only one that fully supports the ACID transaction properties).

Both the referencing and referenced tables must be InnoDB tables, so don't forget to add TYPE=InnoDB at the end of the CREATE TABLE statement; otherwise, MySQL will simply ignore any FOREIGN KEY constraint you may define.

InnoDB tables also require you to create an index on the column being used as a foreign key. Here's how you create the Phone table with MySQL:

CREATE TABLE Phone (
   PhoneID INT PRIMARY KEY NOT NULL,
   FriendID INT,
   PhoneNo VARCHAR(20),
   FOREIGN KEY(FriendID) REFERENCES Friend(FriendID),
   INDEX idx1(FriendID))
TYPE=InnoDB;

You can test the relationship by executing the following commands:

INSERT INTO Friend (FriendID, Name) VALUES (1, 'Helen');
INSERT INTO Friend (FriendID, Name) VALUES (2, 'Susan');
INSERT INTO Phone (PhoneID, FriendID, PhoneNo)
   VALUES (1, 1, '555-HELEN');
INSERT INTO Phone (PhoneID, FriendID, PhoneNo)
   VALUES (2, 1, '555-HL-WORK');
INSERT INTO Phone (PhoneID, FriendID, PhoneNo)
   VALUES (3, 2, '555-SUZY-Q');
INSERT INTO Phone (PhoneID, FriendID, PhoneNo)
   VALUES (5, 9, '0982734833');

The last command will generate an error because you tried to add a phone number for a friend that doesn't exist (a friend with an ID of 9). Because the phone numbers are stored in a separate table, you need to do a table join in order to get a list with your friends and their phone numbers (you learned about table joins in Chapter 7, "Querying Multiple Tables"):

SELECT Friend.FriendID, Friend.Name, Phone.PhoneNo
FROM Friend
   INNER JOIN Phone
   ON Friend.FriendID = Phone.FriendID
ORDER BY Friend.Name;

The results are as follows:

   Name                   PhoneNo

   -------------------- --------------------
   Helen                  555-HELEN
   Helen                  555-HELEN-WORK
   Susan                  555-SUZY-Q