The update failed again! The database keeps an eye on the employees too.
Of course, it's important to control the values inserted into column manager_id in the project table, but if you view this from the opposite direction, the column id in the employee table must be monitored as well. If you changed the id of an employee who's a manager on a project, the project would have an incorrect manager_id value. Makes sense, right?
It's your turn to try to create a table with a foreign key.
We want another table in our database. It's called department and contains the following columns:
id – integer and primary key.name – varchar of up to 100 characters (not NULL).manager_id – an integer, not NULL. It is also a foreign key which points to the column id in the employee table.Do you remember how to create a foreign key? If you want to create a foreign key constraint on column1 that points to column2 in table2, you can write:
FOREIGN KEY(column1) REFERENCES table2(column2)
Type:
CREATE TABLE department ( id integer PRIMARY KEY, name varchar(100) NOT NULL, manager_id integer NOT NULL, FOREIGN KEY(manager_id) REFERENCES employee(id) );
