CS 272 Software Development

CS 272-01, CS 272-02 • Spring 2022

SQL Intro: Enforcing Relationships

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

SELECT * FROM phones;
+----+------+----------+-------------+-----------+
| id | area | phone    | description | office_id |
+----+------+----------+-------------+-----------+
|  1 | 415  | 422-5050 | Office      |         3 |
|  2 | 415  | 422-7256 | Office      |         2 |
|  3 | 415  | 422-5797 | Office      |         1 |
|  4 | 888  | 471-2290 | Fax         |         1 |
|  5 | 415  | 422-6351 | Office      |         4 |
|  6 | 415  | 422-6352 | Office      |         4 |
|  7 | 855  | 531-0761 | After Hours |         4 |
|  8 | 555  | 422-5555 | NULL        |         6 |
+----+------+----------+-------------+-----------+
8 rows in set (0.00 sec)

Do we have an office with id 6 in our table?

SELECT * FROM offices WHERE office_id=6;
Empty set (0.00 sec)

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

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

So, if we want our database 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 offices;

You should see something similar to:

+---------+--------------------------+
| Table   | Create Table             |
+---------+--------------------------+
| offices | CREATE TABLE `offices` (
  `office_id` int(11) NOT NULL,
  `office` char(5) NOT NULL,
  PRIMARY KEY (`office_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+--------------------------+
1 row in set (0.00 sec)

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

ALTER TABLE offices ENGINE=InnoDB;
ALTER TABLE phones ENGINE=InnoDB;

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

ALTER TABLE phones
ADD FOREIGN KEY (office_id)
REFERENCES offices(office_id);

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

INSERT INTO phones VALUES
(8, '555', '422-5555', null, 6);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`user100`.`phones`, CONSTRAINT `phones_ibfk_1` FOREIGN KEY (`office_id`) REFERENCES `offices` (`office_id`))

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 phones, offices;
SHOW TABLES;
Empty set (0.01 sec)

If you want to load this entire example all at once, use:

SOURCE /home/public/cs272/sql/intro.sql