Sql-server – Adding multiple constraints

constraintsql server

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

Best Answer

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.

Related Question