CODEWITHSUNDEEP
sqlsql-serverprimary-keyunique-constraint
Benjamin Hodgson
Benjamin Hodgson

Reputation: 44614

Why can't I specify both a Primary Key and a Unique Constraint on the same table?

I'm trying to create a table:

CREATE TABLE [MyTable]
(
    [Id] [int] IDENTITY,
    [Column1] [int] NOT NULL,
    [Column2] [int] NOT NULL

    CONSTRAINT [PK_MyTable_Id] PRIMARY KEY ([Id])
    CONSTRAINT [UQ_MyTable_Column1_Column2] UNIQUE ([Column1], [Column2])
)

This script fails with the error:

Both a PRIMARY KEY and UNIQUE constraint have been defined for column 'Column2', table 'MyTable'. Only one is allowed.

Why is this restriction enforced? How can I create a table with these properties?

Upvotes: 9

Views: 5661

Answers (3)

Iain
Iain

Reputation: 36

Or put a comma between the primary key declaration and unique constraint declaration:

CREATE TABLE [MyTable]
(
    [Id] [int] IDENTITY,
    [Column1] [int] NOT NULL,
    [Column2] [int] NOT NULL

    CONSTRAINT [PK_MyTable_Id] PRIMARY KEY ([Id]),
    CONSTRAINT [UQ_MyTable_Column1_Column2] UNIQUE ([Column1], [Column2])
)

Upvotes: 2

CynicalSection
CynicalSection

Reputation: 704

You missed a comma after the primary key constraint. 

CREATE TABLE [MyTable]
(
    [Id] [int] IDENTITY,
    [Column1] [int] NOT NULL,
    [Column2] [int] NOT NULL

    CONSTRAINT [PK_MyTable_Id] PRIMARY KEY ([Id]),
    CONSTRAINT [UQ_MyTable_Column1_Column2] UNIQUE ([Column1], [Column2])
)

Upvotes: 15

mohan111
mohan111

Reputation: 8865

CREATE TABLE [MyTable]
(
    [Id] [int] IDENTITY,
    [Column1] [int] NOT NULL,
    [Column2] [int] NOT NULL

    CONSTRAINT [PK_MyTable_Id] PRIMARY KEY ([Id])

)

  ALTER TABLE [MyTable] ADD CONSTRAINT [UQ_MyTable_Column1_Column2] UNIQUE ([Column1], [Column2])

Upvotes: 3

Related Questions