SQL - Constraints • Foreign Key

--====================================================

SQL - Constraints  •    Foreign Key

--====================================================

DROP TABLE XX_EMP_TL

DROP TABLE XX_LOC_TL

----------  DEPT Table ------------

CREATE TABLE XX_LOC_TL

(

  LOC_CODE  NUMBER(2) PRIMARY KEY,

  NAME   VARCHAR2(100 BYTE)

  )

Insert into XX_LOC_TL (LOC_CODE, NAME) Values (01, 'DL');

Insert into XX_LOC_TL (LOC_CODE, NAME) Values (02, 'UP');

Insert into XX_LOC_TL (LOC_CODE, NAME) Values (03, 'MP');

COMMIT;

select * from XX_LOC_TL

LOC_CODE	NAME
1	DL
2	UP
3	MP

----------------------  EMP Table -----------------------

CREATE TABLE XX_EMP_TL

(

  EMPNO     NUMBER(4) PRIMARY KEY, -- Column Level Constraints --

  ENAME     VARCHAR2(100),

  GENDER CHAR(1),  

  SAL       NUMBER(7),

  LOC_CODE    NUMBER(2),

  CREATION_DATE Date Default sysdate

--  , CONSTRAINT XX_LOC_FK FOREIGN KEY ( LOC_CODE) REFERENCES XX_LOC_TL(LOC_CODE) -- Table Level Constraints ---

)

insert into  XX_EMP_TL (EMPNO, ENAME, GENDER, SAL, LOC_CODE, CREATION_DATE)  Values ( 1001, 'Sachin', 'M', 15000, 01, sysdate)

insert into  XX_EMP_TL (EMPNO, ENAME, GENDER, SAL, LOC_CODE, CREATION_DATE)  Values ( 1002, 'Ravi', 'M', 10000, 05, sysdate)

insert into  XX_EMP_TL (EMPNO, ENAME, GENDER, SAL, LOC_CODE, CREATION_DATE)  Values ( 1003, 'Mohan', 'M', 12000, 10, sysdate)

COMMIT;

select * from XX_EMP_TL

EMPNO	ENAME	GENDER	SAL	LOC_CODE	CREATION_DATE
1001	Sachin	M	15000	1	27-03-2020 17:14
1002	Ravi	M	10000	5	27-03-2020 17:14
1003	Mohan	M	12000	10	27-03-2020 17:14

--=============

select * from  XX_EMP_TL

select * from  XX_LOC_TL

UPDATE  XX_EMP_TL SET LOC_CODE = 1 where LOC_CODE <> 1

COMMIT;

EMPNO	ENAME	GENDER	SAL	LOC_CODE	CREATION_DATE
1001	Sachin	M	15000	1	27-03-2020 17:14
1002	Ravi	M	10000	1	27-03-2020 17:14
1003	Mohan	M	12000	1	27-03-2020 17:14

--=============== EXample:-1 ========================

ALTER TABLE XX_EMP_TL

ADD CONSTRAINT XX_LOC_FK

FOREIGN KEY ( LOC_CODE)

REFERENCES XX_LOC_TL(LOC_CODE)

select * from  XX_EMP_TL

select * from  XX_LOC_TL

delete FROM XX_LOC_TL where LOC_CODE = 1

delete  from  XX_EMP_TL where LOC_CODE = 1

ROLLBACK

--=============== EXample:-2 ========================

ALTER TABLE APPS.XX_EMP_TL  DROP CONSTRAINT XX_LOC_FK

ALTER TABLE XX_EMP_TL

ADD CONSTRAINT XX_LOC_FK

FOREIGN KEY ( LOC_CODE)

REFERENCES XX_LOC_TL(LOC_CODE)

ON DELETE SET NULL

select * from  XX_EMP_TL

EMPNO	ENAME	GENDER	SAL	LOC_CODE	CREATION_DATE
1001	Sachin	M	15000	1	27-03-2020 17:14
1002	Ravi	M	10000	1	27-03-2020 17:14
1003	Mohan	M	12000	1	27-03-2020 17:14

select * from  XX_LOC_TL

LOC_CODE	NAME
1	DL
2	UP
3	MP

delete FROM XX_LOC_TL where LOC_CODE = 1

LOC_CODE	NAME
2	UP
3	MP

EMPNO	ENAME	GENDER	SAL	LOC_CODE	CREATION_DATE
1001	Sachin	M	15000		27-03-2020 17:14
1002	Ravi	M	10000		27-03-2020 17:14
1003	Mohan	M	12000		27-03-2020 17:14

--=============== EXample:-3 ========================

ALTER TABLE APPS.XX_EMP_TL  DROP CONSTRAINT XX_LOC_FK

ALTER TABLE XX_EMP_TL

ADD CONSTRAINT XX_LOC_FK

FOREIGN KEY ( LOC_CODE)

REFERENCES XX_LOC_TL(LOC_CODE)

ON DELETE CASCADE

select * from  XX_EMP_TL

EMPNO	ENAME	GENDER	SAL	LOC_CODE	CREATION_DATE
1001	Sachin	M	15000	1	27-03-2020 17:14
1002	Ravi	M	10000	1	27-03-2020 17:14
1003	Mohan	M	12000	1	27-03-2020 17:14

select * from  XX_LOC_TL

LOC_CODE	NAME
1	DL
2	UP
3	MP

delete FROM XX_LOC_TL where LOC_CODE = 1

LOC_CODE	NAME
2	UP
3	MP

EMPNO	ENAME	GENDER	SAL	LOC_CODE	CREATION_DATE

--============= Check your Constraints --======================

SELECT * FROM ALL_CONSTRAINTS 

WHERE TABLE_NAME = 'XX_EMP_TL'

--============= Check your Constraints --======================

CONSTRAINT_NAME	CONSTRAINT_TYPE	TABLE_NAME
SYS_C005381532	P	XX_EMP_TL
XX_LOC_FK	R	XX_EMP_TL