Creating Tables with Foreign Key Contraints

It is quite normal in SQL databases to reference and ID number rather than a name. this way if the name changes that change can be reflected without the need of updating information in all the referencing tables. As the reference is to the ID not the name there is nothing to update so long as the ID does not change.

To display information to the user we would use JOINS so we can perform a look up on the ID to return a name. In the following table definition, the employees table references the department table using the DID column in each table. The data type must match and we must create an index on the referencing, employees, table.

CREATE TABLE employees (
eid TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
ename VARCHAR(10) NOT NULL,
did TINYINT UNSIGNED NOT NULL, PRIMARY KEY(eid),
INDEX(did),
FOREIGN KEY(did) REFERENCES department(did) ON UPDATE CASCADE ON DELETE CASCADE
)

The video follows: