FOREIGN KEY Constraint Example

Steps	Script
Create a table with PK constraint	CREATE TABLE SamplePK01(SampID NUMBER(2) CONSTRAINT SamplePK01_SampID_PK PRIMARY KEY,SampName VARCHAR2(10),SampDate DATE);
Create a table with FK Constraint	CREATE TABLE SampleFK01(SampID NUMBER(2) CONSTRAINT SampleFK01_SampID_PK PRIMARY KEY, SampName VARCHAR2(10), SampDate DATE, SampIDFK NUMBER(2) CONSTRAINT SampleFK01_SampIDFK_FK REFERENCES SamplePK01(SampID));

Insert Below Records
INSERT INTO SampleFK01 VALUES(20, 'SAMPLE20', SYSDATE, NULL);
INSERT INTO SamplePK01 VALUES(10, 'SAMPLE10', SYSDATE);
INSERT INTO SamplePK01 VALUES(11, 'SAMPLE11', SYSDATE);
INSERT INTO SamplePK01 VALUES(12, 'SAMPLE12', SYSDATE);
INSERT INTO SamplePK01 VALUES(13, 'SAMPLE13', SYSDATE);
INSERT INTO SampleFK01 VALUES(21, 'SAMPLE21', SYSDATE, 10);
INSERT INTO SampleFK01 VALUES(22, 'SAMPLE22', SYSDATE, 10)
INSERT INTO SampleFK01 VALUES(23, 'SAMPLE23', SYSDATE, 12);

While Inserting below record we are getting error
INSERT INTO SampleFK01 VALUES(22, 'SAMPLE22', SYSDATE, 15);

INSERT INTO SampleFK01
*
ERROR at line 1: ORA-02291: integrity constraint (SCOTT.SAMPLEFK01_SAMPIDFK_FK) violated - parent key not found

We are unable to inser the row because in parent table does not have any parent for this child (SampID=1).

 Now Check the data in parent and child table

 SELECT * FROM SamplePK01;
 SELECT * FROM SampleFK01;

Now we will try to delete the record from the parent and child table
DELETE FROM SamplePK01 WHERE SampID = 13;
DELETE FROM SamplePK01 WHERE SampID = 12;
DELETE FROM SamplePK01
ERROR at line 1:ORA-02292: integrity constraint (SCOTT.SAMPLEFK01_SAMPIDFK_FK) violated - child record found
We are deleting the record from parent table but this parent record have child record so it will not allow you to delete the parent record. For that first we need to delete from child table then delete from parent table or can we use ON DELETE CASCADE or we can use ON DELETE SET NULL