SQL Server Create Table 2 Primary Key

by

Recently, my team was required to create tables with multiple primary keys in SQL Server. We analyzed the requirement and identified a few simple approaches to achieve this. In this article, I will walk you through multiple ways to do this task. Remember that this is known as Composite Primary Key.

Before starting, let me try to give a basic idea of what a composite primary key is.

What is a Composite Primary Key?

It is a unique identity for a table that consists of two or more columns. This is required when a single column cannot uniquely identify a record.

Approach 1: Using the CREATE TABLE Query

Using the query below, we can mention the two columns we want to create during the table creation process.

Example

In the below example, we are creating a TicketBookings table with two primary keys: TicketNumber and BookingDate. Let us execute the SQL query below.

CREATE TABLE TicketBookings (
    TicketNumber VARCHAR(10),
    BookingDate DATE,
    PassengerID INT,
    PSeatNumber VARCHAR(5),
    CONSTRAINT PK_TicketBookings PRIMARY KEY (TicketNumber, BookingDate)
);

The command was completed successfully, as shown in the screenshot below.

SQL Server Create Table 2 Primary Key

Check out the screenshot below; the two primary keys were created successfully.

how to add two primary key in one table sql

Approach 2: First, create the table and then alter the composite primary key

In this approach, we need to create the table first, and then we can update the composite primary key to the newly created table.

Example

In the following example, we create a new table, TicketBookings, with the query below.

CREATE TABLE TicketBookings (
    TicketNumber VARCHAR(10) not null,
    BookingDate DATE not null,
    PassengerID INT,
    PSeatNumber VARCHAR(5)
);

The table was created successfully after executing the above query, as shown in the screenshot below.

can a table have two primary keys in sql

With the query below, let us alter the newly created table with the two primary keys, TicketNumber and BookingDate.

ALTER TABLE TicketBookings
ADD CONSTRAINT PK_TicketBookings 
PRIMARY KEY (TicketNumber, BookingDate);

After executing the above query, the command executed successfully, and the composite primary key was created successfully, as shown in the screenshot below.

create table composite primary key
SQL Create Table 2 Primary Key

Check out Cannot Define Primary Key Constraint On Nullable Column In Table in case you get any error after executing the above query.

FAQs

Can a table have two primary keys in SQL

Answer: Yes, you can create a composite primary key for multiple columns.

Conclusion

Using any of the approaches mentioned in this article, creating tables with composite primary keys in SQL Server is easy. Now, you choose the best strategy based on your requirements.

You may also like following the articles below.