Keys in DBMS
Subject: Computer Science Fundamentals · Language: DBMS · Level: beginner · 10 min read
Learn DBMS keys with numbered types: super key, candidate key, primary key, alternate key, foreign key, composite key, unique key, and why keys are important.
Keys are attributes or sets of attributes used to identify records and create relationships between tables. They are one of the most important concepts in relational database design because they keep records clear, unique, and connected. A beginner can understand keys with a student example. Many students may have the same name, but each student should have a unique roll number. The roll number helps identify the exact student record. In the same way, database keys help identify exact rows in a table.
Without keys, a database can become confusing. Duplicate records may appear, relationships between tables may break, and the DBMS may not know which row should be updated or deleted.
Why Keys Are Needed
Keys are needed to uniquely identify rows in a table. If a table stores thousands of records, the DBMS needs a reliable way to find one exact record. Keys are also needed to connect tables. In a relational database, data is often split into multiple tables to reduce duplication. Keys allow these tables to be linked properly.
For example, a Students table may store student details, and a Marks table may store marks. The roll number can connect a marks record with the correct student record.
Simple Key Diagram
This simple diagram shows how a primary key in one table can become a foreign key in another table.
```text
Students table
roll_no (Primary Key) ----+
name |
department |
|
v
Marks table
mark_id
roll_no (Foreign Key)
subject
marks
```
Here, roll_no uniquely identifies a student in the Students table. The same roll_no is stored in the Marks table to show which student received those marks.
Types of Keys
The important key types are listed below. Numbering them helps students remember each type separately.
1. Super key: Any attribute or group of attributes that can uniquely identify a row.
2. Candidate key: A minimal super key without unnecessary extra attributes.
3. Primary key: The candidate key selected to uniquely identify rows in a table.
4. Alternate key: Candidate keys that are not selected as the primary key.
5. Foreign key: An attribute that refers to the primary key of another table.
6. Composite key: A key made using more than one attribute.
7. Unique key: A key that prevents duplicate values, usually allowing database-specific null behavior.
8. Surrogate key: An artificial key created by the system, such as an auto-increment ID.
Each key type has a different purpose. Some identify rows, some connect tables, and some enforce uniqueness.
Super Key
A super key is any attribute or combination of attributes that can uniquely identify a row in a table. It may contain extra attributes that are not actually needed. For example, in a Student table, roll_no alone can identify a student. So roll_no is a super key. The combination of roll_no and name is also a super key because it still uniquely identifies a student, but name is extra if roll_no is already unique.
This is why every candidate key is a super key, but every super key is not necessarily a candidate key.
Candidate Key
A candidate key is a minimal super key. Minimal means it does not contain unnecessary attributes. It can uniquely identify a row using only the required attribute or attributes. For example, if roll_no and email can both uniquely identify a student, then both can be candidate keys. Each is capable of becoming the primary key.
A table can have more than one candidate key. The database designer chooses one of them as the primary key.
Primary Key
A primary key is the candidate key selected to uniquely identify each row in a table. It cannot contain duplicate values and should not be null. For example, roll_no can be the primary key in a Student table. Every student should have a roll number, and no two students should have the same roll number.
Primary keys are important because many other tables may refer to them. If the primary key is weak or unstable, relationships in the database can become difficult to manage.
Alternate Key
An alternate key is a candidate key that was not selected as the primary key. It can still uniquely identify rows, but it is not the main identifier chosen for the table. For example, suppose a Student table has roll_no and email, and both are unique. If roll_no is selected as the primary key, then email becomes an alternate key.
Alternate keys are useful because they can still enforce uniqueness. A student email should not be repeated even if it is not the primary key.
Foreign Key
A foreign key is an attribute in one table that refers to the primary key of another table. It is used to create relationships between tables. For example, roll_no in the Marks table can be a foreign key that refers to roll_no in the Student table. This means every marks record belongs to a valid student.
Foreign keys help maintain referential integrity. They prevent records from pointing to non-existing related records.
Composite Key
A composite key uses more than one attribute to uniquely identify a row. It is used when a single attribute is not enough. For example, in an Enrollment table, student_id alone is not enough because one student can enroll in many courses. course_id alone is also not enough because one course can have many students. But student_id and course_id together can identify one enrollment record.
Composite keys are common in tables that represent many-to-many relationships.
Unique Key
A unique key ensures that values in a column or group of columns do not repeat. It is similar to a candidate key in the sense that it enforces uniqueness. For example, email can be defined as unique in a Student table so that two students cannot register with the same email address. The exact handling of null values in unique keys can depend on the database system. For beginner understanding, remember that a unique key mainly prevents duplicate values.
Surrogate Key
A surrogate key is an artificial key created by the system or database designer. It does not come from real-world data. For example, student_id may be generated automatically as 1, 2, 3, and so on. This ID can be used as the primary key even if the student also has a roll number.
Surrogate keys are useful because they are simple and stable. Real-world values like phone number or email may change, but a generated ID usually stays the same.
Primary Key vs Foreign Key
The following table shows the difference between primary key and foreign key. This comparison is important because students often confuse these two terms.
| Point | Primary Key | Foreign Key |
| --- | --- | --- |
| Purpose | Uniquely identifies a row in its own table | Connects one table to another table |
| Duplicate values | Not allowed | May repeat depending on relationship |
| Null value | Not allowed | May be allowed depending on design |
| Location | Present in the main table | Present in the related table |
| Example | roll_no in Students | roll_no in Marks |
Choosing a Good Primary Key
A good primary key should be unique, not null, stable, and simple. It should not change frequently because other tables may depend on it. For example, phone number is usually not a good primary key because a person can change their phone number. A generated student_id or official roll number is usually better.
Choosing keys carefully makes the database easier to maintain and reduces future problems.
Common Mistakes With Keys
A common mistake is choosing a non-unique attribute as a primary key. For example, name should not be used as a primary key because many people can have the same name. Another mistake is ignoring foreign keys. Without foreign keys, related tables may contain invalid references. For example, a Marks table may store marks for a roll number that does not exist in Students.
Students should also avoid making composite keys unnecessarily complex. If a simple stable key is available, it is usually easier to use.
Important Points to Remember
Keys help identify rows and connect tables. Primary key identifies a row in its own table. Foreign key connects one table to another table. Candidate keys are possible primary keys, and alternate keys are candidate keys not selected as primary key.
For beginners, the most important keys are primary key, foreign key, candidate key, and composite key. Once these are clear, other key types become easier to understand.