Including Foreign Key Constraints

1. Including Foreign Key Constraints

2. Not allowed(dept_nbr 9 does not exist in the DEPT table 7571 Ford Manager ... 200 9 7571 Ford Manager ... 200 Insert into Allowed The FOREIGN KEY Constraint Department PRIMARY key dept_nbrdept_name location 10 Accounting New York 20 Research Dallas ... Employee FOREIGN key employee_nbr name job ... Commission dept_nbr 7839 King President 10 7698 Blake Manager 30 ...

3. The FOREIGN KEY Constraint • Defined at either the table level or the column level MySQL> CREATE TABLE employee( 2 employee_nbr INTEGER(4), 3 name VARCHAR(10) NOT NULL, 4 job VARCHAR(9), 5 manager INTEGER(4), 6 hire_date DATE, 7 salary DECIMAL(7,2), 8 commission DECIMAL(7,2), 9dept_nbr DECIMAL(7,2) NOT NULL, 10 CONSTRAINTemployee_dept_nbr_fkFOREIGN KEY(dept_nbr) 11 REFERENCES dept (dept_nbr));

4. FOREIGN KEY Constraint Keywords • FOREIGN KEY • Defines the column in the child table at the table constraint level • REFERENCES • Identifies the table and column in the parent table • ON DELETE CASCADE • Allows deletion in the parent table and deletion of the dependent rows in the child table

5. Adding a Constraint • Add a FOREIGN KEY constraint to the Employee table indicating that a manager must already exist as a valid employee in the Employee table. MySQL> ALTER TABLE employee 2 ADD CONSTRAINTemployee_manager_fk 3 FOREIGN KEY(manager) REFERENCES employee(employee_nbr); Table altered.

6. Dropping a Constraint • Remove the manager constraint from the employee table. • Remove the PRIMARY KEY constraint on the Department table and drop the associate FOREIGN KEY constraint on the employee.dept_nbr column. MySQL> ALTER TABLE employee 2 DROP CONSTRAINT employee_manager_fk; Table altered. MySQL> ALTER TABLEdeptartment 2 DROP PRIMARY KEY CASCADE; Table altered.