How to create composite primary key with multiple columns

First of all, the word composite is not a keyword in SQL. When we create a primary key with single column of a table then we normally called primary key in a table and when we create a primary key with multiple columns of a table then we called it Composite Primary Key.  So Composite Primary Key is not a keyword or not a constraint in SQL Server.

Primary key constraint uniquely identifies each record of the table and it can not have NULL values. Every table should have one primary key to identify each record separately and even SQL Server supports only one primary key in one table.

Second, we can create constraints  or keys  at the time of creation of a new table and/or after creation of a existing table. Lets go for an example-

At the time of table creation to add a primary key or composite primary key, the syntax is the following-

CREATE TABLE table_name
(
   Column_1 char(10)  NOT NULL  constraint Constraint_Name PRIMARY KEY CLUSTERED,
     Column_2  varchar(250)   NOT NULL
)


Lets take another example -

CREATE TABLE Table_Name_1 (
        Column_1 char(10) NOT NULL,
        Column_2 nvarchar(15) NOT NULL,
        Column_3 date NOT NULL,
        Column_4 nvarchar(15) NULL,
        Column_5 nvarchar(max) NULL,
        Column_5 money NOT NULL CONSTRAINT DF_Column_8 DEFAULT ((0)),
        Column_7 datetime NOT NULL CONSTRAINT DF_Column_9 DEFAULT (getdate()),
        Column_8 nchar(1) NOT NULL CONSTRAINT DF_Column_10 DEFAULT ('a'),
        CONSTRAINT PK_Column_1 PRIMARY KEY CLUSTERED (Column_1 ASC),
        CONSTRAINT UNK_Column_2_And_4 UNIQUE NONCLUSTERED (Column_2 ASC, Column_4 ASC),
)

And after creation of the table the syntax will be following-

alter table Table_Name_1
add constraint PK_Const_Name PRIMARY KEY CLUSTERED (Column_1, Column_2)

Likewise we can create or alter unique key constraint in the same way with single column as well as multiple columns. Unique key constraint also have unique values in each records but it accepts one NULL value which is unique for that column.

alter table Table_Name_1
add constraint UNK_Bill_No_Test_code unique (Column_2, Column_3)

Tricks: After creating tables in the database in graphics mode you can check the SQL statement how it is created see the below screenshot

 

Primary key also is used for relation building with each other table(s).  When we create tables for a particular project, we need to plan a lot of things before creating tables for the projects that which will be the master tables and which will be the transaction tables. So to know all the details properly we should take an example of one of my live projects that how to plan for a project.

If you are interested that "how to plan for a project and how to do it" then give a comment as your demand and your interest. I will definitely write a blog for you with examples.