CS 212 Software Development

CS 212-02 • Spring 2020

SQL Intro: Enforcing Relationships

But wait, there is something weird going on in our tables now:

SELECT * FROM demo_phones;
+----+------+----------+-------------+--------+
| id | area | number   | description | userid |
+----+------+----------+-------------+--------+
|  1 | 555  | 111-1111 | Work        |      1 |
|  2 | 555  | 222-2222 | Cell        |      1 |
|  3 | 555  | 333-3333 | Home        |      2 |
|  4 | 555  | 444-4444 | Home        |      4 |
|  5 | 555  | 555-5555 | Cell        |      5 |
|  6 | 555  | 777-7777 | NULL        |      6 |
+----+------+----------+-------------+--------+

Do we have a user with id 6 in our table?

SELECT * FROM demo_users WHERE userid=6;
Empty set (0.01 sec)

Why did our database let us add an entry for a non-existent user? For now, lets get rid of the invalid data:

DELETE FROM demo_phones WHERE id=6;
Query OK, 1 row affected (0.06 sec)

So, if we want MySQL to enforce relationships between our table we need to make sure we are using a database engine that enforces foreign key constraints, like InnoDB. Let’s fix our tables so this works, starting with demo_users. To see the details about this table, use the following command:

SHOW CREATE TABLE demo_users;

You should see something similar to:

+------------+-------------------------------+
| Table      | Create Table                  |
+------------+-------------------------------+
| demo_users | CREATE TABLE `demo_users` (
  `userid` int(11) NOT NULL DEFAULT '0',
  `name` varchar(15) NOT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8         |
+------------+-------------------------------+

If it isn’t using InnoDB as the ENGINE, then run the following commands:

ALTER TABLE demo_users ENGINE=InnoDB;
ALTER TABLE demo_phones ENGINE=InnoDB;

Next, we have to add in the FOREIGN KEY constraint:

ALTER TABLE demo_phones
ADD FOREIGN KEY (userid)
REFERENCES demo_users(userid);

Now, when we try to add a row with a non existent user, we get an error. Try it out:

INSERT INTO demo_phones
(number, userid)
VALUES ('777-7777', 6);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`user104`.`demo_phones`, CONSTRAINT `demo_phones_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `demo_users` (`userid`))

That is the entire example! Now that we are done with it, you might want to do some cleanup of your database. To remove the tables, do:

DROP TABLE demo_phones, demo_users;
SHOW TABLES;
Empty set (0.00 sec)