Menu
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?
931
A table can have only one primary key whereas there can be multiple unique key on a table.
PRIMARY KEY constraint differs from the UNIQUE constraint in that; you can create multiple UNIQUE constraints in a table, with the ability to define only one SQL PRIMARY KEY per each table. Another difference is that the UNIQUE constraint allows for one NULL value, but the PRIMARY KEY does not allow NULL values.
You can define multiple unique keys in one table, as there may be multiple columns that store only unique values. A primary key and a unique key can both be present in a table (as shown in some of the examples); however, it makes no sense to define both a unique key and a primary key on the same column(s).
Yes, it can.
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])
)
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])
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
