Next, we will focus on the relationships between our tables. Make sure you have already logged into your database before following along with this demo. See the Database Accounts guide for details.
At this point in the example, there is something weird going on in our tables:
SELECT * FROM phones;
Do we have an office with id 6 in our table?
SELECT * FROM offices WHERE office_id=6;
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;
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;
If InnoDB is not listed as the ENGINE in the output, 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: