Pekka Alaluukas /Databases

Database Exercises

Referential Integity

Create a database named integrity_example

Create a table named customer with below code

CREATE TABLE customer(
    idCustomer INT PRIMARY KEY,
    customerName VARCHAR(255)
);

Create a table named product with below code

CREATE TABLE product(
    idProduct INT PRIMARY KEY,
    productName VARCHAR(50),
    customerId INT,
    CONSTRAINT customer_product
        FOREIGN KEY(customerId) REFERENCES customer(idCustomer) 
        ON DELETE RESTRICT ON UPDATE CASCADE 
);

Add two customers with the code

INSERT INTO customer VALUES(1,'Jim Jones');
INSERT INTO customer VALUES(2,'Ann Smith');

Add two products with below code

INSERT INTO product VALUES(1,'Mobile phone',1);
INSERT INTO product VALUES(2,'Laptop',2);

Check that you managed to add the data.

Try to add a product with the code
```
INSERT INTO product VALUES(3,'Laptop',3);
```
You didn't managed to add the last product. What is the reason?

Update the id of Jim Jones with below code

UPDATE customer set idCustomer=100 WHERE idCustomer=1;

Check the data of table customer and table product. Note the idProduct values
Try to delete Jim Jones with the command
```
DELETE FROM customer WHERE idCustomer=100;
```
You didn't managed to add the last product. What is the reason?

Update the referential integrity rule with below code

ALTER TABLE product DROP CONSTRAINT customer_product;

ALTER TABLE product ADD CONSTRAINT customer_product
FOREIGN KEY(customerId) REFERENCES customer(idCustomer) 
ON DELETE CASCADE ON UPDATE CASCADE;

Try again to delete Jim Jones with the code

DELETE FROM customer WHERE idCustomer=100;

Check the data from both tables. The product Moblie phone should have been deleted.

Toggle Menu