Tutorial 13- PRIMARY KEY AND FOREIGN KEY Video Lecture | Postgresql: A Complete Tutorial - Database Management

otherwise welcome to our uno PostgreSQL
in this tutorial we'll be talking about
primary keys and foreign keys primary
key basically indicates that a column or
a group of columns basically they can be
used as unique identifiers for the rows
in our table or for the data in our
table so this requires that the values
are both unique and null so we learn
both unique and not the constraints in
the previous tutorials but to Club up
these attributes there is one feature
known as primary key that that is very
important concept in the field of
databases so we will be learning how to
create a primary key on a row so first
of all we'll be creating the products
table again that is we adding a table
that is create table products then we
will be having a product number that
will be acting as a primary key so this
here declares the product number as the
primary key this means that the product
number has to be unique for each product
and it cannot be null so primary key
defines that it uniquely identifies each
product next I have the name as the text
and next I will be having price of
numeric type so this is it for our table
suppose we wanted to add a couple of
primary keys that is a combination of
Rock number and name as the primary key
so we could also do it by this way that
is we have to mention primary key here
and we will mention the name and the
product number and we would not need
primary key head then we will just need
in future
yeah so this indicates that the primary
key is made up of two rows that is this
so we will not be using two rows here or
two attributes I will be just adding a
primary key to the number volume and you
could see that it is no success for the
next I will be entering some data into
it for this I can enter the graphical
one because now I have the primary key
so you could use this too so I will be
entering one and so and price will be 50
now I will enter one again and you would
see that I get an error when I enter
that is anything that is hello and you
see that I have the error duplicate key
in violates unique constraint products
primary key so you see that I get an
error key product number is equal to one
that already exists so I cannot enter
one more product that has the same
product number as this so I have to
enter two and into hello
and I will enter 100 and you see that it
successfully enters so this is the rule
of a primary key in our table usually in
a relational database management system
there is a constraint or there is a
restriction that you have to have a
primary key for each and every table but
PostgreSQL it it does not enforce it to
you that you should have a primary key
in your table but it should be a good
practice to enter primary key for your
table so for example if we have a user
name in our database so if a user has
lost his password and we need to reset
their password so we need to have a
unique identifier to identify each and
every user and reset their password so a
username would be the primary key so you
could just visualize what is the concept
of primary key and how is it used is the
very important concept of database
management in any of the databases so
next would be talking about foreign keys
so a foreign key
silly constraint that specifies the
value in a column that must match the
values of another column that appears in
another table that is suppose I have a
product number here and then I have
another table that has a product number
so that product number should be equal
to this product number so that there is
a referential integrity between our
tables or this table can be linked to
that table so we could I'll be showing
you it with an example the usage of a
foreign key so a foreign key can be
basically used in this way so that we
have same values in both the tables and
to get that that table can be linked to
each other so for the falling key I'll
be just creating one more table so that
you could see one more example of this
foreign key would be that for example if
we have an orders table so like we would
like to have orders for only those
products that we have in our database or
that are available to us so for that we
will be using the concept of foreign key
so I will create a table orders that
indicates the orders next I will be
creating the orders table that is create
table orders and I have order ID that
would be the primary key for this table
next I will have the product number now
remember product number was there in the
products table also and this would be
the same as that of the products table
so that we have a referential integrity
between our tables so that that value
could be matched with this value and we
could compare those values and we could
literally link these tables and create
joins that we'll talk about later and we
could just relate these two tables with
each other so this is the basic concept
of foreign key that is relating two
tables with each other so this is the
way we create a foreign key that
product and the product number so this
statement basically tells the product
number that is the four that is the
primary key in the product stable should
be the foreign key or the product number
here it references the primary key of
the product stable so this becomes the
foreign key here next we have the
quantity of integer type and I missed
interior so that's it and you will see
that it run successfully so now it is
impossible to create an order without
and without product in our entry that we
don't have so I will be showing that to
you in a while so for example I will be
using the
GUI to enter it so for example I have
two products now that is a soap and a
health product so now I want to order a
soap so you would see that I go to
orders and I write that is order ID
should be anything and a product number
is one for a soap and I want to source
so this creates a soap order for a soap
so next thing you would see is that I
had two products here that is a soap and
a Hello with product number one and two
now suppose I'll make an order that has
a product number three now you would see
that this thing is not possible at all
now suppose I enter an order ID of two
and I enter a product ID of three that
does not exist at all in the products
table I will see that I get an error
that is insert or delete or update on
table order while it's foreign key
constraint or the product so you would
see that an order would never be created
if I don't have the product available in
stock so this is how our database is
managed so we will see that this does
not create what I will have to do is I
have to create it as two now I have a
hello product as product number as two
so you would see that this creates
successfully so that is the use of
foreign key so now suppose we want to
allow that order should possibly entry
it should contain many products again so
we will create another table for that
that is order items that is suppose if
we want that in an order it should be a
card type or in an order or user can
order many products so for that we will
create another table and we would be
deleting or altering the order table in
this case we won't refer this so I'll
create the order table once again for
you so first of all I will be creating
the table orders that
is a create table orders that is the
order ID integer type that is the
primary key and next I will be having a
ship or an address that is of a text
type so that's it for this table next I
will be creating an order items table
that is create table order items that is
product number now you would know what
is product number here that is the same
product number as that of the products
table and it is a foreign key references
the products
of product number next I have an order
ID now you would know what is an order
ID there is a four primary key of the
orders table now you could see that we
can have multiple foreign keys in a
table i have quantity that is of integer
type and I will make a primary key of
both product number and order ID as a
constraint so a combination of product
number and order IV would be giving my
primary key so as you can see that this
is successfully created now an order
item could not be created until a unless
an order is created and it could not be
created underline less we have a product
available so you could just check it
once again like suppose if I have two
deflesh this now you can see I have
product that is one soap and to hello
next I have created orders order I
create another order that is suppose
order ID is one shipping address is 10
second suppose if already is 2 and
shipping address is the Kolkata so now I
have two orders and I have two products
and you would see here that if I enter a
product number that is 1 that is for
sure okay and you would enter an order
ID that is 1 and you would enter the
quantity that means that
order ID is one that is user number one
entered for you they demanded for probe
number one of quantity to now suppose
order number is again one and product
number is two and that is three now you
would see that order ID here is one that
is for one user and he entered both the
products now and you could see that it
creates a cart now now suppose if I have
a product number that is 3 and order
number that is 3 we will see that was an
error that foreign key is violated so in
the next tutorial we'll be making an
even more stable version and this one so
stay tuned and subscribe for more