Differences between a Primary Key and a Foreign Key

Differences Between a Primary Key and a Foreign Key

In relational database design, understanding the roles of primary keys and foreign keys is essential for maintaining data integrity and establishing relationships between tables. These keys serve distinct but complementary functions, each playing a crucial role in organizing and linking data within a database. While a primary key uniquely identifies each record within a table, a foreign key connects records across different tables, ensuring that related data is consistently and accurately represented. Together, they form the backbone of relational databases, enabling efficient data management and retrieval.

Primary Key

Uniqueness: A primary key is a unique identifier for each record in a database table. It ensures that no two rows in the table can have the same primary key value, maintaining the uniqueness of each record.
Single Table: The primary key is specific to the table in which it is defined. It is used to identify records within that table only.
Not Null: A primary key cannot contain `NULL` values. Every record in the table must have a valid and unique primary key value.
Example: In a table of employees, an `EmployeeID` column could serve as the primary key, uniquely identifying each employee.

Foreign Key

Relationship: A foreign key is a field (or a set of fields) in one table that uniquely identifies a row of another table. It creates a link between the two tables, establishing a relationship between them.
Multiple Tables: Foreign keys are used to enforce referential integrity between two related tables, meaning that the value in the foreign key column must match a value in the primary key column of the referenced table.
Can Be Null: Foreign key columns can contain `NULL` values if the relationship is optional. This allows records in the table to exist without a corresponding record in the referenced table.
Example: In a table of orders, a `CustomerID` column could serve as a foreign key, linking each order to a specific customer in the `Customers` table, where `CustomerID` is the primary key.

Here are some SQL examples that highlight the differences between a primary key and a foreign key:

Example 1: Defining a Primary Key

In this example, the `EmployeeID` column in the `Employees` table is set as the primary key. This ensures that each employee has a unique identifier.

Example 2: Defining a Foreign Key

In this example, the `Orders` table includes a `CustomerID` column, which is set as a foreign key. This foreign key references the `CustomerID` primary key in the `Customers` table, linking each order to a specific customer.

```sql CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, CustomerName VARCHAR(100), ContactEmail VARCHAR(100) ); CREATE TABLE Orders ( OrderID INT PRIMARY KEY, OrderDate DATE, CustomerID INT, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ); ```

Example 3: Inserting Data with Primary and Foreign Keys

This example shows how primary and foreign keys work together when inserting data. The `CustomerID` in the `Orders` table must match an existing `CustomerID` in the `Customers` table.

```sql -- Insert a customer INSERT INTO Customers (CustomerID, CustomerName, ContactEmail) VALUES (1, 'John Doe', 'john.doe@example.com'); -- Insert an order linked to that customer INSERT INTO Orders (OrderID, OrderDate, CustomerID) VALUES (101, '2024-08-12', 1); ```

Example 4: Foreign Key Constraints

If you try to insert an order with a `CustomerID` that does not exist in the `Customers` table, the database will prevent it, enforcing referential integrity.

```sql -- This will fail because there is no CustomerID = 2 in the Customers table INSERT INTO Orders (OrderID, OrderDate, CustomerID) VALUES (102, '2024-08-13', 2); ```

Example 5: Cascading Deletes with Foreign Keys

You can set a foreign key to cascade deletes, meaning that if a record in the primary key table is deleted, the related records in the foreign key table are automatically deleted.

```sql CREATE TABLE Orders ( OrderID INT PRIMARY KEY, OrderDate DATE, CustomerID INT, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE ); ```

In this setup, if you delete a customer from the `Customers` table, all orders associated with that customer will also be deleted automatically.

These examples illustrate how primary keys uniquely identify records within a table, while foreign keys create relationships between tables, enforcing consistency and referential integrity in the database.

In summary, a primary key uniquely identifies records within its own table, while a foreign key establishes a link between two tables, referencing the primary key of another table.

Differences between a Primary Key and a Foreign Key

Get started with Secoda

How to evaluate a data catalog

Differences Between a Primary Key and a Foreign Key

Primary Key

Foreign Key

Keep reading

Secoda vs Atlan: A comparative study on data compliance features

Data literacy and adoption in your organization: Secoda vs Atlan

Scalability and performance: Comparative review of Secoda and Atlan

Get started in minutes

Product

Solutions

Use cases

Resources

Company

Social