DATABASE ENFORCING REFERENTIAL INTERGRITY

ENFORCING   REFERENTIAL  INTERGRITY

 

CREATING DATABASE

 

CREATE DATABASE REFERENTIAL

USE REFERENTIAL

 

CREATING TABLES AND INSRTING VALUES

 

CREATE TABLE CUSTOMER

(

CID VARCHAR (30) NOT NULL PRIMARY KEY,

CNAME VARCHAR (30) NOT NULL

)

 

INSERT INTO CUSTOMER VALUES ('C1','ANA')

 

CREATE TABLE PRODUCT

(

PID VARCHAR (30) NOT NULL PRIMARY KEY,

PNAME VARCHAR (30) NOT NULL,

CID VARCHAR (30) ,

FOREIGN KEY (CID) REFERENCES CUSTOMER  on update cascade  on delete no action

)

 

INSERT INTO PRODUCT VALUES ('P1','TECNO','C1')

 

VIEW YOUR TABLES

 

SELECT * FROM CUSTOMER

SELECT * FROM PRODUCT

 

VERIFY RULE

 

--update in mother table is possible

UPDATE CUSTOMER

SET CID='CUSTOMER1'

WHERE CID='C1'

 

--now, view product table, CID will automatic adopts changes to Customer1 as rules( on update cascade)

SELECT * FROM PRODUCT

 

--now, try to delete customer1,  is imposible  as rules ( on delete no action )

DELETE FROM CUSTOMER

WHERE CID='CUSTOMER1'

 

--but deletion on child table PRODUCT is posible, but the deleted data will still exist in mother table Customer

DELETE FROM PRODUCT

WHERE CID='CUSTOMER1'

 

--try to select customer table, you will see deleted data still exist

SELECT* FROM CUSTOMER

 

--insert again deleted data in product

INSERT INTO PRODUCT VALUES ('P1','TECNO','CUSTOMER1')

 

--imagine, we drop PRODUCT table and we recreate again with other referential rules

DROP TABLE PRODUCT

 

CREATE TABLE PRODUCT

(

PID VARCHAR (30) NOT NULL PRIMARY KEY,

PNAME VARCHAR (30) NOT NULL,

CID VARCHAR (30) ,

FOREIGN KEY (CID) REFERENCES CUSTOMER  on delete set null

)

 

--then insert data in product table

INSERT INTO PRODUCT VALUES ('P1','TECNO','CUSTOMER1')

 

 

--(ON delete set null ) will run if and only if when you design table, the column (CID VARCHAR (30) ,) is null otherwise the command will never run

 

--set null, when deleting data on mother table, will set null on mother table

 

DELETE FROM customer

WHERE CID='CUSTOMER1'

 

--now, select product table where null column is:

--this command returns empty

SELECT* FROM PRODUCT WHERE CID =''

--this command returns empty

SELECT* FROM PRODUCT WHERE CID =NULL

----this command return null column

SELECT* FROM PRODUCT WHERE CID IS NULL

 

--NB: ( ON DELETE SET NULL)  (ON DELETE SET DEFAULT)  are the same

 

--selectiong multiple tables

 

select * from customer,product

where customer.CID =product.CID

order by customer.CID asc

 

--create VIEW

 

create view  twotables AS

select * from customer

 

create view  ONEtables AS

SELECT* FROM PRODUCT

where CID='CUSTOMER1'

 

--SELECTING  VIRTUAL TABLES

 

select * from twotables

select * from onetables

 

 

REFERENCIAL INTERGRITY RULES

 

RULES	MOTHER TABLE	CHILD TABLE
Cascade delete	posible	Possible: But data will still exist on mother table
Cascade update	posible	Possible But data will still not automatic update  on mother table
Set default	Data is deleted	Colum is set to NULL
Set null	Data is deleted	Colum is set to NULL
Restrict no action	Deletion is  impossible	Deletion is possible But data will still exist on mother table
insertion	posible	Possible: if data exist on mother table otherwise imposible
Drop table	Imposible: There reference from child table	Possible:  but will delete referenced data on mother table

 

Share: