SQL Keys are an important part of relational database design. A key is a column, or a combination of columns, that helps identify records or establish relationships between tables.
For example, a Student table may use StudentID to identify each student. An Enrollment table may use StudentID and CourseID together to identify which course a particular student has selected. These keys give the database a reliable way to identify and connect related records.
Understanding SQL Keys is especially important when designing relational databases because keys are closely connected with uniqueness, relationships, referential integrity, and normalization.
A key is an attribute or a set of attributes used to identify rows or define relationships in a relational database. Different types of keys have different roles.
A key does not always have to be a single column. In some situations, two or more columns are combined to identify a record. Such a key is called a composite key.
Student StudentID StudentName Email 101 Rahul rahul@example.com 102 Priya priya@example.com 103 Amit amit@example.com
If StudentID contains a different value for every student, it can be used to identify each row. If Email is also guaranteed to be unique, it can potentially identify a student as well.
| Key | Main Purpose |
|---|---|
| Super Key | Any attribute set that can uniquely identify a row |
| Candidate Key | A minimal super key that can uniquely identify a row |
| Primary Key | The candidate key selected as the main identifier |
| Alternate Key | A candidate key that was not selected as the primary key |
| Composite Key | A key made from two or more columns |
| Foreign Key | A column or set of columns used to reference another table |
A Primary Key is the key selected to uniquely identify each row of a table. A primary key cannot contain NULL values, and its values must be unique.
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100),
Course VARCHAR(50)
);
Here, StudentID is the primary key. Each student must have a different StudentID, allowing a particular row to be identified without depending on the student's name.
A Foreign Key is used to represent a relationship between tables. It contains values that refer to a key in another table, normally the primary key or another suitable unique key.
Consider two related tables: Department and Employee.
CREATE TABLE Department (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100) NOT NULL
);
CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100) NOT NULL,
DepartmentID INT,
FOREIGN KEY (DepartmentID)
REFERENCES Department(DepartmentID)
);
DepartmentID in the Employee table is a foreign key. It refers to DepartmentID in the Department table.
This allows the database to understand that an employee belongs to a department and helps prevent references to departments that do not exist, according to the foreign-key rules defined for the database.
INSERT INTO Department (DepartmentID, DepartmentName) VALUES (10, 'Computer Science'); INSERT INTO Employee (EmployeeID, EmployeeName, DepartmentID) VALUES (501, 'Amit', 10);
The value 10 in Employee.DepartmentID refers to the department whose ID is 10.
A Super Key is any set of one or more attributes that can uniquely identify a row in a table.
A super key may contain additional attributes that are not necessary for uniqueness.
Consider the following Student table:
StudentID Email StudentName
Suppose StudentID and Email are both unique.
The following can be super keys:
For example, StudentID alone is enough to identify a row. Adding StudentName does not make the identifier more useful for uniqueness, but StudentID + StudentName is still a super key because it contains a unique attribute.
A Candidate Key is a minimal super key. It uniquely identifies a row and does not contain an unnecessary attribute.
Suppose the following columns are guaranteed to be unique:
StudentID Email MobileNumber
If each of these attributes can independently identify a student, each can be a candidate key.
The important word is minimal. For example, if StudentID alone uniquely identifies a student, then StudentID + StudentName is not a candidate key because StudentName is unnecessary.
A table may have more than one candidate key, but one candidate key is normally selected as the primary key.
For example:
StudentID Email MobileNumber
Suppose all three are unique and suitable for identifying students. They can be candidate keys.
If the database designer selects StudentID as the primary key, then Email and MobileNumber remain candidate keys that were not selected as the primary identifier.
This distinction helps explain the relationship between candidate keys, primary keys, and alternate keys.
An Alternate Key is a candidate key that was not selected as the primary key.
For example, suppose StudentID and Email are both candidate keys:
| Column | Role |
|---|---|
| StudentID | Selected as Primary Key |
| Alternate Key |
The alternate key is still capable of uniquely identifying a student, but it is not the table's primary key.
A Composite Key is a key made from two or more columns. It is useful when no single column is sufficient to uniquely identify a row.
A common example is a student enrollment table. A student can enroll in many courses, and a course can have many students.
CREATE TABLE Enrollment (
StudentID INT,
CourseID INT,
EnrollmentDate DATE,
PRIMARY KEY (StudentID, CourseID)
);
Here, StudentID alone is not unique because one student can take multiple courses. CourseID alone is also not unique because many students can take the same course.
The combination of StudentID and CourseID identifies one enrollment record.
StudentID CourseID 101 10 101 20 102 10 102 30
Student 101 appears more than once, and Course 10 also appears more than once. However, the combination of StudentID and CourseID can uniquely identify each enrollment.
The following example shows how different keys can work together in a simple college database.
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100) NOT NULL,
Email VARCHAR(150) UNIQUE
);
CREATE TABLE Course (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100) NOT NULL
);
CREATE TABLE Enrollment (
StudentID INT,
CourseID INT,
EnrollmentDate DATE,
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID)
REFERENCES Student(StudentID),
FOREIGN KEY (CourseID)
REFERENCES Course(CourseID)
);
This design demonstrates several concepts together. StudentID identifies students, CourseID identifies courses, and the combination of StudentID and CourseID identifies an enrollment. The two foreign keys connect Enrollment with the Student and Course tables.
| Feature | Primary Key | Foreign Key |
|---|---|---|
| Main purpose | Identifies rows in its own table | References a key in another table |
| Duplicate values | Not allowed | Can occur when many child rows reference the same parent |
| NULL | Not allowed | May be allowed if the foreign-key column is nullable |
| Number in a table | One primary key constraint | Multiple foreign keys can be defined |
| Feature | Candidate Key | Primary Key |
|---|---|---|
| Meaning | A minimal unique identifier candidate | The candidate key selected as the main identifier |
| Number | There can be multiple candidate keys | One primary key constraint per table |
| Selection | May or may not be selected | Must be selected from the candidate keys in the relational model |
| Feature | Super Key | Candidate Key |
|---|---|---|
| Uniquely identifies row | Yes | Yes |
| Extra attributes allowed | Yes | No |
| Minimal | Not necessarily | Yes |
The easiest way to remember the difference is that every candidate key is a super key, but a super key is not necessarily a candidate key because it may contain unnecessary attributes.
The different key concepts are related rather than completely separate ideas.
Understanding this relationship makes SQL Keys easier to learn because the terms describe different roles in the same relational database design.
A primary key should be selected according to the structure and requirements of the data. A good primary key should reliably identify one row and should not change unnecessarily.
For example, using StudentID as the primary key is usually more practical than using StudentName because different students may have the same name.
When a natural identifier is not suitable, database designers often use a generated numeric identifier such as an auto-incrementing or identity column, depending on the database system.
Incorrect key design can create duplicate records, broken relationships, or unnecessary complexity. Some common problems include:
Keys contribute to different aspects of database integrity. A primary key helps maintain entity identity, while a foreign key helps maintain referential integrity between related tables.
For example, if an Enrollment record refers to StudentID 101, the foreign key relationship can require a corresponding student record to exist. This prevents the database from storing a relationship to a non-existent student.
The exact behavior of foreign keys, cascading operations, indexes, and constraint enforcement depends on the database management system being used.
A primary key uniquely identifies each row in a table. It prevents duplicate identifiers and does not allow NULL values.
A table has one primary key constraint. However, that primary key can contain multiple columns, in which case it is a composite primary key.
A candidate key is a minimal set of attributes that can uniquely identify a row. One candidate key is selected as the primary key.
A composite key consists of two or more columns that together identify a row uniquely. It is useful when no single column provides the required uniqueness.
A foreign key represents a relationship between tables by referencing a key in another table. It helps maintain referential integrity.
SQL Keys provide the foundation for identifying records and connecting related tables in a relational database. Primary Keys identify rows, Foreign Keys represent relationships, Candidate Keys provide possible unique identifiers, Alternate Keys are candidate keys that were not selected as the primary key, and Composite Keys combine multiple columns when one column is not sufficient.
Super Keys and Candidate Keys are also important for understanding how uniqueness is defined during relational database design. Once these concepts are clear, topics such as normalization, relationships, joins, and database schema design become easier to understand.
The most important principle is to choose keys according to the actual structure of the data. A well-designed key should identify records reliably and should support the relationships required by the database.