Working with Primary & Foreign key Constraint in SQL Server | SQL Server Tutorial

hi guys welcome to nari shotty this is rocker and today we are going to discuss the very important topic in database so that is to make the relationship in between the tables so whenever you want to make the relationship in between the cables I I just need the two keywords okay in your con strings that is one of the primary key and foreign key so now with the help of this two key words how we can make or how we can establish the relationship in between tables so before I want to establish the relationship in between two tables then what kind of conditions I want to follow in my tables and how I can make the relationship after making the relationship okay how I can go to to take the reference data from one table to another table okay so that mechanism we will go to discuss in this session here and now so now first I will go to so working with working with with the primary key and foreign key primary key and foreign key constraints okay so now we can see this foreign key constraint and this is okay so let us see now how you can make how we can work with primary and foreign key constraints okay on your tables okay so basically these two constraints key words we are going to using whenever you want to make the relationship especially the foreign key whatever I am going to use your foreign key the foreign key we are using the main reason is what now to make okay to make the relation between M between two are more than two tables so whenever you want to go for to make the relation between two or more than two tables then you can use your foreign key constraint along with the primary key okay the two constraints I am going to take here to make the relation in between the tables so now but here when you want to make the relationship in between these two tables by using primary key and foreign key okay I need to follow some conditions okay so conditions I want to follow before you want to establish the relationship between the tables okay so what kind of conditions I want to follow okay so now we will discuss now in this my first condition what it is here okay one table should contain on table should should contain contains primary key and another table and other table okay so another table contain another table content contains okay foreign key this is my first so before i want to establish the relationship in between two tables what i want to do now so one table should contains primary key and another table should contains foreign key okay so primary key foreign key first of all you want to need to keep one primary key constraint in one table and foreign key constraint in another table okay so now this is two key words i required now and the second one what is my second one here when you want to make the relation in between two table i need a common column okay i need a common column okay so in in both tables okay this is second requirement but third requirement what it is here whatever the common column i was taken here this common column data type should be same okay so i want to say the common column the common column data type data type must be must be same in both the tables okay so these are the three conditions you should be remember when you want to make the relationship in between tables one is one should contain primarykey and one table should contain foreign key second one the table should have one common column okay and third one the common column datatypes must turn should be same within both tables so based on these three conditions now I am going to create my relationship in between tables now okay so let us see now how we can create the relationship in between primary key and a foreign key so now I will go to open my SQL Server management studio and later as usual you want to go to connect your server and later you just take the new query editor and now select your required database my class and as of now in my class database I have a the tables was there so my table co okay so EMP Chennai okay EMP Chennai and he MP either about an extra student these are the three tables are there at present in my database so now I am going to create here my another example table so I am going to take here to perform primary and foreign key relations okay so here I will be follow the step by step I will be followed because these steps once you can see when you are going to practice at the time the steps you will be follow one by one then you can easily understand what it is happened okay so now first of all first I will create one table so creating table my table name for example I want to say Department is the one table I am creating now so in Dept table so I will take your DEP T number Department number in teaser but here should be take what I said one table should take a primary key so primary key I am taking now later I will take your Department name and I am taking your work here offers some size and the same pattern I will take your location okay so location was taken here this is also very care of some 40 and this is what now one table I am going to create now so now Isis created after created your table one point should be remember on which table the primary key was applied this table will be treated as a parent table okay and after that after design your parent table along with your primary key constraint then later I want to insert some records so insert I will go for Department and I am sending values of okay now one by one value I'm sending for example 10 my department name for example d/b/a and the location I will take your Hyderabad hyd like this one more record I'm inserting multiple records 20 and later I will take SAP okay the location I will take your Mumbai and in the same pattern one more record I am going to take now that is called a 30 and the department is HR and I will take the location is Pune so now these are the three records I am going to inserting into my parenting table now so I was inserted the parent table data successfully after that after that if you after inserted then you just call your parents able then my parent table successfully designed or not if you want to check then you just call you at our interval like this after calling your then you can see whatever the parent table I was designed here the parent table data was also successfully inserted okay and later on what I am doing here the next I will go to create a my second table okay so second abel is nothing but i simply say my child table because parent table already created now i want to go for child table okay so now child table creation purpose I will take nice step number three okay so what is my step number three Here I am going to create create table my table name is for example employee and the employee ID it was in teaser okay and next one I will take employee name okay to take your worker off some sighs okay and later for example employee salary and money but here what I said your before you want to make the relationship okay one common column should be maintained but which column I want to take as a common column means okay in your parent a table on which column are you applied the primary key constraint that column should be taken common column so thus de PT number this column I am taking as a common column in my both tables because in my parent a table on which column do you applied primary key constraint that column should take as a common column okay then only the relation can be established so by this reason in mice child table creation now I will take common column de PT number so common column name I was taken later the common column data type must on should be in same so there which data type I was taken at the time of design in Deezer same to same I want to take here in design later on I will be apply my foreign key so I am going to apply a foreign key references references from which table are you taking references your department table from department table of which column de PT number column my point is clear now so now here the one more table I was created and common column de PT number common column on that common column I applied foreign key reference and the foreign key reference are you taking from parent the table of Department of Department number okay so once you want to just you applied like this and go to execute now once I will execute it here okay once I will execute it here then you can see the two tables I was designed in my database so now check out here my first table Department I just open and open your columns folder then you can see at your primary key was applied so primary key was there the key was there in yellow color marker okay this is my primary key representation so primary key was applied here and primary key not Nell you know primary key will not accept a null values and if you see the key key was generated here primary key okay so primary key was generated this was this ID system okay this is what now Dept table primary key and later I will create a one more table that employee if I want to expand here then column you can see the foreign key so foreign key was created now okay this is my foreign key symbol okay and now fergie was applied here okay and later on if you want to see the key folder then you can see foreign key ID also generated by your system okay so there is primary key foreign key the both okay the both constraints applied in two tables so that means what I said in previously on which table are you applied primary key that I was treated as a parent I said in the same to same on which table you applied foreign key this table will treat as a child table okay so now the relation was established so but relation is established or not if you want to check then you can see how the relation is established now even I am going to testing them so how to test your first of all I will take here my tables select star from so employ employee and the first parent table I am going to take now this is my parent table so Department is parent employee is a child now I will go to open so parented table okay the reference column is what nan de PT number in this column which values was there 10 20 30 those values only accessing by your child table that means the 10-20-30 can only support connect can only accept in your child table okay but ten twenty thirty other than these three values if you inserted any other value in my childhood able that was not accepted if it is not accepted then you can assume like that my relation was established okay simple logic I am Telling You so why because you know every child was having all rights on parents also now so that means the child was have a chance to take the references from parents as are no same to Samir also Department is a parent cable and child of is a employee so that means this employee table can take reference data from parent a table but which data I want to take as a reference means on which column are you applied primary key that column value so only reference values to take in child cable okay but other than this if any other value user try to insert into child table then the value will not accept it that means for example 10 20 30 these are called a reference values why we call it is a reference values means these values are available in my parent a table so that is why these are references so in pairing the table which values are there those values so only child a will accepted but other than these values child will never accept another values okay so now that was a testing now I am going to test now so now later after design your relation with the primary and foreign key now I am going to testing my quarry insert my child table employee I am sending values of okay for example employ t101 employee name was Adams employ Sally was 56 thousand and Department number it is asking so when I give my department name of 10 but 10 is available in my parent a table so that means that 10 number is a reference number okay so that is why this record is accepted into my child table successfully there is nothing the problem now we can see the child table was accepted this record or not now see so whatever the 10 was there in parent a table the same 10 when user inserted in child table that was accepted because this 10 was available in my parent table reference column so that means the 10 is called a reference value that is why it was accepted in the same pattern next I will go for the another query on good writing now insert employee values of now this m102 I will send here James and the 45000 of salary next 20 20 can accept now yes 20 is also accepted now now we can see I am going to select this query and to execute airdam again it was accepted so why it is accepted means you know the 20 value was there in my parent table now so that is why it was a accepted norm okay so 20 was there again 20 is also reference value okay so that is why your child is accepted there is what one more value is there 30 in cutting also I just try here or this another record I am in sitting now insert employee sending values of 1 0 3 comma next I will send here Scott and the 23 thousand rupees and 30 so this time my third record is also I am going to insert but it is accepted or not you can check now to execute it now once executed again this row also accepted once it is accepted here then you can come and check your child table and parent table then you can see in parent a table reference column whatever the use are there the same values we was taken in child table reference column also so now can you see this if you want to careful observed so now this is parented table ten was there so that child is accepted second one twenty was there in my parent table so that child was also accepted in the same the same the thirty was there in the parent table so that childhood is also accepted so it means now you can see the clear okay the relation will be established so power in table ten twenty thirty same ten twenty thirty was accepted in child but if you check like this another quarry I am going to in sitting now next time I will insert the same insert employee values of one zero four then the name I was given he or Miller and the salary was sixty-two thousand rupees then I inserted forty but can you see your the forty number is there in my parent table no okay the parent table it is not there so the forty value is not there in parent table means the forty is called a unreferenced value okay so unreferenced value is nothing but the value which is not available in parent a table primary key column so if that unreferenced value are you tried to inserting into your table which table child table then what is happen you can see I am going to execute it it will giving compliation error so now we can see the compilation error what exactly it was given by server so the insert statement complicated with foreign key con is friend FK employed de PT number and some ID the conflict occurred in the database my class Department and dep d number so you will try and try and try your unreferenced values in child table try and try and try but it is not accepted into I'll table why because this 40 value is not there in my parent table simple logical so that means so parent table and child table now I will go to opera and theater now you can see so 10 is accepted 20 is accepted 30 is accepted but when use ascending 40 that was not accepted in my child table because the 40 was not there in my parent table reference column leap it in number so by seeing this simple logic then you can understand that the parent and child relation how it was established okay so now by seeing this example you will be confirmed like this year the relation was established in this two tables that is the reason 40 is not accepted if variation was not there then 40 I can insert not only 40 you can insert any number any value in your child table okay but when the relation was there then child will accept it only parent the table values okay so that is why 10 is accepted from these acceptor that is accepted but 40 when you inserted it is not accepted in child this is what it is here the simple way to make the relation in between the tables by using primary key and foreign key okay so now after relation was established now you can come to check your condition whether we followed or not so what I said your the working with primary form means the foreign key establishment especially we are used for relationship purpose the relation was established and later what is said conditions are given one table should contain primary key and one table should be foreign so we applied primary key foreign key we applied on your table so first condition we satisfied you can see here apply primary key in one table foreign key in secondary so first condition was satisfied second condition what it is here common column should be maintained so I will maintain common columnar so what is our common column Department number okay a DEP D number and also the EPD number common column and which one is a common column the common column data - must be same so now same data by was taken here my common column in teaser type and you are also my common column in these are type so that mean the three conditions are satisfied okay and later finally what I said primary key table should be treat as a parent and the foreign key table should be treated as a child table okay so later after establishing your relationship and you want to check whether my relation was established or not then you can go to test this small query sir so I am going to testing now so what I given your first quarry I insert a 10 number in my child table employ this was accepted that I am King Singh here allowed second when I inserted it is also allowed third when I inserted it is also allowed and when I inserted 40 then what we observe here order this is not allowed so that means your relation was successfully done in between two tables okay so this is simple identification how the relation was established and whether it is established or not you want to check by taking this examples this testing examples once you can go to check the North America you can understand that okay whether my relation was established are not okay so this is the simple mechanism okay how to work with primary key and foreign key on tables okay so thank you guys thank you so much so and you will be watch more videos from nourish I do

Channel: Naresh i Technologies

Views: 199,296

Keywords: MSSQL, Sudhakar L, Naresh IT, Hands on MSSQL Training, Online MSSQL Training, MSSQL Tutorial Videos, MSSQL Overview, Learn MSSQL, MSSQL Interview Questions, Working with Primary & Foreign key Constraint, primary key in sql server, foreign key in sql, primary key and foreign key, sql server tutorial, Foreign key Constraint, sql server tutorial for experienced, sql server tutorial for beginners with examples, mysql tutorials

Id: e1WSVL4EIz4

Length: 24min 13sec (1453 seconds)

Published: Wed Nov 09 2016