In SQL Server, a key is a form of constraint that operates to ensure the uniqueness, accuracy, and integrity of data stored in a table. for example – Imagine it a rule that determines how rows in one table interact with those in another.
Keys play an essential role in sql server:
- Avoiding duplicate records.
- Identifying each record uniquely.
- Establishing relationships between tables.
- Maintaining data consistency.
Types of Keys in SQL Server
Primary Key:
The main purpose of A primary key is to uniquely identify each record in a table.
It will not accept null values. A table can only have one primary key, but it can contain one or more columns (composite keys).
Example – Create a table with a primary key
CREATE TABLE Tbl_Employees (
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(100),
Address NVARCHAR(MAX),
Department NVARCHAR(50)
);Foreign Key:
A foreign key acts as an intermediary between two tables. For using a foreign key on the 2nd table, those columns should be the primary key in main or 1st table.
For example, suppose you have two tables: Tbl_Employees and Tbl_empAddress. Create a foreign key based on a specific column (empId) in an empAddress table by making the empId column the primary key in the employee table.
It ensures referential integrity, meaning you cannot insert a value in the foreign key column if it doesn’t exist in the parent table.
Example:
CREATE TABLE Tbl_empAddress (
AdrsID INT PRIMARY KEY,
EmployeeID INT FOREIGN KEY REFERENCES Tbl_Employees(EmployeeID)
);Unique Key:
- A Unique Key ensures that every single value in a column is unique.
- Unlike the Primary Key, it allows one NULL value.
- A table can have multiple Unique Keys.
Example:
CREATE TABLE Tbl_Users (
UserID INT PRIMARY KEY,
Email NVARCHAR(100) UNIQUE
);Composite key:
- A composite key is created by merging two or more columns to provide a unique identifier.
- When a single column is required to identify a row uniquely, this method is useful.
Example:
CREATE TABLE Tbl_StudentCourses (
StudentID INT,
CourseID INT,
PRIMARY KEY (StudentID, CourseID)
);Candidate key:
- Candidate Keys refer to all columns that have the ability to become Primary Keys.
- One is picked as the Primary Key, while the others remain as alternate keys.
Alternative Key:
- An Alternate Key is a candidate key that was not selected as the primary key.
- It is still capable of uniquely identifying a row.
Super key:
A Super Key is a group of columns that can be used to uniquely identify rows in a table.
A Primary Key is always a Super Key, but not every Super Key is a Primary Key.
Frequently Asked Questions (FAQ) keys in SQL Server
1. What are keys in SQL Server?
In SQL Server, keys are restrictions used to uniquely identify records and ensure data integrity in a database table. They also help to create relationships between tables.
2. How many different key types are there in SQL Server?
SQL Server supports a wide range of keys, which help us identify unique records and avoid duplicate records. There are some important keys, which are the following:
- Primary Key
- Foreign Key
- Unique Key
- Composite Key
- Candidate Key
- Alternate Key
- Super Key
3. What is the difference between a Primary and Unique Key in SQL Server?
- A Primary Key is unique to each row and does not tolerate NULL values.
- A Unique Key ensures that uniqueness while allowing for one NULL value in the column.
4. Can a table have several primary keys?
No, Primary Key can not be applied to multiple columns in a table. The primary key should be single in the table. However, the Primary Key might be generated from multiple columns (known as a Composite Key).
5. What is a Foreign Key in SQL Server?
A foreign key works to connect two tables. It involves another table’s Primary Key and ensures that only valid data is added, hence protecting the validity of references.
6. What is the difference between Candidate Key and Alternate Key?
- Candidate keys are all possible keys that uniquely identify a record to avoid duplicate records.
- One of these is chosen as the major key, while the others are known as alternative keys.
7. Why are keys important in SQL Server?
Keys must be used because they:
- Prevent records from being duplicated.
- Maintain data trustworthiness and transparency.
- Make sure and validate relationships between tables.
- Help with more rapid searching and indexing.
