Tutorial 14- ON DELETE RESTRICT AND CASCADE Video Lecture | Postgresql: A Complete Tutorial - Database Management

hello and welcome to a shootable of
PostgreSQL today we will be making
another stable version of the database
that we made the last time so if you
miss the last lesson I recommend that
you should watch it again so today we
will be discussing about what if a
product is removed after an order is
created that is already referencing it
and what happens when an order is
removed and it also removes the order
items so we will be using the restrict
and the Cascade methods today so what
we'll be discussing about it is what
happens if suppose a user has already
ordered for a product and if we are out
of stock and we remove the product so
this is against the action as the user
has already ordered for that product and
we need to deliver it so we cannot
delete the product without delivering
that order or we cannot delete the
product until and unless it is not gone
from our orders list or until unless it
is available or until and unless it is
there in the products in the orders list
that the user has ordered we cannot
delete the product and the second one if
a user is cancelling an order that is
suppose I do not want to purchase
something I have mood swing and I don't
want my order anymore so what if if I
cancel my order it should cancel all the
products associated with it
so for that we'll be creating an table
that uses district and cascade methods
so I will be using the products and the
orders table is that we created last
time I'll be altering the order items
table so I will be creating it again so
you could create it again that is create
table order items
and I have a product number that is a
vanity retyped that references products
and product number next I hat an order
ID and there is a due date that
reference reference is order order ID
next I at quantity of interior type and
I had a primary key number already
so this was my order item stable last
time so what I will be doing is I adding
something that is on delete restrict
what this means is that if I have an
order or if I have an product that is in
transit or that has been ordered so the
seller cannot cancel any product or he
cannot remove the product from his
database or his table until and unless
this order has been delivered or until
and let this product it exists in the
order items table the product cannot be
removed it cannot be removed without
being delivered or it cannot be removed
in between over transit so this is the
purpose of on delete restrict that means
it would restricted from delete until
and unless this product number is there
in this order item stable next what
we'll be doing is Auto item is will be
creating and on delete cascade what this
means is that whenever a user cancels an
order whenever he removes an order all
the orders associated with his orders
will be cancelled like all the items
would be removed if he cancels his main
order so that is the purpose of on
delete cascade that means if that main
item is deleted all the items in this is
also deleted so we'll be testing it to
see if it is done yes and now you could
test it with the GUI that is the refresh
it now you see if I have products that
is my products are I will be adding a
new product that as a shampoo
and price is 200 now you suppose that
the orders table
I have already do I orders that is okay
and now see the order items table and I
will be creating a number that is to
same stay again and one for the order
number all for the quantity and next to
83 and same order number that is 1 so
you would see that this is now if I see
that I have my products table that is
would see the products table here I have
3 products that is 1 2 & 3 and now I
have ordered for 2 products that is
product number 2 and product number 3
have been ordered by user number 1 now
suppose what happens that if I have
ordered for this product and the main
supplier it deletes this product so this
condition should be prevented that is
suppose I have a user has ordered
product number 2 and product number 3
but for some reasons I go out of stock
and I decide to delete this product
without caring that some user has
entered this so this should be prevented
like suppose if I have been ordered for
2 and I try to delete it you would see
that I get an error update or delete on
products table it violates the foreign
key constraint and you could see the
reason that it is still different from
table order items so it is still
reference from the table order item
means there is a demand for this product
there is still a demand from this
product and I cannot delete the product
now suppose I have product number one
whose demand does not exist now and I
have successfully supplied all their
products and there is no more demand for
this product so I can delete this and
you would see that this would delete see
this deletes and these are not deleted
because the product is still in demand
the next thing that we added is the cart
the order now you would see the orders
table now as you could see that I have
order ID 1 and order ID - now you could
see that order ID 1 is basically the
order for this one alrighty 1 now
alright even consists of two products
that is protein number 2 and number 3 of
quantity one each now suppose if I
delete this order or I don't want this
order anymore and I decide see now if I
delete this this would delete all the
orders or all the products that I've
ordered for now I delete one and you
would see that in I will refresh this
here and you see that this is gone now
as you could see this is the role of on
delete cascade system that if I do not
want to order any product anymore so
this would delete all the defense's in
another table
so thank you for watching this tutorial
subscribe for more