0

I am trying to add constraints and I think i have an error in the code as its saying i cant add mutlple constrints. What is best way to add mutlple constraints

I am using MSQL Server 2008 r2

CREATE TABLE [erp].[table](
    [SysRevID] [timestamp] NOT NULL,
    [SysRowID] [uniqueidentifier] ROWGUIDCOL  NOT NULL DEFAULT (CONVERT([uniqueidentifier],CONVERT([binary](10),newid(),(0))+CONVERT([binary](6),getutcdate(),(0)),(0))),
    [Company] [nvarchar] (8) NOT NULL DEFAULT (''),
    --THE ABOVE ARE DEFAULT FIELDS.  LEAVE THEM ALONE.  ADD FIELDS BELOW
    [AffilationNum] [int] NOT NULL DEFAULT ((0)),   
    [VendorNum] [int] NOT NULL ,
    [CreationClass]   [nvarchar] (10),  
    FOREIGN KEY ( VendorNum ) REFERENCES Vendor (VendorNum),
    PRIMARY KEY (AffilationNum),
CONSTRAINT [idxEvAffiliation] PRIMARY KEY CLUSTERED 
(
    [Company] ASC,
    [AffilationNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]

go

 CREATE NONCLUSTERED INDEX [idxAffilationInterestedParty] ON [erp].[EvAffiliation]
 (
 [Company] ASC,
 [VendorNum] ASC
 )


GO

ALTER TABLE [erp].[EvAffiliation] SET (LOCK_ESCALATION = DISABLE)
GO

Edit I am not sure how to create an additional index i dont want the second one to be on primary key its just an additional non clusted index i want for the second one.

This is the errror i am getting i am trying to create one pimary index and one non clustered index

Msg 8110, Level 16, State 0, Line 2 Cannot add multiple PRIMARY KEY constraints to table 'erp.EvAffiliation'.

  • Which of the 3 statements throws the error? What error exactly do you get? (and is the table named [EvAffiliation] or [table]?) – ypercubeᵀᴹ Nov 20 '15 at 11:14
  • Oh, it's that you are trying to add 2 PRIMARY KEY constraints. This is not allowed. – ypercubeᵀᴹ Nov 20 '15 at 11:16
  • You can have a PRIMARY KEY and a UNIQUE constraint. Or 2 (or more actually) UNIQUE constraints if you want to and no PRIMARY KEY. – ypercubeᵀᴹ Nov 20 '15 at 11:19
  • @ypercube please see edit below code i not wanting to i need a second index just not on the primary key – kymberly Nov 20 '15 at 11:19
  • @ypercube can you please provde an example of the constraint so i can add a second index thanks and il mark as answer – kymberly Nov 20 '15 at 11:19
2

The problem is with the erp.table, you are defining primary keys multiple times.

Try the following code:

CREATE TABLE [erp].[table](
    [SysRevID] [timestamp] NOT NULL,
    [SysRowID] [uniqueidentifier] ROWGUIDCOL  NOT NULL DEFAULT (CONVERT([uniqueidentifier],CONVERT([binary](10),newid(),(0))+CONVERT([binary](6),getutcdate(),(0)),(0))),
    [Company] [nvarchar] (8) NOT NULL DEFAULT (''),
    --THE ABOVE ARE DEFAULT FIELDS.  LEAVE THEM ALONE.  ADD FIELDS BELOW
    [AffilationNum] [int] NOT NULL DEFAULT ((0)),
    [VendorNum] [int] NOT NULL ,
    [CreationClass]   [nvarchar] (10),
    FOREIGN KEY ( VendorNum ) REFERENCES Vendor (VendorNum),
CONSTRAINT [idxEvAffiliation] PRIMARY KEY CLUSTERED 
(   [Company] ASC,
    [AffilationNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]

Refer to Primary Key Constraints and Composite Primary Keys.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.