What are the keys to SQL Server? How many types of keys are in SQL Server and also define for example?
Keys in SQL Server :
A key is a single or combination of multiple columns in database tables. Keys are used to retrieve data and rows from Database table based on requirement or conditions. Keys are also used to create relationship between different tables and views in database.
Types of Keys in SQL Server :
There are several types of keys are used in SQL Server Database which are as-
Super Key : A super key is a single or group of keys that can used to uniquely identify rows in a table.
Ex- primary key, Alternate key and Unique are subset of super key.
Candidate Key : It is a single key or group of multiple keys that can identify uniquely of rows in a table.
Ex- EmpId, License_Number and Passport_number of Employee table is a Candidate key.
Primary key : A Primary key is a Candidate key that selected by Database administrator to uniquely identify the row in database table. There can be only one Candidate key become a primary key to retrieve the data from table.
Ex- EmpId of Employee table is a primary key. It also a Candidate key.
Alternate Key : Alternate key are those Candidate keys which are not selected as primary key.
Ex- License_Number and Passport_number is Alternate key of table Employee
Foreign Key : Foreign key is a key of table that are defined as primary key in its parent table. Or primary key of a parent table is a foreign key of its child table.
Ex- EmpId of Salary table is a foreign key.
Composite Key : A Composite key is a primary key or candidate key that consist more than one attribute(column name) of a table.
Ex- EmpId and SalaryDate of Salary table is composite key.
Unique Key : A unique key is a set of one or more than one field or column of a table that uniquely identify a record in a database table.
Ex- License_Number and Passport_number of Employee Table is a Unique key.