SQL tutorials 18: SQL Foreign Key Constraint By Manish Sharma

Key Takeaways

what's up guys I'm Manish from revellion rider.com and I'm back again with another SQL tutorial today's SQL tutorial is about foreign key in my previous video I have shown you what primary key is and how to define it if you haven't watched it then please find the link for the video on primary key in the description below okay let's check out the foreign key similar to primary key we can Define foreign key using either create table statement or Al table statement first we will see how to Define foreign key using Create table statement we can Define foreign key using Create table statement in two different ways first one is on column level and second one is on table level we will start with defining foreign key using Create table statement on column level we have to create two separate table that of parent and child table so our parent table is authors and child table is books our parent table author is just a simple table with two columns author ID and author name where author ID is a primary key column as I have already shown you different way of defining a primary key on table in my last video so I'm going to fast forward this if you haven't watched my primary key tutorial then I'm providing the link in the description below so please watch this video and now let's jump over SQL Developer let's create our parent table okay we have our parent table author we will be using author ID column of author table in our foreign key thus it's mandatory to Define this author ID column in parent table as either primary key or unique key please note that it's mandatory only the primary key column or the unique key column of parent table can participate in foreign key constraint avoiding this may result in error so we have our parent table author with a primary key column author ID now let's create the child table box child table is the core of foreign key constant let's create it we are defining foreign key on column level this is the main column over which we are going to put foreign key using author ID column from our parent table so this column must have same data type and size as of the author ID column of parent table author you can give whatever name you want to this column but as I said data type and size of this column must match with the parent table column author ID let me explain this to you in order to Define foreign key we first Define our column then we specify constant which is a keyword followed by user defined constant name and then references which is again a keyword and then name of parent table and the column of parent table in parenthesis which you want to use in foreign key so we have created our child table with the name of box now let's see the second way of defining foreign key using Create table on table level so for that we will be using our same parent table author for demonstrating I have to drop a child table box and recreate it okay let's drop our child table books for that just write drop table and the table name which is box execute there we go and now let's recreate our child table box just a second let me give a comment as I have already told you that we are going to put our foreign key on book author ID column the third column here so it must have the same data type and the size as of the column in our parent table which we are going to reference here which is our author ID column let's move ahead let me give some spaces so that you can read it clearly okay execute there we go as you can see I first Define all the column of the table and then at the end I Define my constant let's see the last statement of the table constant is the keyword followed by the user defined constant name and our constant foreign key which is again a keyword and then the child table column in the parenthesis over which you want to put foreign key and the keyword references followed by the name of parent table and the column of parent table in parenthesis that's it now let's see how to Define foreign key using alter table statement let's say we have a child table box with all three columns book ID book title and author book ID but we do not have any constant over the table yet so let's start by dropping our child table book and recreating it once again we have to execute this command now we don't have any child table P let's recreate it okay let me give some space so that you people can see it clearly remember we are yeah I was saying remember we are going to create a simple Book Table without any constant this means we are not going to emphasis or put any constant right now on this table execute that's it just a simple table without any constant now we have to put foreign key constant on third column author book ID oh sorry book author ID before emphasizing foreign key constant on the column make sure this column has same data type and size as that of column in parent table which we are going to reference in foreign key now let's alter the table for that just write this gdl command let's format it first okay let's me let me check alterable books add constant book foreign key just make it come here book author not od's [Music] ID same here references okay let's execute okay let's check whether we have this constant on our table box or not for that just go and click over the table name you can click here or here it doesn't matter just click over the table name and then press shift key and F4 key and SQL Developer will show you this Wizard and here just click constant and as you can see we have our foreign key constant B e a i f k and if you click here double click it sorry and you will get to know that this constant is on column position one and uh every detail about this constant you can get from here it means we have a foreign key constant on our table books here so guys that's it for today for more indepth knowledge on foreign key you can read this article just right here on my website you can find the link in the description below also please share this video tutorial with your friends and peer on your social networking if you haven't subscribed already please do so and get latest update on SQL tutorial in case of any Cy feel free to come and take care see you soon with another interesting SQL tutorial this is Manish bye-bye