Introduction
Two students can share the same name, one student can enroll in several courses, and every enrollment must refer to an existing student. A database needs rules that distinguish records and preserve these connections.
Keys provide those rules. Some identify rows, some express alternative identifiers, and others enforce references. Understanding their different roles helps prevent duplicate records, ambiguous relationships, and invalid data.
Key Hierarchy
Suppose STUDENT contains StudentID, AdmissionNo, and Name. The college requires both StudentID and AdmissionNo to uniquely identify students.
The following diagram shows how superkeys, candidate keys, primary keys, and alternate keys relate.
Key Hierarchy
Superkeys
A superkey is a set of attributes that uniquely identifies a row. It may contain unnecessary attributes.
For example, both StudentID and the combination StudentID plus Name are superkeys. Adding Name does not improve identification because StudentID is already unique.
Uniqueness must follow the intended rules for every valid database state. A column that happens to contain different values today is not automatically a key.
Candidate Keys
A candidate key is a minimal superkey. Removing any attribute from it would destroy its guaranteed identifying ability.
Here, StudentID and AdmissionNo are separate candidate keys. StudentID plus Name is not a candidate key because Name can be removed.
Minimal does not mean shortest. A table can have a single-column candidate key and a different two-column candidate key, provided neither contains a removable attribute.
Primary and Alternate Keys
The primary key is the candidate key selected as the principal row identifier. The remaining candidate keys are alternate keys.
If StudentID is selected as primary, AdmissionNo remains an alternate key and must retain its uniqueness. A table has one primary-key constraint, which can include multiple columns.
Key Structure and Origin
Key terminology can describe different dimensions. “Composite” describes structure, while “natural” and “surrogate” describe origin.
Simple Keys
A simple key contains one attribute, such as StudentID.
A one-column key is convenient, but convenience alone does not establish that its values are unique or suitable identifiers.
Composite Keys
A composite key contains two or more attributes that identify a row together.
For ENROLLMENT, assume a student can enroll in a particular course only once. Neither StudentID nor CourseID alone identifies an enrollment, but their combination does.
Composite Keys
If repeat enrollments are allowed, the identifier must also distinguish the course offering, term, or attempt.
A composite primary key is one key containing several columns, not several primary keys.
Natural and Surrogate Keys
Key origin | Meaning | Example | Main consideration |
|---|---|---|---|
Natural key | An identifier with meaning in the application’s domain | A college admission number | Its uniqueness, scope, and stability must be established |
Surrogate key | An identifier introduced for database identity | An internally generated StudentID | It does not enforce business uniqueness by itself |
Adding StudentID does not prevent two records from using the same admission number. Preserve that business rule with an additional constraint.
Terms such as compound key are used differently across textbooks. State the actual columns and identifying rule instead of relying on the label alone.
Foreign and Partial Keys
Foreign Keys
A foreign key constrains references to an eligible key in a referenced table. It can contain one column or several columns.
The following illustration focuses on the student reference within ENROLLMENT.
Foreign Keys
Several enrollments can reference the same student. A foreign key therefore does not automatically make its own values unique.
Important cases include:
Optional references: A nullable single-column foreign key permits an absent reference. Use NOT NULL when the association is compulsory.
Alternative referenced keys: A foreign key can reference an eligible unique key, not only the primary key.
Self-references: An employee’s ManagerID can reference EmployeeID in the same table.
Composite references: The referencing columns must correspond to the referenced key’s columns and use compatible types.
Foreign-key constraints preserve referential integrity.
Partial Keys
A partial key, or discriminator, distinguishes weak entities belonging to the same owner.
For example, RoomNo identifies a room within a building. BuildingID plus RoomNo identifies it across the database.
RoomNo is not globally unique. It contributes to identification together with the owner’s key.
Secondary Keys and Indexes
Some textbooks use secondary key for attributes used to retrieve records, such as Department or LastName. Such values need not uniquely identify rows.
A key constraint expresses a logical rule. An index is an access structure. A non-unique index does not make its columns a candidate key.
Implementing Keys in SQL
The following example demonstrates:
A generated StudentID primary key.
A required, unique AdmissionNo alternate key.
A CourseID primary key.
A composite enrollment primary key.
Foreign keys connecting each enrollment to its student and course.
The two versions implement the same rules. Use the appropriate version in your language tabs.
CREATE TABLE student ( student_id BIGINT AUTO_INCREMENT PRIMARY KEY, admission_no VARCHAR(20) NOT NULL, student_name VARCHAR(100) NOT NULL, CONSTRAINT uq_student_admission UNIQUE (admission_no));CREATE TABLE course ( course_id INT PRIMARY KEY, course_name VARCHAR(100) NOT NULL);CREATE TABLE enrollment ( student_id BIGINT NOT NULL, course_id INT NOT NULL, PRIMARY KEY (student_id, course_id), CONSTRAINT fk_enrollment_student FOREIGN KEY (student_id) REFERENCES student(student_id), CONSTRAINT fk_enrollment_course FOREIGN KEY (course_id) REFERENCES course(course_id));The MySQL version uses InnoDB and AUTO_INCREMENT. The PostgreSQL version uses an identity column and has no ENGINE clause.
With these constraints:
Attempt | Result |
|---|---|
Insert two students with the same required admission number | Rejected |
Enroll one student in two different courses | Allowed |
Insert the same student–course pair twice | Rejected |
Reference a nonexistent student or course | Rejected |
These outcomes assume the other required values are valid.
UNIQUE and NULL
A nullable UNIQUE column is not equivalent to a primary key. PostgreSQL’s default unique constraints allow multiple NULL values; its NULLS NOT DISTINCT option changes that behavior.
For a required alternate identifier such as AdmissionNo, combine UNIQUE with NOT NULL.
Choosing and Maintaining Keys
Define the row’s meaning first: A key must identify the intended subject or event. StudentID and CourseID cannot distinguish repeated course attempts unless the model includes another discriminator.
Prefer stable identifiers: Changing a referenced identifier can affect many related records. Names and contact details are often unsuitable because they can change or be shared.
Preserve every business identifier: A surrogate primary key does not remove the need to enforce alternate candidate keys.
Do not assume generated numbers are gapless: Rollbacks, deletions, and allocation behavior can leave gaps. An identifier is not automatically a consecutive business-document number.
Choose deletion policies deliberately: A foreign key can restrict deletion or use a supported referential action. Referential integrity does not imply that cascading deletion is always appropriate.
Consider implementation costs: Wide keys can increase index and reference storage. Evaluate those costs without discarding necessary uniqueness rules.
Keys also support dependency analysis and normalization. Understanding every candidate key matters, even when the application usually searches using only the primary key.
Summary
Keys identify records and preserve relationships. Superkeys guarantee uniqueness, candidate keys remove unnecessary attributes, and primary and alternate keys express the chosen identifying roles. Keys may be simple, composite, natural, or surrogate. Foreign keys enforce references, while partial keys support weak-entity identification. Good design preserves business uniqueness alongside convenient technical identifiers.
Be the first to add a comment.