Create Composite Primary Key with Multiple Columns in SQL Server
In your database design on table level, database administrators can create composite primary key which is consists of two or more columns in that table. In SQL Server database developers or administrators can choose to use composite keys (composite primary key or indexes on multiple columns) instead of defining a primary key on a single table column.
If the composite primary key is being created with the table itself, it is possible to add the primary key definition within the table creation DDL statement. But if you are trying to create a primary key (even if composite primary key or not) on a table without any primary key, ALTER TABLE ADD CONSTRAINT structure can be used for modification on target database table.
This SQL tutorial shares Transact-SQL codes to help developers can DBA's to create composite primary key with multiple columns on a SQL Server database table.
Let's start our SQL tutorial with a simple case. In this example, in our database design we require a new table named "CompositePKTable". And we realized that it is better to configure the primary key to be formed of two columns namely column1 and column2 table columns.
In this case, we can define the composite primary key within the table DDL command as follows.
CREATE TABLE CompositePKTable
(
column1 INT NOT NULL,
column2 INT NOT NULL,
column3 VARCHAR(50),
CONSTRAINT PK_CompositePKTable PRIMARY KEY CLUSTERED (column1, column2)
-- CONSTRAINT PK_CompositePKTable PRIMARY KEY NONCLUSTERED (column1, column2)
)
Database designer can decide if the primary key of the table will be created as clustered or nonclustered. And of course, it is also possible to use more than two columns within the primary key definition.
If you look at the above screenshot from SQL Server 2014 Management Studio (SSMS) showing the database table details, under Columns node you will see the column1 and column2 are marked with PK (primary key) identifier.
Also under the Keys node, the primary key is listed with its given name in the table create DDL script.
The primary key is also listed in the Indexes node.
Instead of the above CREATE TABLE script, developers and database administrators can use the below sql codes too. The result is very similar, but this time SQL Server decides the name of the primary key which may not be consistent with your company's naming standards.
CREATE TABLE CompositePKTable
(
column1 INT NOT NULL,
column2 INT NOT NULL,
column3 VARCHAR(50),
PRIMARY KEY NONCLUSTERED(column1, column2)
-- PRIMARY KEY CLUSTERED(column1, column2)
)
Here is how SQL Server handles the primary key and related
Second case in our SQL tutorial is to show how to add primary key to an existing database table.
Following table does not have a primary key when it is first created.
CREATE TABLE DatabaseList (
database_name SYSNAME NOT NULL,
server_name SYSNAME NOT NULL,
[description] NVARCHAR(4000)
)
Using ALTER TABLE ADD CONSTRAINT command, SQL developers can modify an existing database table to create a new primary key consisting two or more columns to build a composite primary key. Here is a sample SQL script which creates composite primary key formed from two table columns.
ALTER TABLE DatabaseList ADD CONSTRAINT PK_DatabaseList PRIMARY KEY (database_name, server_name)
A third case we should consider in this SQL Server tutorial is to add a composite primary key on a table where a primary key is already defined.
Assume that our table is created using the following DDL (Data Definition Language) statement.
CREATE TABLE DatabaseList (
id INT IDENTITY(1,1) NOT NULL,
database_name SYSNAME NOT NULL,
server_name SYSNAME NOT NULL,
[description] NVARCHAR(4000)
PRIMARY KEY NONCLUSTERED(id)
)
If the SQL Server database administrator wants to change the primary key with a new one, first of all the existing primary key should be dropped. Of course if the database admin or developer knows the primary key name, it is easy to drop the constraint defined on the target table. Look at the below SQL command which drops the primary key named by the SQL Server engine
ALTER TABLE DatabaseList DROP CONSTRAINT PK__Database__3213E83EFBBF1DC0
I know database developers are not always lucky to know the primary key name. But in order to get the primary key name for a database table programmatically, SQL developers can execute a SELECT statement over the INFORMATION_SCHEMA.TABLE_CONSTRAINTS system management view.
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'DatabaseList' AND CONSTRAINT_TYPE = 'PRIMARY KEY'
After the primary key name is fetched automatically from system view INFORMATION_SCHEMA.TABLE_CONSTRAINTS, developers can build a dynamic SQL script which first drops the existing primary key and then creates the new composite primary key.
Below dynamic SQL script first finds the existing primary key name. Then a SQL command is built which will drop the primary key constraint. This DROP CONSTRAINT statement is executed using sp_executesql procedure. As the last step, the composite primary key which includes two columns as primary key fields is created.
DECLARE @PrimaryKeyName sysname
DECLARE @SQL nvarchar(max)
SELECT @PrimaryKeyName = CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'DatabaseList' AND CONSTRAINT_TYPE = 'PRIMARY KEY'
SET @SQL = 'ALTER TABLE DatabaseList DROP CONSTRAINT ' + @PrimaryKeyName
EXEC sp_executesql @SQL
ALTER TABLE DatabaseList ADD CONSTRAINT PK_DatabaseList PRIMARY KEY (database_name, server_name)
I hope this SQL tutorial will be useful for some database administrators and SQL programmers.