Watch Our YouTube Channel
It is currently Tue Jan 23, 2018 1:55 am
 

All times are UTC



Post new topic Reply to topic  [ 1 post ] 
Author Message
PostPosted: Tue Mar 29, 2016 11:03 am 
Offline

Joined: Fri Nov 13, 2015 3:56 am
Posts: 460
To maintain data integrity we use concept of keys.There are nine types of keys in database.keys are used to fetch records from tables and to make relationship among tables or views.
1.Primary Key:
Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. Primary keys must contain unique values. There is only one primary key per table.

When multiple fields are used as a primary key, they are called a composite key.It can not accept null, duplicate values. Only one Candidate Key can be Primary Key.Primary key can be made foreign key into another table.

In SQL Server when we create primary key to any table then a clustered index is automatically created to that column.Primary Key is the column you choose to maintain uniqueness in a table at row level.

2.Foreign Key
Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables. It is also known as referential integrity.

Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values. We can have more than one foreign key in a table.

Foreign key do not automatically create an index, clustered or non-clustered. You can manually create an index on foreign key.

3.Super Key:
A super key is any set of attributes for which the values are guaranteed to be unique for all possible sets of tuples in a table at all times.

Super key is a set of one or more keys that can be used to identify a record uniquely in a table.Primary key, Unique key, Alternate key are subset of Super Keys.

A Super Key is simply a non-minimal Candidate Key, that is to say one with additional columns not strictly required to ensure uniqueness of the row.

Each super key is able to uniquely identify each tuple (record).

4.Candidate key:
A candidate key is simply the "shortest" super key. Candidate Key are individual columns in a table that qualifies for uniqueness of each row/tuple.Every table must have at least one candidate key but at the same time can have several.

A candidate key is a "minimal" super key - meaning the smallest subset of super key attributes which are unique. Removing any attribute from a candidate key would therefore make it non-unique.

Candidate keys are those keys which is candidate for primary key of a table. In simple words we can understand that such type of keys which full fill all the requirements of primary key which is not null and have unique records is a candidate for primary key.

5.Composite Key:
A composite key contains at least one compound key and one more attribute. Composite keys may also include simple keys and non-key attributes.When we create keys on more than one column then that key is known as composite key.

If a table do have a single column that qualifies for a Candidate key, then you have to select 2 or more columns to make a row unique.

Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key. It is also called the compound key.

6.Alternate Key:
If any table have more than one candidate key, then after choosing primary key from those candidate key, rest of candidate keys are known as an alternate key of that table.

Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key.

7.Unique Key:
Uniquekey is a set of one or more fields/columns of a table that uniquely identify a record in database table. We can have more than one unique key in a table.Unique key is to prevent duplicate values in a column.

In SQL Server, Unique key can be made foreign key into another table.It is like Primary key but it can accept only one null value and it can not have duplicate values.It can be a candidate key.You can’t change or delete primary values but Unique-key values can.

8.compound key:
A compound key is a key that consists of two or more attributes that uniquely identify an entity occurrence.A simple key is one that has only one attribute.

Compound keys may be composed of other unique simple keys and non-key attributes, but may not include another compound key.

9.Surrogate key:
A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data, unlike a natural (or business) key which is derived from application data.

The surrogate is internally generated by the system but is nevertheless visible to the user or application.The value contains no semantic meaning

Example:
Code:
In student table have four fields such as 
studentid, firstname.lastname,courseid

primary key : student id
Unique key : student id
Foreignkey : courseid
Alternate Key :  firstname +lastname
Composite Key:  firstname +lastname
Candidate keys : student id or firstname +lastname
Super key : {student id,firstname +lastname}

_________________
Learn Web Development | Latest Technology News |Motivational Quotes


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 1 post ] 

All times are UTC


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron