Using the database posted do the following: Finish the examples for the following rules:

on update set NULL;

on update restrict;

on update set default;

on delete restrict;

on delete set default;

### This file is prepared to explain the basic operations with foreign key reference ### It does not mention cross-reference

DROP DATABASE IF EXISTS employees; CREATE DATABASE IF NOT EXISTS employees;

USE employees; CREATE TABLE employees ( emp_no INT NOT NULL, birth_date DATE NOT NULL, first_name VARCHAR(14) NOT NULL, last_name VARCHAR(16) NOT NULL, gender ENUM ('M','F') NOT NULL, hire_date DATE NOT NULL, PRIMARY KEY (emp_no) );

CREATE TABLE salaries ( emp_no INT NOT NULL, salary INT NOT NULL, from_date DATE NOT NULL, to_date DATE NOT NULL, PRIMARY KEY (emp_no, from_date), constraint salaries_employees_empNo foreign key (emp_no) references employees(emp_no) );

INSERT INTO `employees` VALUES (10001,'1953-09-02','Georgi','Facello','M','1986-06-26'), (10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'), (10003,'1959-12-03','Parto','Bamford','M','1986-08-28'), (10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01'), (10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12') ;

INSERT INTO `salaries` VALUES (10001,60117,'1986-06-26','1987-06-26'), (10001,62102,'1987-06-26','1988-06-25'), (10002,65828,'1996-08-03','1997-08-03'), (10002,65909,'1997-08-03','1998-08-03'), (10003,40006,'1995-12-03','1996-12-02'), (10003,43616,'1996-12-02','1997-12-02'), (10004,40054,'1986-12-01','1987-12-01'), (10004,42283,'1987-12-01','1988-11-30'), (10005,78228,'1989-09-12','1990-09-12'), (10005,82621,'1990-09-12','1991-09-12');

set foreign_key_checks = 1;

alter table salaries drop foreign key salaries_employees_empNo;

alter table salaries add constraint salaries_employees_empNo foreign key (emp_no) references employees(emp_no) on update cascade; ##on update set NULL; ##on update restrict;

##on delete cascade; ##on delete set NULL; ##on delete restrict; update employees set emp_no = 299 where emp_no = 10001;

select * from employees;

select * from salaries;

alter table salaries drop foreign key salaries_employees_empNo;

alter table salaries add constraint salaries_employees_empNo foreign key (emp_no) references employees(emp_no) on delete cascade;

delete from employees where emp_no = 10002;

select * from employees; ### entry with emp_no=10002 in employees table is deleted

select * from salaries; ### entry with emp_no=10002 in salaries table is deleted too.

alter table salaries drop foreign key salaries_employees_empNo;

alter table salaries add constraint salaries_employees_empNo foreign key (emp_no) references employees(emp_no) on delete set null; ### Here cannot use this reference option, because emp_no in salaries table cannot be NULL ###emp_no is primary key OR part of the primary key

###Let's use another table

CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(50) NOT NULL, category VARCHAR(25) );

CREATE TABLE inventory ( inventory_id INT PRIMARY KEY, product_id INT, quantity INT, min_level INT, max_level INT, CONSTRAINT fk_inv_product_id FOREIGN KEY (product_id) REFERENCES products (product_id) ON DELETE SET NULL );

######http://www.techonthenet.com/sql_server/foreign_keys/foreign_delete_null.php

insert into products values (111, 'Textpad', 'Office'), (222, 'Table', 'Office'), (333, 'Basketball', 'Wellness'), (555, 'Yoga mat', 'Wellness');

insert into inventory values (01, 111, 100, 20, 30), (02, 222, 200, 10, 50), (03, 333, 300, 3, 20), (05, 555, 211, 2, 60);

delete from products where product_id = 111;

select * from products;

select * from inventory;

alter table inventory drop foreign key fk_inv_product_id;

alter table inventory add constraint fk_inv_product_id foreign key (product_id) references products(product_id);

delete from products where product_id = 222; #By default "No Action": Reject delete from parent