|
|
Error Message
Server: Msg 8110, Level 16, State 0, Line 1
Cannot add multiple PRIMARY constraints to table
'<Table Name>'.
Causes
Constraints allow the definition of the automatic enforcement of integrity in a database. Constraints define the rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. SQL Server supports 5 types of constraints, namely, NOT NULL constraint, CHECK constraint, UNIQUE constraint, PRIMARY KEY constraint and FOREIGN KEY constraint.
|
The PRIMARY KEY constraint identifies the column or set of columns that have values that uniquely identify a row in a table. No two rows in a table can have the same primary key value and NULL values for any columns defined as the primary key are not allowed. As a good database design practice, it is recommended that each table has a primary key defined.
A table can have only one PRIMARY KEY constraint defined and the columns that participate in the PRIMARY KEY constraint cannot have NULL values. A PRIMARY KEY constraint can be defined with just a single column or it can be defined with a set of columns, all of which should have the NOT NULL constraint set.
This error message can be encountered when defining more than one column as the PRIMARY KEY and these are incorrectly specified in the CREATE TABLE command, as can be seen in the following CREATE TABLE statement:
CREATE TABLE [dbo].[DNC] (
[AreaCode] CHAR(3) NOT NULL PRIMARY KEY,
[PhoneNumber] CHAR(7) NOT NULL PRIMARY KEY,
[PhoneType] CHAR(1)
)
Msg 8110, Level 16, State 0, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'dbo.DNC'.
The DNC table in this example contains the list of phone numbers that are defined in the Do-Not-Call list. The complete phone number is separated by the 3-digit area code and the 7-digit phone number and the combination of these columns make up a unique phone number and serves as the PRIMARY KEY for this table.
Another way of getting this error message is through the same incorrect CREATE TABLE statement but for a table that has 2 possible PRIMARY KEY candidates. As an example, a table containing the U.S. states can have a 2-character state code (CA, FL) or a 3-character state code (CAL, FLA) that can be both considered as primary keys:
CREATE TABLE [dbo].[USStates] (
[Code2] CHAR(2) NOT NULL PRIMARY KEY,
[Code3] CHAR(3) NOT NULL PRIMARY KEY,
[Name] VARCHAR(100)
)
Msg 8110, Level 16, State 0, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'dbo.USStates'.
Solution / Work Around:
There are a couple of ways of avoiding this error. The first method is through the proper way of defining a PRIMARY KEY constraint through the CREATE TABLE statement. The following CREATE TABLE statement is the proper way of defining a PRIMARY KEY comprising of more than one column:
CREATE TABLE [dbo].[DNC] (
[AreaCode] CHAR(3) NOT NULL,
[PhoneNumber] CHAR(7) NOT NULL,
[PhoneType] CHAR(1),
CONSTRAINT [PK_DNC] PRIMARY KEY CLUSTERED ( [AreaCode], [PhoneNumber] )
)
The second method of defining a PRIMARY KEY constraint comprised of more than one column is through the ALTER TABLE ADD CONSTRAINT statement as can be seen from the following:
CREATE TABLE [dbo].[DNC] (
[AreaCode] CHAR(3) NOT NULL,
[PhoneNumber] CHAR(7) NOT NULL,
[PhoneType] CHAR(1)
)
GO
ALTER TABLE [dbo].[DNC]
ADD CONSTRAINT [PK_DNC] PRIMARY KEY CLUSTERED ( [AreaCode], [PhoneNumber] )
It is important that the columns that make up the PRIMARY KEY are defined with the NOT NULL constraint as this is one of the requirements when defining columns as part of the PRIMARY KEY constraint.
As for the second example (U.S. states table), since a table can only have one PRIMARY KEY defined and this particular table has 2 possible candidate keys, one of the columns has to be selected as the PRIMARY KEY and the other column can be defined as a UNIQUE INDEX.
CREATE TABLE [dbo].[USStates] (
[Code2] CHAR(2) NOT NULL PRIMARY KEY,
[Code3] CHAR(3) NOT NULL,
[Name] VARCHAR(100)
)
CREATE UNIQUE INDEX [IX_USStates_Code3] ON [dbo].[USStates] ( [Code3] )
GO
|