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
2.Foreign Key
3.Super Key
4.Candidate key
5.Composite Key
6.Alternate Key
7.Unique Key
8.compound key
9.Surrogate key
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.
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.
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.Every table must have at least one candidate key but at the same time can have several.
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:
Unique key 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:
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}
1.Primary Key
2.Foreign Key
3.Super Key
4.Candidate key
5.Composite Key
6.Alternate Key
7.Unique Key
8.compound key
9.Surrogate 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.
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.
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.Every table must have at least one candidate key but at the same time can have several.
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:
Unique key 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:
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}
