Creating and working with tables - Part 3

Oh welcome to presume technologies I am venket this is part three of signal server in part one of this video series we have seen how to connect to sequel server using sequence of a management studio and in part two we have seen how to create alter and drop databases in this session we'll learn how to create tables and enforce primary and foreign key constraints between those tables the aim of this session is basically to create these two tables TBL person and TBL agenda now within this TBL person table I want to mark this ID column as the primary key column and in TBL gender I want this column to be marked as a primary key column now what is the use of primary key a primary key basically is used to identify uniquely each record in that table for example in this TBL person table if I have two Mary's how do I uniquely identify each one of them using this primary key column so if there is another record for Mary probably she will have an ID of seven so two is for this Mary seven is for another Mary that we are going to have okay so primary key is basically used to uniquely identify each record within that table alright to create a table in sequel server we have got two options one is graphically using single server management studio and the other one is basically to write a query now first we will see how to create a table graphically using sequence of a management studio so let's flip back so now within the databases in the previous session we have seen how to create a sample database so if I expand the sample database you see there's a folder called tables folder and if I expand that we don't have any tables yet because we haven't created any now if I want to create a new table I can right click this folder and say new table and we want to create a TBL person table with these columns ID name email and gender ID so let's create those so this is the table design of vendor so what's the column name that we want we want the column name s ID and the data type now if you look at the ID the number you know it's a number it's an integer so I want the data type to be integer and thus should this column allow nulls no it shouldn't because I want this column to uniquely identify each row within this table so I don't want to 11 odds so remove the checkbox and I want this column to be the primary key column of this table and how do I do that right-click on that column and select set primary key okay so what have you done we have created this ID column we have specified the data type and we said it's not going to allow nulls and we mark this column as a primary key column and what's the next column in our table name column okay and the data type of this one is going to be n we're care and we also have to specify the length the number of characters let's say I want 50 characters and do you want to allowed nulls no every person in my table needs to have a name so remove the checkbox and the next column we have is email and again this is going to be and we're care fifty characters in length and I don't want to allow nulls and the final column that we want is the gender ID okay so gender ID is going to be an integer and I can allow nulls for example I don't know the gender of you know a few people in that case their gender ID will be now okay so I want to create this gender ID column so gender ID and I want this to be integer and I want to allow nulls that's it we are done now right click here and say save Table one and I want to call this table as TBL person table so basically what we have done now we have used the designer and created this TBL person table so you look at this this is the TBL person table and if you expand that and if you expand the columns folder you should see all the columns ID name email gender ID and if you look at this ID column has a key symbol next to that indicating that this is the primary key there is also the sub related form PK primary key it's an integer data type and it doesn't allow nulls on the other hand if you look at gender ID it's an integer column and it does allow nulls all right so we have just seen how to create a table you know graphically using the designer now let us see how to create this TBL gender table using sequel query now in the previous session when we created a database you know we use the command create database database name now we want to create a table so we will say create table and the name of the table so create table and what's the table that I want to create TBL gender now this table is going to have how many columns two columns ID and gender columns and we want the ID to be integer column and we don't want to allow nulls and we want this column to be the primary key column of this table so how do we do that so I want the column as ID and the data type as integer and I don't want to allow nulls so not now and I want this column to be primary key so primary key so that's the first column your name of the column data type whether you want to allow nulls or not and primary key now the next column that I want to create is the gender column itself so gender and the data type of this column is going to be and we're care and I don't want to allow nulls so not now and this is not going to be a primary key and remember a table can have only one primary key and now execute this command so command completed successfully now if you come here and expand refresh the tables folder you should see TBL gender table now interestingly you don't see the TBL gender table why because if you look at the database context here you have created that table I mean you have selected here the master table so when you execute it this query it got executed in the context of the master database and what's a master database it's the system database so if you come here expand this you should see TBL gender here but that's not our intention we want this table to be created in sample database so let's get rid of that so that's why we need to be extremely cautious when executing a query are we executing that against the database that we want it to be created or another database so let's let's get rid of this table so delete click ok so the table is gone now to create this table within sample database you can select the database here or what you can do is in the script here you can say use sample database and look at that sample you know word isn't blue color meaning it's like it's used like a key word so if you want to specify that as the name of the database put it in a square brackets like this and then click go so we are saying use the sample database go now for example if I am in the master database if I execute this just these two statements what's going to happen look at this the database is automatically changed and now if I execute this what's going to happen this table will get created in the sample database so now refresh and you should see TBL gender table here and if I expand that and if I see the columns there you should see ID is the primary key column it's an integer data type and doesn't allow nulls but as gender is an Weka 50 and it doesn't allow nas as well so what have we done until now until now we have created these two tables and mark these two columns as primary keys for these two tables okay now what else we want to do we we know that this gender ID column you know we want to mark this gender ID column as the foreign key in this table so how do we do that again you can do that graphically or you can do that using a query first wheels you have to do that graphically and then we'll use a query now if you what is the advantage of foreign key basically let's talk about that practically first let's create this foreign key relationship and before that let's enter some data into these tables now in the we will see how to enter data using queries but for now we will use the designer basically to enter data and to do that right click on the table and click on edit top 200 drills before we do that for TBL person let's do it for TBL gender right click on the table and select edit top 200 rows okay now let's say I want to enter 1 male 2 is female let's say okay a simple sample data that I have okay so one is male 2 is female now let's do it for TBL person edit top 200 rows now what do we have here the ID of the person name let's say John his email let's say j @ j calm and his gender ID let's say 1 okay now this is fine so John's gender ID is 1 so what is John's gender he is a male now on the other hand let's say I enter another person maybe Mary and M at m.com but here I am saying gender ID is 99 okay now look at this that value is accepted by the sequel server now if somebody asks ok can you tell me what is made his gender now you're looking okay Mary's gender ID is 99 and you go to the genders table and you don't have a 99 here so what is Mary's gender you don't know ok so basically here the data that you have in the person table is not correct ok somebody made me by mistake they have entered this 99 and sequel server accepted that value so if you look at this data you know the database integrity is lost your data is not intact you know there's something wrong with this data ok but on the other hand if we have established the relationship between these two tables the foreign key relationship it wouldn't have allowed you to enter 99 here but on the other hand it would have allowed now ok so if you an internal okay what is Mary's gender it's null which means we don't know they haven't provided their gender information but if somebody is able to enter maybe 99 or 1 zero-one like this this data doesn't make any sense why because jenthor ID 1:01 does not exist in the gender stable and we don't know what is Mary's gender so the database integrity is lost so how do we enforce the database integrity using this foreign key relationship foreign key constraints so let us see how to add if one key constraint so now let's enter this as null for the time being let's close this table let's close this table as well okay now what do we want to do in TBL person table we want to mark this column as a foreign key constraint okay let's do that graphically so here TBL person is the foreign key I mean foreign key table this table contains the foreign key and this foreign key is looking up for values in TBL gender table so this primary key you know the primary key of TBL gender is acting as a foreign key in TBL person table so primary key table is TBL gender and foreign key table is TBL person and in TBL person gender ID column looks up its values in TBL gender table in this ID column okay for example if I want Martin's gender okay Martin's gender ID is one so you take this value go into TBL gender look in this column one is male okay so this is your primary key table TBL gender and your foreign key table is TBL person okay now let's flip back to signal server management studio so in the TBL person table i want this column gender ID to be marked as a foreign key how do i do that right click on the table and select design and within the designer window of this table this is the gender ID column that we want to mark as a foreign key so right click on that and select relationships so this shows up a foreign key relationships window click here add now you should see tables and column specification here click there and you should see an ellipsis button click that button this is where you will have to specify which is the primary key table which is the foreign key table and we know that the primary key table is the TBL gender table in TBL gender table I want to look up the ID column and the foreign key table is TBL person table and with an TBL person table gender ID is the foreign key column so click OK now if you say clues and save the table what's going to happen this gender ID will be marked as a foreign key constraint okay but let's do that using a query rather than using the designer okay let's not save this okay if I want to create a foreign key constraint using a query rather than the designer how do we do that there is a general formula for that so alter table and foreign key table look at this alter table which table we want to alter TBL person table so let's try that so alter table TBL person that's the table we want to alter why because in this table I want to mark gender ID column as the foreign key constraint as a foreign key column so alter table TBL person table and what do I want to do I want to add a constraint what type of constraint foreign key constraint add constraint and then give it a name so add constraint and give it a name what is the name of this constraint you know I'm creating this one key constraint on this TBL person table so one TBL person on which column am i creating that in on gender ID column so gender ID and this is going to be a foreign key constraint so that's the name of the constraint and next what we need to do we need to specify which column in this table and that column you know which table which other table is it going to reference and which column in that other table so if we look back so foreign key so foreign key and we need to specify you know which column in TBL person table is going to be the foreign key in the steel person table gender I d so we specify gender ID is the column that we want to mark as a foreign key but then if this is a foreign key then it has to be a primary key of some of the table so which is that other table in which we have to look up the primary key column so in our case it's going to be the TBL gender table so this column is going to reference TBL gender table and the ID column so references which table TBL gender and which column in that table ID column so if you look at that primary key column alright so if I execute this query what's going to happen it's going to create a one key constraint with this name so execute that so command completed successfully now if I come to the TBL person table refresh that and if I expand the TBL person and if I go into the key stable look at that there is this TBL person gender ID FK created this this constraint basically created foreign key constraint is created and if you right-click on that and if you say modify you should see exactly the same thing that we have specified here if you come here tables and column specifications click the ellipsis button you should see that the primary key table is TBL gender and the column is ID and the foreign key table is TBL person gender ID so you can do that either graphically or using the designer now do I have to remember this you know the syntax of adding a foreign key constraint on top of my head I would say not really but you'll have to understand the concept that's what is important in your interview nobody will ask you how to you know what is the syntax for adding a foreign key constraint but they would generally ask you can you explain the concept of foreign key constraint and why do we use foreign key constraints that's important to understand the syntax you can google that within two minutes if you search Google you will get tons of articles explaining you the syntax so basically what we have done until now is we have created these two tables mark the ID columns as pry for both the tables and we marked gender ID in TBL person as a foreign key column now since we have marked this column as a foreign key column now this column only allow values if they exist in TBL gender otherwise the value doesn't make sense for example for Rob if I enter 99 what is Rob's gender it doesn't make sense okay so it checks that value when you try to insert or update that and if that value does not exist in the primary key table it's going to flag that as an error and that it doesn't allow you to enter let's see that in action for example let's go to this TBL gender table and let's say edit top 200 rows and if you look at gender table we only have one and two male and female and let's go to TBL person table and I did top 200 rows there and let's say I want to change Mary's gender from null to maybe 99 okay and the moment I click away look at this no rows updated the data in Row two was not committed committed meaning not saved okay so what's the error there is a foreign key constraint violation okay so because this 99 does not make sense okay so let's Center now because why in general table 99 does not exist so now your data is being verified because of this foreign key constraint if somebody tries to enter invalid data accidentally or intentionally you know sequencer is not going to save the data and instead it throws an error so now we are able to maintain database integrity the data now is going to be a valid so foreign key constraint so what's a funky constraint basically we are using foreign key constraint to enforce database integrity in layman's terms a foreign key in one table points to a primary key in another table and the foreign key constraint prevents invalid data from being entered or you know inserted or updated because the values that you enter here should be one of those values present in the primary key column okay otherwise you get an error all right so on this slide you can find resources faced with our tendency shop interview questions that's it for today thank you for listening have a great day

Channel: kudvenkat

Views: 1,328,229

Rating: 4.8723402 out of 5

Keywords: Creating tables, Enforcing, primary key, foreign key, constraints, alter table add foreign key sql server, foreign key example in sql server, add foreign key to existing table in sql server, foreign key constraint in sql example, sql server create table, sql server create table with primary key, sql server create table with foreign key, primary key foreign key example in sql server, primary key column in sql server, alter table add foreign key constraint sql server

Id: JLeaM8pK8dE

Length: 20min 7sec (1207 seconds)

Published: Sat Aug 04 2012