Lab2 :- Primary key, foreign key, referential integrity and constraints

Shivprasad Koirala
A free video tutorial from Shivprasad Koirala
We love recording Step by Step tutorials
4.5 instructor rating • 15 courses • 62,010 students

Lecture description


2 :- Identity

3 :- Unique key

4 :- Candidates / Primary keys

5 :- Refe Integrity and FK

6 :- Database digrams 

Learn more from the full course

sql server tutorial

These step by step video lessons will teach you Sql Server step by step from scratch.

07:03:08 of on-demand video • Updated June 2021

  • SQL Server installation, creating entities like database , tables , rows and columns. Covers fundamentals like primary key,foreign key , referential integrity , constraints , normalization ( first , second and third normal forms), Denormalization,OLTP , OLAP, StarSchema and SnowFlake design.
  • Writing DML and DDL query like inner join,left join,right join, outer join, union , co-related queries, union all, alias,case statements,cartesian,ISNULL, aggregate( sum/count),wild card , top, order by, create , insert, bulk insert , update , backup , restore,ISNULL,Coalesce,Row_Number, Partition, Rank ,DenseRank , Pivot and Unpivot.
  • Understanding importance of indexes , how it makes our search faster , non-clustered indexes vs clustered indexes , page splits in indexes and column store indexes.
  • Triggers,Insteadof , After trigger ,Inserted , deleted tables ,Stored Procedures,CDC,8 KB page and SQL Server agent.
English [Auto] So Welcome to lab 2 and in lab 2 we'll be covering the following topics. The first one we'll talk about mouse. So what exactly are Nulls and how are they different from zero and empty. Then we'll talk about something called as an identity column. Then we'll discuss about unique these unique columns we'll discuss about candidate keys and primary keys right primary keys and we'll discuss about. Also like the referential integrity and foreign keys right and foreign keys. And finally we will also see something called as a database diagram know like well there's a small facility given in Haskell seven colors database diagrams by which you can go and you can create primary keys and foreign keys. So this will be approximately 25 minutes or 30 minutes video that will be covering these six topics. So let us go ahead and start so let us start with the first topic of lap two nulls right now. Let me go to my design here. Let me go and add one more failure card less customer I.D. So we already have customer code which is a new etiquette and we'll get means a string type. Now let me go and add one more failure customer I.D. which is a numeric type. So this field can only store numbers and no other value. I'll be covering the square. So what data types in a very separate lecture. Right. So for an hour just keep it as a number and I will save it. OK. So now let us click on edit BBM customer. Now let me put some detail. Let us see and we're going put 1 0 0 1 and shiv right and and customer I.D. 1 right. This customer I.T. field in this woman you can think about that. It represents the record right. It represents a record uniquely. It is a value by which I can pull up the record right. Once it is it or two and I say Raj All right. And I would say to you can see that Eskil server is actually displaying something called arsenal. So you can see that there is something called Somalia right. So what exactly is on a log. Think about that. I have a record year 1 0 0 3 and I've still not got the customer name right. So what I would like to do is I would like to put here something called another light a null represents the absence of a value. Right. If you see for example customer I.D. which is a numeric value can be zero but zero is still a value. Right. Customer name can be empty. This is still a value an empty string is a value empty empty string does not represent NULL NULL represents that there is an absence of data ok it represents nothing. OK. And many times what happens is you know you'd like to actually tell that you know this data is not present at this moment for example let's say somebody goes and gives you the customer gives you the name right. X Y Z. But you know you'd like to say okay the customer will come later on. Right. So you'd would like to go and see that okay. Put on a lower here instead of a null value on a column you can control zero. So if you press control zero it becomes a knowledge. Right. So a null represents absence of a data the next concept which I want to discuss here is something called less an identity column. Right. So you can see that when you select SQL Server column over here you can see that is something called an identity specification. So what exactly is identity specification or identity column when you go and mark a column as identity specification as yes. It means that this column will auto increment by itself. So you're not going to go and put a value but as equals so we'll we'll go and order increment is column by itself. That also means that identity column cannot be a string that means it cannot be anywhere a cat or cat. It has to be a numeric type. Right. So we can see that I have gone I have selected this customer I.D. and I've said that this is an identity column so I have said it here. Yes. And I'm seeing that increment by a number of one and start with the number one. Right. So if I want I can see it start from number 100. So it will become like hundred hundred and one. Right. So let me start by a number of hundred and increment by a value one. So let me go and see if this now unable to modify know because we have nulls right. So. Right. So let us go ahead here and I know you can't have a null here. Right. So let it let us put 0 0 0 here. OK. What it is saying is that there are nulls what to do with it right. So I would go and say that nulls out anymore let say control s right. Now already the column would have been filled. He will not do anything right. But no let us go and add a column here ones it isn't a full duster right so against you know it is a hundred and one. So I say ok once it is five or something. Hundred and two. Right. So you can see now this column is getting incremented by itself. So it has started from the seed one seed means the start number. And then it is increment incrementing by the value one. So I don't really see seed is nothing but it is a column which is auto incremented by a square service. And you do not have the right to go and change this column as well so you can go and see a I can type anything in this column. Right. So when you create an auto increment column Ask when someone decides how to go about it the next concept which I want to discuss here is a unique column or a unique key. I can see that what I've done is you know to this table I have added two more columns here to the customer phone number. And the customer as numbers. Right. So now customer phone number can be unique. Right. Customer SSN number can be unique customer code is unique customer I.D. is unique so you can see in this table at least I can see that there are four people you know who can be unique. Right. So in order to go and ensure that you do not have duplicate values for what I need to do is I need to go right click on the column I need to go to indexes and keys and I have to say. OK. This is customer code and customer code is a unique key. Right. And see this then I can see customer saying way I can go and say indexes and keys. Let me add one more I'll say OK we're going to give a nice name here. You can see and see that this is unique customer code and I'll say this is customer I.D. is again unique. Right. OK. So unique Saudi that changed us in the same way that I can say customer I.D. is unique. So again this is unique key. So let's give a proper name to the objects. Unique customer I.D. So I know that in the same way. Let me add one more like customer phone number of unique phone number right in the CMB. I can see SSN number is unique. So basically a unique column indicates that it cannot have duplicate values. So unique this isn't. So you can see now at this moment if you see a duplicate found I think one of them has a duplicate value. Let me just check. Yeah. You can see the customer phone number. They have a duplicate they have nulls at this moment right. So let me just go and make this that one delete all of these values for now. Because because of these duplicate values he cannot go and create the keys right. So I would see this right cleat. So now if you see at this moment we have four columns. We have four columns which are unique in all the phone number. The customer call the customer I.D. and says a number. So if he had this moment. So if I go here. So I if I put that 1 0 0 1 That's it. Dest This is auto increment in a phone number something SSN numbers something right. My if I tried to put a duplicate value let us say in the customer code 1 0 0 2. Right. Let us say I tried to do this. OK so if I try to pull this ones it is a one sorry I tried to put a duplicate value here. I'm sorry. We can see that he says you cannot insert a duplicate value right. So if I try to put a duplicate value here it says you can't do this. It says that I cannot insert a duplicate value because you know there is a constraint of a unique key. Right. So a unique key as the name says you know it cannot have duplicate values the other speciality or also the other feature of the unique is that you can have values which are null in unique keys. For example I can in a customer phone number say that OK I can have a null value. OK. So that is again a feature of the unique key. But remember that again you can only have one null value means. For example if I had tried to put one more null value take again complaints but said and then one of the records in the unique key can be NULL. So I can say this is can be NULL. Remember to put to put a null value control zeal control zero. OK. So if I try to put a null again it says no like customer code can have only one null value. But what I would like the unique key can have null values now as a technical person as a database administrator from these four unique keys I want to make a choice of one of the keys as a primary key. What is the primary key. A primary key is the key in which I as a technical person or do use it to locate a record right. So definitely in all for a good primary key. I want the following things the first one is that I don't want any kind of notes in a primary key. I don't want notes because my goal is to record is to look at a record. So I want a proper value right. Second does I don't want my primary keys to change now and then. For example let's say we create a primary. We see that look at this record is located about 1 0 0 1 right. And for some some reason you know we say that we get a custom on board is changing that after two years I come to know this record is now look at it by one's a digital five. If that is happening and what will happen is I will be building lot of technical things that sounded like I'll be building references I believe building indexes. So everything has to change right. So I want that this primary key cannot be changed right. So these four unique Keys at this moment we can thumb them as candidates the candidates you know who can compete to become a primary key. Right. So if you see for example a lot of the same customer phone number right. The customer the phone number can have nulls. So definitely I can choose this as a primary key. A customer can change his phone number right. So again if you can't change his phone number of this again does not qualify for the primary key. So if you see all of these unique keys what we have at this moment they are allowing nulls and they can change. Probably so because of what it was not as for example in India right. A customer or a person it was identified by something called as a panchayat. No. Right. But today we have something called us. I'll have a card number so again over here. The SSA numbers are changing right. So as a technical person I now cannot take any risk of choosing a primary key which is from the domain means which is given by the domain. Right. So so all these four keys if you look at customer I.D. this customer idea generated by Secret Service it is auto incremented nobody can go and change it also. So if I go and try to type something I cannot change it. And the final thing is that these values cannot be changed and these values cannot be null. Right. So from these four keys I find customer I.D. to be a suitable primary key. Right. So Primary Key cannot have nulls a primary key should not be allowed to change. Right. So let us go ahead and choose from these. Candy did these customer I.D. as a primary key. So I would one set it as a primary key save it. Right. So remember candidate keys are all primary keys and unique keys are candidate keys. If you ask if you tell if you if you want to just make it simple right. So candidate keys are unique but primary key is for the technical person is for the database administrator and for that they want that it should not have nulls and nobody can change it so that we don't have problems you know locating that regard and doing things. I don't get caught right in a real world any data on any data what we deal with is not standalone it has references for example. Let's say we have customer than customer can have multiple addresses so you can have a home address. He can have our office address and so on. Right. So for that you know we can go and create reference tables. So for example like you see over here I have this customer I.D. like 1 0 3 1 0 4 1 0 5. So now I have chosen this as a primary key so what I can do is I can go ahead and create a new table here cordless addresses so I will say address I.D.. So let me make this as a primary key. Now remember as a good practice you know you will always have a primary key. And as you said that primary key should not be editable so make it auto increment. Right. And I will say that looking for this address I.D. There will be a customer I.D. reference. Right. So I'll again make this as in digital and I will have address one. So address one I will have Citi and so on. Right. Right so this is Bill Press. So basically. So this is 1 0 3. So I can now go and say OK 1 0 3 1 0 3 has an address which is an office address which is in Mumbai right. And so on. Plate and then we can have again 1 0 3 can have one more address in. Which is in Nepal. And you know I can. So you know you can use the reference value of this table like 1 0 0 0 different values of this table like 1 0 4 and you can make an entry into this table. Right. Good good deal here. So now at this moment you know we are trying to create a relation between the customer table and the address table but now at the end of the day humans can make mistakes. Right. For example now let's say I go and make an entry here like 1000 right now in my customer table. I do not have any customer I leave the value 1000. So now you can see that we are ending up with bad data. What I would say improper data right. So in short you know the reference references between both of these tables now is leading to problems right. In short the correctness of this relationship between these two tables are doubted. Isn't it. This is termed as a referential integrity. So you know like a good database will ensure that there is proper referential integrity maintained between two tables. OK. So for that you know what we need to do is win to create one more key class of foreign key. The goal of foreign keys is that if two tables have relationships between them it ensures that that is data integrity. And I will say definition integrity maintained between two tables. Right. So let me go and delete this record out here. So in order to go and create a foreign key what you need to do is you need to go to the addresses table you need to go to the design here and over here you will right click and say that you want to go and create a relationship. So this relationship is between what and what. Right. So it seems that okay this is a foreign key yes. So that is a foreign key. Let me go and select on the sign out here. So now here he says Okay so which is a primary key table. So my primary key table is customer and the primary key on that is customer I.D. and which is the foreign key table. It is DB will address and address I.D.. Now one of the things which is important is you know when you actually define a relationship between two tables the database which establishes the relationship should be of the same view that I've been shot for example customer I.D. And if I tried to select let us address one I would get an exception. It says that you know like the data types of these properties do not match right. So ensure that the data types to match. Right. So I will go and see. Okay I will close this and I will save this. Remember it is very important to save in guess if you have not saved the table you will see this small star sign over here which indicates that your table is not saved. So a lot of times you know people who are newcomers to change the table but they forget to head to save button so remember that this indicates that whatever design changes you have made to the table has not been committed. Right. So let me go in it Sylvia. I would say yes so now if I tried to put a thousand year and this thousand does not exist in that table I would get an exception. So he can see now it is giving me exception you are saying that that is a conflict between the references. Right. So the goal of foreign key is to maintain that referential in dignity between two tables. Now in a project we can have lot of tables and to go and create references between these tables by using this menu over here is extremely difficult. We would like to have some kind of a visual look in Nevada begin to strike and drop and then see all the differences in one go and that's what exactly this small thing over here. We have something called s a database diagram so you can see that we have the database and then we have this small 10 year call as database diagram. So what it can do is you can see or can you database diagram and then you can go and select your table so we can say Add add. So once you do that you can now see a visual view of the reference. So you can see now this small low key or what he'll say is that the primary key is located in the tibial customer and this small sign your infinity or many signs says that the foreign key is located tibial address. So I'll go and delete this relationship right. So in case you want to go and create the relationship back right you will select this primary key and then drag and drop to this foreign key. You can see. So then I drag and drop this it actually opens up the same UI which you feel manually previously. Right. So I can go and press OK and then don't forget to say See if you can see that these are star signs. These top signs indicate that it has not been saved. And then it says save and save it right. So you know you know normally when you work at big projects you'd be creating such kind of a database diagram. So right click new database diagram choose your 2 0 tables. Right. What you want to between whom you want to create the relationship and then drag and drop and create your primary keys and foreign keys. Right. Also you can go and create your primary key from here. So you can right click and say setters primary key. You can also do the other things as well. Right. Good. So that brings us to this end of the session. So in this session we looked into nulls. We looked into identity columns. We looked into primary key candidate key foreign key unique key and we also saw how to use the database diagram. Thank you.