In Relational databases, Keys are an essential aspect of relational databases, as they help identify and organize data. Primary keys and composite keys are two types of keys in SQL that are used to establish relationships between tables.
Table of Contents
Primary Key
A primary key is a column or set of columns in a table that uniquely identifies each row. It is a single column that uniquely identifies each record in the table. It can’t contain null values, and each value must be unique. A primary key constraint is used to enforce this rule.
Let’s consider an example to understand Primary Key. Suppose we have a table named “Students” with the following columns: Student_ID, Name, Age, and Department.
| Student_ID | Name | Age | Department |
|---|---|---|---|
| 1 | John | 20 | CSE |
| 2 | Amy | 21 | IT |
| 3 | Mike | 19 | CSE |
| 4 | Sara | 22 | ECE |
Here, Student_ID is a unique identifier that can’t be null and can’t be duplicated.
Composite Key
A composite key is a combination of two or more columns that together uniquely identify a row in a table. It is useful when no single column can uniquely identify a record. A composite key constraint is used to enforce this rule.
Let’s consider an example to understand Composite Key. Suppose we have a table named “Orders” with the following columns: Order_ID, Product_ID, and Customer_ID.
| Order_ID | Product_ID | Customer_ID |
|---|---|---|
| 100 | 1 | 10 |
| 101 | 2 | 20 |
| 102 | 1 | 30 |
| 103 | 3 | 20 |
Here, we can’t use any single column to uniquely identify each record in the table. Instead, we use a combination of Product_ID and Customer_ID as a composite key.
Differences between Primary Key and Composite Key
The primary key is a single column that uniquely identifies each record in the table. It can’t contain null values and must be unique. On the other hand, a composite key is a combination of two or more columns that together uniquely identify a row in a table. It is useful when no single column can uniquely identify a record.
Another significant difference between primary key and composite key is that the primary key is used to establish a relationship between two tables, while a composite key is used to create a relationship between multiple columns within the same table.
Conclusion
In conclusion, primary keys and composite keys are essential aspects of SQL databases that help organize and identify data. A primary key is a single column that uniquely identifies each record in the table, while a composite key is a combination of two or more columns that together uniquely identify a row in a table. Understanding the differences between these two keys is crucial when designing and implementing relational databases.