CIS395 Database Systems I - Foreign Keys Lab - In order to get complete credit for this lab, you must provide all the source code used on a separate .sql or .txt file. - Create a word document and include screeshots of all the results of the queries on this lab. - Do not upload the file unless it is compressed into a ZIP file (no .rar files) - Name your zip file as follows: 395_last_name_studentid_fkeys.zip where last_name is your Last Name and studentid is your Student ID. - Non ZIP files will be ignored and get a grade of 0. - Not follwoing the naming convention will be ignored with a grade of 0. ------------------------------------- FOREIGN KEYS LAB ------------------------------------- Lets create a new database called fkdemo for the demonstration. CREATE DATABASE fkdemo; USE fkdemo; RESTRICT & NO ACTION actions Inside the fkdemo database, create two tables categories and products: CREATE TABLE categories( categoryId INT AUTO_INCREMENT PRIMARY KEY, categoryName VARCHAR(100) NOT NULL ); CREATE TABLE products( productId INT AUTO_INCREMENT PRIMARY KEY, productName varchar(100) not null, categoryId INT, CONSTRAINT fk_category FOREIGN KEY (categoryId) REFERENCES categories(categoryId) ); The categoryId in the products table is the foreign key column that refers to the categoryId column in the categories table. Because we dont specify any ON UPDATE and ON DELETE clauses, the default action is RESTRICT for both update and delete operation. The following steps illustrate the RESTRICT action. 1) Insert two rows into the categories table: INSERT INTO categories(categoryName) VALUES ('Smartphone'), ('Smartwatch'); 2) Select data from the categories table: SELECT * FROM categories; 3) Insert a new row into the products table: INSERT INTO products(productName, categoryId) VALUES('iPhone',1); It works because the categoryId 1 exists in the categories table. 4) Attempt to insert a new row into the products table with a categoryId value does not exist in the categories table: INSERT INTO products(productName, categoryId) VALUES('iPad',3); MySQL issued the following error: Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT) 5) Update the value in the categoryId column in the categories table to 100: UPDATE categories SET categoryId = 100 WHERE categoryId = 1; MySQL issued this error: Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT) Because of the RESTRICT option, you cannot delete or update categoryId 1 since it is referenced by the productId 1 in the products table. CASCADE action -------------- These steps illustrate how ON UPDATE CASCADE and ON DELETE CASCADE actions work. 1) Drop the products table: DROP TABLE products; 2) Create the products table with the ON UPDATE CASCADE and ON DELETE CASCADE options for the foreign key: CREATE TABLE products( productId INT AUTO_INCREMENT PRIMARY KEY, productName varchar(100) not null, categoryId INT NOT NULL, CONSTRAINT fk_category FOREIGN KEY (categoryId) REFERENCES categories(categoryId) ON UPDATE CASCADE ON DELETE CASCADE ); 3) Insert four rows into the products table: INSERT INTO products(productName, categoryId) VALUES ('iPhone', 1), ('Galaxy Note',1), ('Apple Watch',2), ('Samsung Galary Watch',2); 4) Select data from the products table: SELECT * FROM products; +-----------+----------------------+------------+ | productId | productName | categoryId | +-----------+----------------------+------------+ | 1 | iPhone | 1 | | 2 | Galaxy Note | 1 | | 3 | Apple Watch | 2 | | 4 | Samsung Galary Watch | 2 | +-----------+----------------------+------------+ 5) Update categoryId 1 to 100 in the categories table: UPDATE categories SET categoryId = 100 WHERE categoryId = 1; 6) Verify the update: SELECT * FROM categories; +------------+--------------+ | categoryId | categoryName | +------------+--------------+ | 2 | Smartwatch | | 100 | Smartphone | +------------+--------------+ 7) Get data from the products table: SELECT * FROM products; +-----------+----------------------+------------+ | productId | productName | categoryId | +-----------+----------------------+------------+ | 1 | iPhone | 100 | | 2 | Galaxy Note | 100 | | 3 | Apple Watch | 2 | | 4 | Samsung Galary Watch | 2 | +-----------+----------------------+------------+ As you can see, two rows with value 1 in the categoryId column of the products table were automatically updated to 100 because of the ON UPDATE CASCADE action. 8) Delete categoryId 2 from the categories table: DELETE FROM categories WHERE categoryId = 2; 9) Verify the deletion: SELECT * FROM categories; +------------+--------------+ | categoryId | categoryName | +------------+--------------+ | 100 | Smartphone | +------------+--------------+ 10) Check the products table: SELECT * FROM products; +-----------+----------------------+------------+ | productId | productName | categoryId | +-----------+----------------------+------------+ | 1 | iPhone | 100 | | 2 | Galaxy Note | 100 | +-----------+----------------------+------------+ All products with categoryId 2 from the products table were automatically deleted because of the ON DELETE CASCADE action. SET NULL action --------------- These steps illustrate how the ON UPDATE SET NULL and ON DELETE SET NULL actions work. 1) Drop both categories and products tables: DROP TABLE IF EXISTS categories; DROP TABLE IF EXISTS products; 2) Create the categories and products tables: CREATE TABLE categories( categoryId INT AUTO_INCREMENT PRIMARY KEY, categoryName VARCHAR(100) NOT NULL ); CREATE TABLE products( productId INT AUTO_INCREMENT PRIMARY KEY, productName varchar(100) not null, categoryId INT, CONSTRAINT fk_category FOREIGN KEY (categoryId) REFERENCES categories(categoryId) ON UPDATE SET NULL ON DELETE SET NULL ); The foreign key in the products table changed to ON UPDATE SET NULL and ON DELETE SET NULL options. 3) Insert rows into the categories table: INSERT INTO categories(categoryName) VALUES ('Smartphone'), ('Smartwatch'); 4) Insert rows into the products table: INSERT INTO products(productName, categoryId) VALUES ('iPhone', 1), ('Galaxy Note',1), ('Apple Watch',2), ('Samsung Galary Watch',2); 5) Update categoryId from 1 to 100 in the categories table: UPDATE categories SET categoryId = 100 WHERE categoryId = 1; 6) Verify the update: SELECT * FROM categories; +------------+--------------+ | categoryId | categoryName | +------------+--------------+ | 2 | Smartwatch | | 100 | Smartphone | +------------+--------------+ 7) Select data from the products table: SELECT * FROM products; +-----------+----------------------+------------+ | productId | productName | categoryId | +-----------+----------------------+------------+ | 1 | iPhone | null | | 2 | Galaxy Note | null | | 3 | Apple Watch | 2 | | 4 | Samsung Galary Watch | 2 | +-----------+----------------------+------------+ The rows with the categoryId 1 in the products table were automatically set to NULL due to the ON UPDATE SET NULL action. 8) Delete the categoryId 2 from the categories table: DELETE FROM categories WHERE categoryId = 2; The values in the categoryId column of the rows with categoryId 2 in the products table were automatically set to NULL due to the ON DELETE SET NULL action. SELECT * FROM products; +-----------+----------------------+------------+ | productId | productName | categoryId | +-----------+----------------------+------------+ | 1 | iPhone | null | | 2 | Galaxy Note | null | | 3 | Apple Watch | null | | 4 | Samsung Galary Watch | null | +-----------+----------------------+------------+ END OF LAB Follow instruction on the top of the document before submit.