c# (Csharp) and SQL interview question :- What is the difference between unique key and primary key?

c# (Csharp) and SQL interview question :- What is the difference between unique key and primary key?


Now this is the very simple SQL Server Interview Question what is the difference between Unique and Primary key Now many developer end of saying that both of these things are one on the same and only the vocabularies difference etc etc in actual sense both of these things are different entities and they have their own important depending on condition so first lets try to understand what of the important differences between the Unique and primary key there are three important points which the interviewer is aspecting from you first Unique keys can have nulls but primary keys can not have nulls. you can create multiple unique keys on a table but you can have only one primary key on a table and the third important point is when you create a unique key it create a nonclustered index by default and when you create primary key it creates a clustered index by default lets try to demonstrate these three point one by one step by step and let see that if our answer is right or wrong so lets start with the first point unique key can have nulls but primary keys can not have nulls so here the simple table i have called as tbl_company let me first go and try to create a simple primary key on this column and let me try to see that if it allows nulls so let me just click on this check box called as allow nulls and you can see that basically i am getting error you saying that we can not set a null property on a column which is having a primary keys so nulls property can not be set on a column which is part of the primary key now lets try to create a unique column and let see that if you can click on the allow Nulls check box so in order to create a unique column you are go to indexes and keys you to add a key and you to select that the type of a key is unique key lets give a name to this unique 1and close this and let me save this so you can see that basically currently i have a column which is the company code column is unique and the unique key constraint is the pride on a company code column let me try to go and click on the allow nulls check box and let me try to save this so you can see that basically i am able to click on allow nulls check box on column which is a unique key now lets talk about the second point we can create multiple unique keys on a table but we can have only one primary key on a table so lets try to demonstrate if this is right or wrong so i have again taken the same table so let me go and set this is primary key so i have set the id column is primary key now let me go to the company code column let me set that as a primary key now if i try to set this as a primary key you can see that the first primary keys goes on from here so in other words you can set only one primary key in a table now lets try to go and create a unique key so in order to create a unique key again let’s go and click on indexes and keys and let say add and lets give a name to the saying unique 1 and let say that this is unique key so unique key so we have creating the first unique key on company code column now let’s go and create one more unique key called as unique key 2 and this time we will select company id so we have unique 1 and unique 2 let me close this and let me try to save this and let see if it box so you can see that in this table i have two unique keys one is on the company code column and the other is on the id column so coming back to point number two we can create multiple unique keys on a table but we can have only one primary key on a table now lets move ahead to the third point unique key creates a nonclustered index by default and primary key create a clustered index by default lets try to see a demonstration of the third point so as per the third point when ever we create a primary key so when we set this as a primary key by default what happens it create a clustered index internally so you can see that basically this primary key has created a default clustered index on the id column and when we try to create a simple unique key so for example if i try to go and create this as a unique key we can see that it has selected by default the con clustered index in other words it’s not a clustered index so in other words just to confirm what i will do is let me go and remove the primary key so i don’t have any clustered index let me go again my indexes an keys delete this add a new unique key lets give a name to this and let me try to say unique so you can see that it has selected by default the index as a non clustered index so that s the third point unique key creates a nonclustered index by by default while the primary key creates a clustered index by default so in order to answer this question completely right you to talk about three point 1st one is unique key can have nulls but primary keys can not have nulls second is unique keys it can be created multiple columns in other words one table can have multiple unique keys while you can only one primary key on a table and the final one is unique key creates a nonclustered index by default while the primary key creates a clustered index by default in interviews you can always have twist and turns so one of the twist and turns for this question is basically can be reference can a foreign key reference a unique key yes both unique keys and primary keys can be reference by foreign key tables this question was related to the SQL server topic and in the same way you can have other basic question which can pop up like for example some basic questions on normalization denormalization of database design on SQL syntaxes that inner joins left joins right joins group by union union all those kind of typical SQL Syntaxes and must be some question on performance tuning etc so what you would suggest is to login into your questpondvd account and have a look at the performance tuning as well as the SQL basic videos so that basically any kind twist and turn question you get right you can answer them properly.

45 thoughts on “c# (Csharp) and SQL interview question :- What is the difference between unique key and primary key?

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *