In relational databases, primary keys and foreign keys play crucial roles in maintaining data integrity and establishing relationships between tables. Understanding the fundamental differences between these keys is essential for designing well-organized databases. In this guide, we’ll compare primary key vs. foreign key, suggest tips for using both and help you understand how together, they can transform a good database into a great one.
What is a Primary Key?
A primary key in an Entity Relationship Diagram (ERD) is a unique identifier for each record within an entity. It ensures data accuracy and integrity by preventing duplication and guaranteeing that each row in a table is distinct. Every entity in a relational database, must have a primary key to maintain uniqueness.
For example, an Employee_ID can serve as a primary key, ensuring that even if two employees share the same name, their IDs remain different. The primary key in ERD plays a crucial role in database normalization, helping organize tables efficiently while minimizing redundancy.
What is a Foreign Key?
A foreign key in a relational database is used to establish relationships between tables by referencing the primary key of another table. It ensures referential integrity, preventing orphaned records and maintaining data consistency.
By linking related data in different tables, a foreign key in ERD helps structure databases efficiently, simplify queries, and enforce constraints to avoid invalid entries. For example, in an ‘Orders’ table, the ‘Customer_ID’ acts as a foreign key, ensuring every order belongs to an existing uniquely identified customer.
Differences in How the Primary Key and Foreign Key Functions
Primary Key Functions
A primary key uniquely identifies each record in a database table and plays a central role in maintaining data integrity. Its main functions include:
Unique Identification: Each record in a table must have a distinct primary key value. This ensures no two rows are identical, allowing precise access, updates, and deletions of data.
Data Integrity and Consistency: By enforcing uniqueness and non-null constraints, the primary key guarantees that every record is valid and traceable. It acts as a reliable point of reference for relationships across the database.
Non-Nullable Constraint: A primary key cannot contain NULL values. This ensures that every record is always identifiable, so no record can exist without a valid identifier.
Preventing Duplicate or Orphaned Records Internally: Since a table can have only one primary key, and it must contain unique, non-null values, it prevents duplicate or disconnected data within the same table.
Stability in Relationships: Primary key values are rarely changed because they serve as anchors for foreign keys in other tables. Updating a primary key can disrupt these references, so such operations are generally avoided.
Foreign Key Functions
A foreign key establishes a link between two tables by referencing the primary key of another table. It ensures referential integrity, meaning relationships between records remain valid and consistent. Its functions include:
Creating Relationships Between Tables: The foreign key connects records from one table to another (e.g., linking Orders to Customers). This allows efficient data retrieval and provides structural insight into how entities are related.
Referential Integrity Enforcement: Foreign keys ensure that a record always references a valid primary key in the related table. If a referenced record is deleted or missing, it can result in orphaned records which proper constraints help prevent.
Uniqueness Relationship: A foreign key does not need to be unique within its table; multiple records can reference the same primary key in another table. However, every foreign key value must match a unique primary key value in the referenced table.
Handling NULL Values: Foreign keys can contain NULL values when a relationship is optional or temporarily undefined. Example: In an e-commerce order, a shipping_address_id may remain NULL until a shipping address is added.
Updates and Deletions:
- Updating: A foreign key can be updated as long as the new value corresponds to an existing primary key.
- Deleting: Deleting a record that contains a referenced primary key can break relationships in other tables. To prevent data loss, referential actions such as ON DELETE CASCADE or ON DELETE SET NULL are often used.
The primary and foreign keys are just two of the attributes that belong to entities (tables) in a database. To learn more about other types, read attributes in ERD.
Primary Key Vs. Foreign Key Comparison
Criteria | Primary Key | Foreign Key |
Purpose | Uniquely identifies each record in a table | Establishes relationships between tables by referencing a primary key |
Uniqueness | Must be unique for each record | Uniqueness is optional |
Data Type | A unique identifier, usually numeric | Same as primary key data type it refers to |
NULL | Cannot be NULL | Can be NULL |
Number | Only one primary key per table | Multiple foreign keys can exist in a table |
Insertion | Must be assigned when a record is created | Can be inserted with a valid reference to an existing primary key |
Indexing | Automatically indexed for fast retrieval | Not automatically indexed but can be indexed manually to speed up retrieval |
Duplication | No duplicates allowed | Duplicates are allowed indicating multiple records with the same reference |
Deletion | Cannot be deleted from the parent table if it is referenced as a foreign key from another table | Can be updated or deleted, but may cause orphaned records if not handled properly |
Primary Key Vs. Foreign Key Example
Let’s consider an e-commerce site to illustrate how keys work. Each product on the site requires a unique ‘Product_ID’, so that no two products can ever be assigned the same ID. A primary key is needed for this.
The ‘Products’ table will include details about each product such as the unique ‘Product_ID’ (1234), the ‘Product_Name’ (t-shirt, trousers, etc) and the respective ‘Product_Description’ (cotton, nylon, etc). This table will also feature a foreign key called ‘Category_ID’ which will link to the table called ‘Categories’.
The ‘Categories’ table exists to categorize the products. In it, the ‘Category_ID’ is a primary key (A2025) for a field like ‘Category_Name’ (Clothing). Now the foreign key (Category_ID) in the ‘Products’ table is referencing the primary key (Category_ID) in the ‘Categories’ table.
If a field in the ‘Categories’ table such as ‘Category_Name’ were to be updated , this will automatically update in the ‘Products’ table for any products connected to that category.
In the example ER diagram below, ‘PK’ is used to indicate the primary key and ‘FK’ is the foreign key.
Discover more examples with primary and foreign keys in the ERD templates collection.
Tips for Using Primary Key and Foreign Key
Here are some useful tips for working with primary and foreign keys in databases.
Choose Simple Primary Keys
Use single-column primary keys whenever possible to simplify indexing, query execution and relationships. For example, a serial number such as 13874 is vastly more efficient as a ‘Employee_ID’ primary key than one that uses a combination of an employee name and date of birth.
Ensure Uniqueness
Primary keys must be unique and non-null to reliably identify records.
Use Indexing
Primary keys are automatically indexed, improving query performance.
Define Foreign Key Constraints
Foreign keys should reference valid primary key values to maintain referential integrity.
Enable Cascading Actions
Cascading actions in primary and foreign keys help maintain referential integrity in a database when a related record is updated or deleted. These actions ensure that changes in a primary key table are properly reflected in the foreign key table.
Avoid Changing Primary Keys
Modifying primary keys can disrupt relationships and indexing. Avoid changing them if possible.
Optimize Data Types
Choose appropriate data types for keys to minimize storage and enhance speed.
Normalize Database Design
Properly structure tables to reduce redundancy and improve efficiency.
By following these tips, you can maintain clean relationships and strong referential integrity in your database design. To put these practices into action, use Creately’s ER diagram maker which can visually map entities, define keys and relationships, and create optimized database structures collaboratively.
Other Types of ERD Keys
In databases that are more complex, there are other types of keys beyond the simple primary and foreign keys we looked at earlier.
Consider the below ‘Students’ table to explain the differences between the keys. The Primary Key in the table is ‘Student_ID’.
Student_ID | Name | Phone | |
101 | Alice | alice@email.com | 1234567890 |
102 | Bob | bob@email.com | 9876543210 |
103 | Charlie | charlie@email.com | 4567891230 |
Super Key
A superkey is a set of one or more attributes that uniquely identify each row in a table. Any combination of columns that ensures uniqueness qualifies as a superkey. The primary key is selected from the set of superkeys, ensuring it is the minimal key required for uniqueness—meaning it contains no unnecessary attributes. A superkey may contain extra attributes beyond what is necessary for uniqueness.
Possible superkeys in the example table are:
- Student_ID
- Student_ID, Email
- Student_ID, Phone
- Student_ID, Name, Email
- Student_ID, Name, Phone
- Student_ID, Name, Email, Phone
Since ‘Student_ID’ alone is enough to uniquely identify each row, it is also a candidate key and a primary key. However, any combination that includes ‘Student_ID’ is still a valid superkey because it maintains uniqueness.
Candidate Key
A candidate key in a database is a set of attributes that uniquely identifies each row in a table. It is a minimal superkey, meaning it has no unnecessary attributes—only the essential ones needed for uniqueness. No two rows can have the same values for a candidate key. A candidate key contains only the necessary attributes to ensure uniqueness and cannot contain NULL values. One candidate key is chosen as the primary key, while others become alternate keys.
Possible Candidate Keys are:
- Student_ID
- Phone
Alternate Key
An alternate key in a database is a candidate key that was not chosen as the primary key. It is still unique and can be used to identify records, but the database administrator selects only one primary key, leaving the remaining candidate keys as alternate keys. Each value in an alternate key must be unique and it cannot contain NULL values. The alternate key can still be used to reference records uniquely.
Since they are unique but not selected as the primary key, the possible Alternate Keys are:
- Phone
Unique Key
A unique key in a database is a constraint that ensures all values in a column (or a combination of columns) are distinct, preventing duplicate entries. It helps maintain data integrity by enforcing uniqueness across records. Unlike a primary key or candidate key, a unique key can contain one NULL value. A table can have more than one unique key, but only one primary key. Unique keys improve search performance by creating indexes.
Since they ensure uniqueness but allow one NULL value, the possible unique keys are:
- Phone
One thing to note is that phone numbers may change over time, and it is not recommended as a unique identifier in the long term compared to email.
Composite Key
A composite key is a key in a database that consists of two or more columns used together to uniquely identify a record in a table. Unlike a primary key that may consist of a single column, a composite key is necessary when no single column alone can ensure uniqueness. The combination of values across these columns must be unique for each row.
Consider an ‘Orders’ table as an example:
Customer_ID | Product_ID | Quantity |
101 | 5001 | 2 |
102 | 5002 | 1 |
101 | 5002 | 3 |
Here, neither ‘Customer_ID’ nor ‘Product_ID’ alone can uniquely identify a row. The combination of the two serves as a composite primary key, ensuring uniqueness.
Free ERD Templates with Primary and Foreign Keys
Primary keys and foreign keys play distinct but complementary roles in database design. Together, these keys support a structured, relational database system, making data retrieval, organization, and maintenance seamless. Understanding their differences helps in designing robust databases that promote consistency and reliability.
If you’re ready to put your newfound knowledge to the test, visit Creately and start designing databases!
FAQs about Primary and Foreign Keys
Can a key be both primary and foreign?
Can a foreign key be null?
Why Allow NULL values in Foreign Keys?
- Optional Relationships – If a relationship between two tables is not mandatory, a foreign key can be left NULL.
- Data Migration – When inserting records, sometimes the related data is not available yet.
- Unassigned Values – Some records may not have a valid reference in the parent table.
What is the role of primary and foreign keys in ERD relationships?
How to identify primary keys?
Identifying a primary key in a database involves checking for attributes that uniquely identify each row. Here’s how you can determine the primary key.
- Uniqueness – The column(s) must have unique values for every row.
- Non-nullability – A primary key cannot contain NULL values.
- Minimality – It should be the smallest set of attributes needed for uniqueness.
Resources
Cleve, A. and Hainaut, J.-L. (2012). What Do Foreign Keys Actually Mean? [online] IEEE Xplore. doi:https://doi.org/10.1109/WCRE.2012.39.
Wilmot, R.B. (1984). Foreign keys decrease adaptability of database designs. Communications of the ACM, 27(12), pp.1237–1243. doi:https://doi.org/10.1145/2135.2142.
Zhang, M., Hadjieleftheriou, M., Ooi, B.C., Procopiuc, C.M. and Srivastava, D. (2010). On multi-column foreign key discovery. Proceedings of the VLDB Endowment, 3(1-2), pp.805–814. doi:https://doi.org/10.14778/1920841.1920944.
