How to create a table with 2 primary key in SQL Server?

Question

I want to insert a record like below

ID  VendorName  RequestNo   VendorCode  ChequeDateSearch         Description    StoreID
-------------------------------------------------------------------------------------
1   John        1011        1021        2017-10-25 00:00:00.000         descr       6000
2   michael     1011        1022        2017-10-25 00:00:00.000         descr       6000
3   Abraham     1011        1023        2017-10-25 00:00:00.000         descr       6000

Note

It should not accept duplicate VendorCode for same RequestNo

In My table ID,RequestNo and VendorCode should be primary key. ID is auto increment, and the RequestNo and VendorCode is user specification

CREATE TABLE [dbo].[CheqVendorSearch](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [VendorName] [nvarchar](max) NULL,
    [RequestNo] [varchar](50) Not NULL,
    [VendorCode] [varchar](50) NOT NULL,
    [ChequeDateSearch] [datetime] NULL,
    [Description] [nvarchar](max) NULL,
    [StoreID] [varchar](10) NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC,
    [RequestNo] ASC,
    [VendorCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

But this table is accepting the duplicate for RequestNo and VendorCode like below

ID  VendorName  RequestNo   VendorCode  ChequeDateSearch         Description    StoreID
-------------------------------------------------------------------------------------
1   John        1011        1023        2017-10-25 00:00:00.000         descr       6000
2   michael     1011        1023        2017-10-25 00:00:00.000         descr       6000
3   Abraham     1011        1023        2017-10-25 00:00:00.000         descr       6000

Radim Bača · Answer 1 · 2017-10-10T08:34:10.877

I believe that you just need the UNIQUE constraint in your table definition.

CREATE TABLE [dbo].[CheqVendorSearch](
    [ID] [bigint] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [VendorName] [nvarchar](max) NULL,
    [RequestNo] [varchar](50) Not NULL,
    [VendorCode] [varchar](50) NOT NULL,
    [ChequeDateSearch] [datetime] NULL,
    [Description] [nvarchar](max) NULL,
    [StoreID] [varchar](10) NULL,
    CONSTRAINT UQ_vendor_request UNIQUE
    (
        [RequestNo] ,
        [VendorCode] ASC
    )
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The major problem in your solution is that each row is uniquely identified by the ID, therefore, it is useless to have RequestNo and VendorCode as a part of the primary key.

Aman Jain · Answer 2 · 2017-10-10T08:38:13.787

You are trying to make a Composite Primary key. The Composite Primary key enforces to make the combination of Prime Attribute(ID, RequestNo and VendorCode) to be unique. To make RequestNo and VendorCode to be unique too, you have to add Unique Clause on both of them.

CREATE TABLE [dbo].[CheqVendorSearch](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[VendorName] [nvarchar](max) NULL UNIQUE,
[RequestNo] [varchar](50) Not NULL UNIQUE,
[VendorCode] [varchar](50) NOT NULL,
[ChequeDateSearch] [datetime] NULL,
[Description] [nvarchar](max) NULL,
[StoreID] [varchar](10) NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC,
    [RequestNo] ASC,
    [VendorCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

score 0 · Answer 3 · answered Oct 10 '17 at 08:27

You define the PK with ID, RequestNo and VendorCode - therefore your mentioned example is not treated as duplicate. However, remove the ID from the PK and it should work fine. Since ID is populated by identity, this should be no problem. If for one reason or the other you have to insert data via identity_insert I suggest to add a unique key to ypur table covering the column ID only.

score 0 · Answer 4 · answered Oct 10 '17 at 08:31

A composite key apply a unique value for the combination of the 3 columns, not each column individually (1,John,1011) is unique from (2,Michael,1011). You need to add a unique key on the RequestNo column if you want that to be unique too. On a side note have varchar data types as a PK isn't always a good idea and can RequestNo not be an int?

How to create a table with 2 primary key in SQL Server?

4 Answers4