Wednesday, November 18, 2020

Different types of Keys in SQL Server

Key: A key is a single or combination of more than one columns in a table. We can also say that a key is a field or columns in a table in database. There are the different types of keys in sql server. These are as below:

Primary Key: A primary key is a key that uniquely identify a each record in a table. A primary key can't accept null value while set primary key on a column during table creation in database. Here, ID column is a primary key of an Employee table. Please see the below screenshot.


Super Key: A super key is a set of attributes or set of columns which identify a specific row in a table. A Unique Key, Primary key and Alternate key are a subsets of Super Key. Here, ID, EMP_CODE, EMAIL columns are the super keys of an Employee table. Please see the below screenshot. 


Alternate Key: A alternate key is a field or set of fields that uniquely identify a record in a table. For example ID is a primary key and the other fields i.e. emp_code and email are the alternate keys in table. There will be only one column as primary key in a table. Here, EMP_CODE, EMAIL columns are the alternate keys of an Employee table. Please see the below screenshot. 


Candidate Key: A candidate key is a column or combinations of columns that identify a tuple in a table. There are more than one candidate keys in a table. A candidate key works as same as primary key that uniquely identify a tuples. Here, ID, EMP_CODE, EMAIL columns are the candidates keys of an Employee table.  Please see the below screenshot.


Unique Key: A unique key is a key that uniquely identify a record in a table.Its a combinations of one or more than one columns in a table. A unique key works as same as primary key but it doesn't allow duplicate value in a column. Here, EMP_CODE column is a unique key of an Employee table. Please see the below screenshot.


Foreign Key: A foreign key is a key that is used as a primary key of one table and its links as a foreign key in another table. Mainly, it creates relationship between two or more than two tables. There will be duplicate values in columns when using foreign key in the tables. Here, EMPLOYEE_ID column is a foreign key in EmployeeDetail table and its primary key i.e. ID column in Employee table. Please see the below screenshot. 


Composite Key: A composite key is a set of more than one columns of a table that uniquely identify a particular record in a table. 


           - - - - - - Cheers, Happy to Help! - - - - - - 

 


No comments:

Post a Comment

DIFFERENCE BETWEEN CHAR AND VARCHAR IN SQL SERVER

CHAR:  The  CHAR datatype is a fixed length data type in sql server. It is used to store fixed length type of string data or character strin...