SQL-Primary key and foreign key Relation/Parent-child relationship between tables/ On delete cascade/foreign key constraint with Example

SQL-Primary key and foreign key Relation/Parent-child relationship between tables/ On delete cascade/foreign key constraint with Example

Suppose we have two table customer and supplier
CREATE TABLE supplier
( supplier_id numeric(10) not null,
 supplier_name varchar2(50) not null,
 contact_name varchar2(50),
 CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

insert into supplier values(1,'Sarat','Chand');
insert into supplier values(2,'Sartaj','Ahmed');
insert into supplier values(3,'Saral','Chand');


CREATE TABLE products
( product_id numeric(10) not null,
 supplier_id numeric(10) not null,
 CONSTRAINT fk_supplier
   FOREIGN KEY (supplier_id)
  REFERENCES supplier(supplier_id)
  ON DELETE CASCADE
);

insert into products values(101,1);
insert into products values(102,2);
insert into products values(103,3);

--Check/verify the entered data into the table using below command

select * from products;
select * from supplier ;

--not Allowed to add the product into the supplier table because the supplier id is not exist in the parent table name supplier

insert into products values(103,4);

--Error Message
--ORA-02291: integrity constraint (HR.FK_SUPPLIER) violated - parent key not found

So we can say that the table relation between table is established

--The data can be deleted from the supplier table because it is parent table but cannot delete the data into child table
--delete the data from supplier table whose supplier_id is 1;
delete from supplier where supplier_id=1;

delete from products where supplier_id=1;
--check the constraints
select * from user_cons_columns;
select * from user_constraints;