What are different types of Keys? Please explain all the
keys with a suitable example.

Answers were Sorted based on User's Feedback



What are different types of Keys? Please explain all the keys with a suitable example...

Answer / abdul malik(new college)

keys-A key allows us to identify a set of attributes and
thus distinguishes entities from each other.keys also help
uniquely identify relationships,and thus distinguishes
relation with each other.
different types of keys:
(i)super key
(ii)candidate key
(iii)primary key
(iv)unique key
(v)foreign key
sk-it is a set of one or more attributes that allows us to
identify uniquely an entity set.
ck-a super key for which no subset is a superkey is called
a candidate key.
pk-In a table pk does not take no repetation and does not
allow null values.
fk-it acts as a referential order.it refers one table from
the another one.

Is This Answer Correct ?    53 Yes 9 No

What are different types of Keys? Please explain all the keys with a suitable example...

Answer / vinodh

Introduction

Not long ago, I had an interesting and extended debate with
one of my friends regarding which column should be primary
key in a table. The debate instigated an in-depth discussion
about candidate keys and primary keys. My present article
revolves around the two types of keys.

Let us first try to grasp the definition of the two keys.

Candidate Key - A Candidate Key can be any column or a
combination of columns that can qualify as unique key in
database. There can be multiple Candidate Keys in one table.
Each Candidate Key can qualify as Primary Key.

Primary Key - A Primary Key is a column or a combination of
columns that uniquely identify a record. Only one Candidate
Key can be Primary Key.

One needs to be very careful in selecting the Primary Key as
an incorrect selection can adversely impact the database
architect and future normalization. For a Candidate Key to
qualify as a Primary Key, it should be Non-NULL and unique
in any domain. I have observed quite often that Primary Keys
are seldom changed. I would like to have your feedback on
not changing a Primary Key.
An Example to Understand Keys

Let us look at an example where we have multiple Candidate
Keys, from which we will select an appropriate Primary Key.

Given below is an example of a table having three columns
that can qualify as single column Candidate Key, and on
combining more than one column the number of possible
Candidate Keys touches seven. A point to remember here is
that only one column can be selected as Primary Key. The
decision of Primary Key selection from possible combinations
of Candidate Key is often very perplexing but very imperative!

On running the following script it will always give 504 rows
in all the options. This proves that they are all unique in
database and meet the criteria of a Primary Key.

Run the following script to verify if all the tables have
unique values or not.
view sourceprint?
01.USE AdventureWorks
02.GO
03.SELECT *
04.FROM Production.Product
05.GO
06.SELECT DISTINCT ProductID
07.FROM Production.Product
08.GO
09.SELECT DISTINCT Name
10.FROM Production.Product
11.GO
12.SELECT DISTINCT ProductNumber
13.FROM Production.Product
14.GO

All of the above queries will return the same number of
records; hence, they all qualify as Candidate Keys. In other
words, they are the candidates for Primary Key. There are
few points to consider while turning any Candidate Key into
a Primary Key.
Select a key that does not contain NULL

It may be possible that there are Candidate Keys that
presently do not contain value (not null) but technically
they can contain null. In this case, they will not qualify
for Primary Key. In the following table structure, we can
see that even though column [name] does not have any NULL
value it does not qualify as it has the potential to contain
NULL value in future.
view sourceprint?
1.CREATE TABLE [Production].[Product](
2.[ProductID] [int] IDENTITY(1,1) NOT NULL,
3.[Name] [dbo].[Name] NULL,
4.[ProductNumber] [nvarchar](25) NOT NULL,
5.[Manufacturer] [nvarchar](25) NOT NULL
6.)
Select a key that is unique and does not repeat

It may be possible that Candidate Keys that are unique at
this moment may contain duplicate value. These kinds of
Candidate Keys do not qualify for Primary Key. Let us
understand this scenario by looking into the example given
above. It is absolutely possible that two Manufacturers can
create products with the same name; the resulting name will
be a duplicate and only the name of the Manufacturer will
differ in the table. This disqualifies Name in the table to
be a Primary Key.
Make sure that Primary Key does not keep changing

This is not a hard and fast rule but rather a general
recommendation: Primary Key values should not keep changing.
It is quite convenient for a database if Primary Key is
static. Primary Keys are referenced in numerous places in
the database, from Index to Foreign Keys. If they keep
changing then they can adversely affect database integrity,
data statistics as well as internal of Indexes.
Selection of Primary Key

Let us examine our case by applying the above three rules to
the table and decide on the appropriate candidate for
Primary Key. Name can contain NULL so it disqualifies as per
Rule 1 and Rule 2. Product Number can be duplicated for
different Manufacturers so it disqualifies as per Rule 2.
ProductID is Identity and Identity column cannot be
modified. So, in this case ProductID qualifies as Primary Key.

Please note that many database experts suggest that it is
not a good practice to make Identity Column as Primary Key.
The reason behind this suggestion is that many times
Identity Column that has been assigned as Primary Key does
not play any role in database. There is no use of this
Primary Key in both application and in T-SQL. Besides, this
Primary Key may not be used in Joins. It is a known fact
that when there is JOIN on Primary Key or when Primary Key
is used in the WHERE condition it usually gives better
performance than non primary key columns. This argument is
absolutely valid and one must make sure not to use such
Identity Column. However, our example presents a different
case. Here, although ProductID is Identity Column it
uniquely defines the row and the same column will be used as
foreign key in other tables. If a key is used in any other
table as foreign key it is likely that it will be used in joins.
Quick Note on Other Kinds of Keys

The above paragraph evokes another question - what is a
foreign key? A foreign key in a database table is a key from
another table that refers to the primary key in the table
being used. A primary key can be referred by multiple
foreign keys from other tables. It is not required for a
primary key to be the reference of any foreign keys. The
interesting part is that a foreign key can refer back to the
same table but to a different column. This kind of foreign
key is known as "self-referencing foreign key".
Summary

A table can have multiple Candidate Keys that are unique as
single column or combined multiple columns to the table.
They are all candidates for Primary Key. Candidate keys that
follow all the three rules - 1) Not Null, 2) Unique Value in
Table and 3) Static - are the best candidates for Primary
Key. If there are multiple candidate keys that are
satisfying the criteria for Primary Key, the decision should
be made by experienced DBAs who should keep performance in mind.

Is This Answer Correct ?    15 Yes 5 No

What are different types of Keys? Please explain all the keys with a suitable example...

Answer / rishit v chandarana

there are main 5 types of key, namely

candidate key,
primary key,
alternative key,
secondary key &
foreign key i.e refrential key.

Ck- all keys in a table.
Pk- selected key in a table.
Al- not selected as a pk key in a table.
Sk- not selected at all.
Rk- key showing refrence at other table.

Is This Answer Correct ?    6 Yes 4 No

What are different types of Keys? Please explain all the keys with a suitable example...

Answer / avishek patra

There are mainly two types of keys are available in SQL server
1. PK
2. FK

1.PK-- In a Table the unique key(with no repetation and does
nota take null value) is called Primary Key
2.FK-- Foreign Key when that PK refers to another table then
its called as foreign Key

Is This Answer Correct ?    39 Yes 42 No

Post New Answer

More SQL Server Interview Questions

What part does database design plays a role in performance of an sql server-based application?

0 Answers  


Tell me what do you mean by an execution plan? Why is it used? How would you view it?

0 Answers  


If I delete a template from the list in sql studio, will it be deleted from the hard disk? : sql server management studio

0 Answers  


Advantages and disadvantages of stored procedures.

2 Answers   HCL,


How to list all field names in the result set using mssql_field_name()?

0 Answers  






What is the difference between Procedure and Function

4 Answers   Accenture, Nagarro, TCS,


how do u do Performance tunning ?

1 Answers   Infodat Technologies, Satyam,


write down the sql query? Table Name : emp1 empid empname 1 bala 2 guna Table Name : emp2 empid empname 1 <Null> 2 <Null> Solution : emp1 names are updated in emp2, write a query?

8 Answers  


diffrence between function and procedure

5 Answers   Wipro,


If a user does not have permission to a table, but has permission to a view created on it, will he be able to view the data in table?

0 Answers  


What is the difference between in and exists. Ex: select * from emp where empno in(....) and select * from emp where empno exists(....) What is the difference between a Join and Union and Union and UnionAll.

5 Answers   ABC, Accenture,


Explain cdc and sql injection?

0 Answers  


Categories