Creating a composite primary key on a table involves defining a primary key that consists of two or more columns, rather than just one. A composite primary key is useful when a single column is not unique enough to serve as a primary key.
Here is how you can create a composite primary key using SQL, depending on whether you are creating a new table or modifying an existing one.
Creating a New Table with a Composite Primary Key
When you are creating a new table, you can define a composite primary key in the CREATE TABLE statement. Here's an example:
CREATE TABLE Orders (
OrderID INT,
ProductID INT,
Quantity INT,
PRIMARY KEY (OrderID, ProductID)
);
In this example, the Orders table has a composite primary key made up of the OrderID and ProductID columns. This means that the combination of values in these two columns must be unique for each row in the table.
Adding a Composite Primary Key to an Existing Table
If you need to add a composite primary key to an existing table, you can use the ALTER TABLE statement as follows:
ALTER TABLE Orders
ADD PRIMARY KEY (OrderID, ProductID);
Important Considerations
- Uniqueness: The combination of the columns in the composite key must be unique for every row in the table.
- Null Values: None of the columns involved in the composite primary key can contain NULL values.
- Referential Integrity: Composite primary keys can also be referenced by foreign keys in other tables.
Example of a Foreign Key Reference
Here's how you could use a composite primary key as a foreign key in another table:
CREATE TABLE OrderDetails (
OrderID INT,
ProductID INT,
UnitPrice DECIMAL(10, 2),
Quantity INT,
FOREIGN KEY (OrderID, ProductID) REFERENCES Orders(OrderID, ProductID)
);
In this case, the OrderDetails table references the composite primary key from the Orders table.
Creating a composite primary key is a common practice when designing a relational database schema to ensure data integrity and uniqueness across multiple columns.