Introduction to Primary and Candidate Keys
In relational database design, keys play a pivotal role in ensuring data integrity and enabling efficient querying. This tutorial will guide you through understanding primary keys, composite keys, candidate keys, and their significance within the relational data model.
Primary Keys: The Cornerstone of Uniqueness
A primary key is a column or set of columns that uniquely identifies each row in a table. It must contain unique values and cannot have nulls. In practice, databases allow only one primary key per table. This uniqueness constraint ensures that no two rows are identical, which is fundamental for accurate data retrieval.
Composite Primary Keys: Multiple Columns as One
Sometimes a single column isn’t enough to ensure uniqueness across all records in a table. In such cases, you can create a composite primary key—a primary key that consists of multiple columns combined together. These columns must collectively satisfy the uniqueness condition for every row.
Example of a Composite Primary Key
Consider a userdata table where both userid and userdataid are necessary to uniquely identify each record:
CREATE TABLE userdata (
userid INT,
userdataid INT,
info CHAR(200),
PRIMARY KEY (userid, userdataid)
);
Here, the combination of userid and userdataid forms a composite primary key. This ensures that while a single userid or userdataid can be duplicated across different rows, each pair is unique.
Candidate Keys: The Foundation of Uniqueness
A candidate key is any column or set of columns within a table that can uniquely identify a row. Unlike primary keys, candidate keys are not limited to one per table; there may be multiple candidate keys available.
Candidate keys share two crucial properties:
- They must contain unique values.
- They cannot have null values.
Every candidate key has the potential to become the primary key for the table. However, while all candidate keys could serve as a primary key, only one is selected and designated as such.
Alternate Keys: The Unchosen Candidates
The alternate keys are the candidate keys that were not chosen to be the primary key. These still maintain their uniqueness properties but aren’t used by default for identifying records in operations like joins or foreign key references.
Key Concepts in Practice
When designing databases, it’s common practice to select one of the candidate keys as the primary key and use any necessary unique constraints on alternate keys. This ensures optimal query performance and maintains referential integrity through foreign keys that reference the chosen primary key rather than alternate keys.
Understanding Composite Keys with Auto-Generated Columns
While composite keys are a powerful feature, their interaction with auto-generated (auto-increment) columns can be tricky. In some database systems like MyISAM in MySQL, it is possible to have one column of a composite key auto-increment:
DROP TABLE IF EXISTS `test`.`animals`;
CREATE TABLE `test`.`animals` (
`grp` CHAR(30) NOT NULL,
`id` MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
`name` CHAR(30) NOT NULL,
PRIMARY KEY (`grp`, `id`)
) ENGINE=MyISAM;
INSERT INTO animals (grp, name) VALUES
('mammal', 'dog'), ('mammal', 'cat'),
('bird', 'penguin'), ('fish', 'lax'),
('mammal', 'whale'), ('bird', 'ostrich');
SELECT * FROM animals ORDER BY grp, id;
In this example, id is part of a composite primary key with grp. While MyISAM allows this setup, other database systems like InnoDB may not support auto-incrementing columns in a composite key.
Best Practices
When working with keys in relational databases:
- Always ensure that your primary key uniquely identifies each row.
- Consider the use of composite keys when single-column uniqueness isn’t feasible.
- Select alternate candidate keys as unique constraints to maintain data integrity where needed.
- Understand how different database systems handle auto-incremented columns within composite keys.
By adhering to these principles, you can design robust and efficient databases that effectively support your application’s requirements.