How to Create and Use Composite Primary Keys in SQL Server 2008?

Composite Primary Keys in SQL Server 2008

When designing tables in SQL Server, it is essential to define a primary key for efficient data retrieval and integrity maintenance. A primary key uniquely identifies each row in a table. In certain scenarios, it may be necessary to establish a composite primary key, which consists of multiple columns combined.

Creating a Composite Primary Key

The following steps guide you through creating a composite primary key in SQL Server 2008:

1. Define the table structure: First, define the table's columns and data types.

create table my_table (
    column_a integer not null,
    column_b integer not null,
    column_c varchar(50)
);

2. Specify the composite primary key: Use the PRIMARY KEY constraint to define the composite key. The primary key may comprise multiple columns separated by commas.

ALTER TABLE my_table 
ADD CONSTRAINT PK_my_table PRIMARY KEY (column_a, column_b);

This creates a primary key consisting of the column_a and column_b columns.

Example:

For instance, in a hypothetical "Orders" table containing columns like OrderID, CustomerID, and ProductID, you might set the OrderID as the primary key. However, to ensure uniqueness further, you could create a composite primary key using OrderID and CustomerID.

CREATE TABLE Orders (
    OrderID int NOT NULL,
    CustomerID int NOT NULL,
    ProductID int NOT NULL,
    OrderDate datetime,
    PRIMARY KEY (OrderID, CustomerID)
);

By implementing a composite primary key, you guarantee that each combination of OrderID and CustomerID is unique within the "Orders" table.

The above is the detailed content of How to Create and Use Composite Primary Keys in SQL Server 2008?. For more information, please follow other related articles on the PHP Chinese website!