Super Key
A combination of one or more columns in a table which can be used to identify a record in a table uniquely, a table can have any number of super keys.
Candidate Key
A Column (or) Combination of columns which can help uniquely identify a record in a table without the need of any external data is called a Candidate Key. Depending on the need and situation a Table may have one or more candidate keys and one of them can be used as a Primary Key of the table.
A candidate key is a sub set of a Super Keys.
For Example, In an Employee Table, we may have columns like Employee ID, Employee Name, and Employee SSN. We can consider either Employee ID or Employee SSN as Candidate Key’s
Compound Key
A Combination of more than one column identifying records of a table uniquely, all the columns that take part in the combination process are Simple Key’s.
We can represent this buy taking a table which has the combined information of Department to Employees, Employee ID + Department ID – here both of them define a record in this table, but both can make sense separately as well.
Primary Key
A Column in a table (which is a Simple Key) which is a Candidate Key (Uniquely identify a records in a table) and has the Constraint NOT NULL attached to it is known as a Primary Key.
Generally, but not always and need not be, Primary Key goes with a Clustered Index.
Composite Primary Key (Composite Key)
When we have a Primary Key of a table defined using more than one columns then it is known as a Composite Key, each columns data can be duplicated, but combined values cannot be. The columns which are participating in a composite primary key are not simple keys.
For Example, we can have a situation where there is a need to define the key using first Name + last Name.
Alternate Key
We cannot define the Alternate Key Seperately from a Candidate Key, for a table, if there are two Candidate Key’s and one is chosen as a Primary Key the other Candidate Key is known as the Alternate Key of that table.
In Example, we can consider the Employee SSN as Alternate Key as we have taken Employee ID as our Primary Key
Unique Key
A column (or) combination of columns which can be used to uniquely identify a record in a table, it can have one NULL Value.
Primary Key can be considered a special case of unique key with a Not Null Constraint.
Generally, but not always and need not be, Unique Key goes with a Non Clustered Index.
Foreign Key
A column of one table points to the Primary Key column of another table to implement referential data integrity.
For Example, we can have a department id column in the employee table which is pointing to department id column in a department table where it a primary key.
If you have liked the article and want to get the articles (or) new posts at sql like, subscribe to articles through email by entering your email address in the top right and verifying the same. If you have any comments, then the below form is for you, go ahead!!!
Comments on this entry are closed.