0
0
In relational database design, keys play a critical role in uniquely identifying records and establishing relationships between tables. Below are the different types of keys used in SQL:
1. Primary Key
A Primary Key uniquely identifies each row in a table and does not allow null values. Only one Primary Key is allowed per table.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50)
);2. Foreign Key
A Foreign Key creates a link between two tables by referencing the Primary Key in another table. It ensures referential integrity.
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
EmployeeID INT,
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);3. Unique Key
Unique Keys ensure that all values in a column are distinct but allow one null value.
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Email VARCHAR(100) UNIQUE
);4. Composite Key
A Composite Key uses two or more columns to uniquely identify a row.
CREATE TABLE Enrollment (
StudentID INT,
CourseID INT,
PRIMARY KEY (StudentID, CourseID)
);5. Candidate Key
Any column or combination of columns that can serve as a Primary Key is called a Candidate Key. If one is chosen as the Primary Key, others become Alternate Keys.
6. Alternate Key
An Alternate Key is any Candidate Key not chosen as the Primary Key.
Summary Table of Key Differences
| Aspect | Primary Key | Foreign Key | Unique Key | Composite Key | Candidate Key |
|---|---|---|---|---|---|
| Uniqueness | Unique | May contain duplicates | Unique | Unique across multiple columns | Unique |
| Null Values | Not allowed | Allowed | One null value allowed | Depends on columns | Not allowed (if chosen as Primary Key) |
| Relation to Other Tables | None | References a Primary Key in another table | None | None | None |
| Primary Use | Uniquely identifies rows | Establishes relationships between tables | Ensures uniqueness of column values | Identifies rows across multiple columns | Can be chosen as Primary Key |
Tricky Interview Questions
1. Can a table have multiple Primary Keys?
Answer: No, a table can only have one Primary Key, but it can have multiple Candidate Keys.
2. Can a Foreign Key reference a Unique Key?
Answer: Yes, a Foreign Key can reference either a Primary Key or a Unique Key in another table.
3. What happens when a row with a Foreign Key is deleted in the referenced table?
Answer: It depends on the referential action set (e.g., CASCADE, SET NULL). If CASCADE is set, deleting the referenced row will also delete the rows in the child table.
4. What is the difference between a Candidate Key and an Alternate Key?
Answer: A Candidate Key is any column(s) that can uniquely identify a row in a table. An Alternate Key is a Candidate Key that is not chosen as the Primary Key.
5. Can a Composite Key be a Foreign Key?
Answer: Yes, a Composite Key can be used as a Foreign Key, referencing another table’s Composite Key.
6. Can a table have both a Primary Key and a Foreign Key?
Answer: Yes, a table can have both. The Primary Key uniquely identifies rows within the table, while the Foreign Key establishes a relationship with another table.
7. Can you have multiple Foreign Keys in a table?
Answer: Yes, a table can have multiple Foreign Keys, each referencing different tables or even the same table in cases of self-referencing.