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'.
[EvAffiliation]
or[table]
?)PRIMARY KEY
constraints. This is not allowed.PRIMARY KEY
and aUNIQUE
constraint. Or 2 (or more actually)UNIQUE
constraints if you want to and no PRIMARY KEY.