4.7 LAB - Implement independent entity (Sakila)

Implement a new independent entity phone in the Sakila database. Attributes and relationships are shown in the following diagram:

The diagram uses Sakila naming conventions. Follow the Sakila conventions for your table and column names:

All lower case

Underscore separator between root and suffix

Foreign keys have the same name as referenced primary key

Write CREATE TABLE and ALTER TABLE statements that:

Implement the entity as a new phone table.

Implement the has relationships as foreign keys in the Sakila customer, staff, and store tables.

Remove the existing phone column from the Sakila address table.

Step 2 requires adding a foreign key constraint to an existing table. Ex:

ALTER TABLE customer

ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id)

ON DELETE SET NULL

ON UPDATE CASCADE;

Specify data types as follows:

phone_id, phone_number, and country_code have data type INT.

phone_type has date type VARCHAR(12) and contains strings like 'Home', 'Mobile', and 'Other'.

Apply these constraints:

NOT NULL constraints correspond to cardinalities on the diagram above.

Foreign key actions are SET NULL for delete rules and CASCADE for update rules.

Specify a suitable column as the phone table primary key.

My attempt:

CREATE TABLE phone ( `phone_id` INT NOT NULL, `country_code` INT NULL, `phone_type` ENUM( 'Home', 'Mobile', 'Other'), `phone_number` VARCHAR(45) NULL, PRIMARY KEY (`phone_id`), UNIQUE INDEX `phone_id_UNIQUE` (`phone_id` ASC));

ALTER TABLE customer ADD COLUMN `phone_id`, ADD CONSTRAINT `phone_fk` FOREIGN KEY (`phone_id`) REFERENCES phone (`phone_id`) ON DELETE SET NULL ON UPDATE CASCADE; ;

ALTER TABLE staff ADD COLUMN `phone_id`, ADD CONSTRAINT `phone_fk` FOREIGN KEY (`phone_id`) REFERENCES phone (`phone_id`) ON DELETE SET NULL ON UPDATE CASCADE; ;

ALTER TABLE store ADD COLUMN `phone_id`, ADD CONSTRAINT `phone_fk` FOREIGN KEY (`phone_id`) REFERENCES phone (`phone_id`) ON DELETE SET NULL ON UPDATE CASCADE; ALTER TABLE address DROP COLUMN `phone`;

Output:

ERROR 1064 (42000) at line 9: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', ADD CONSTRAINT `phone_fk` FOREIGN KEY (`phone_id`) REFERENCES phone (`phone_' at line 2