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'.