CODEWITHSUNDEEP
sql-server
Roy.hpr
Roy.hpr

Reputation: 59

How insert rows into table with two primary key?

I have created following table:

CREATE TABLE [dbo].[tblArchLogDetail](
    [RecordID] [numeric](18, 0) NOT NULL,
    [TableName] [varchar](30) NOT NULL,
    [TotalRecords] [int] NULL,
    [ArchivedRecords] [int] NULL,
 CONSTRAINT [PK_tblArchLogDetail] PRIMARY KEY CLUSTERED 
(
    [RecordID] ASC,
    [TableName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

So, how can I insert rows into this table? I keeps getting error "Violation of PRIMARY KEY constraint". This is my sample code:

Insert into tblArchLogDetail values(54,'tblAuditLogin',13128,0)

Upvotes: 1

Views: 3367

Answers (2)

Dean Kuga
Dean Kuga

Reputation: 12119

What you have here is a so called "composite" primary key.

This means that any combination of values you chose to make up your composite primary key must be unique.

The error you are getting only means that you already have a record in that table whose RecordId is 54 and TableName is 'tblAuditLogin'.

Upvotes: 1

tommy_o
tommy_o

Reputation: 3783

I think what you're trying to do is add an automatically incrementing integer in RecordID field. If so, make it an identity field:

CREATE TABLE [dbo].[tblArchLogDetail](
    [RecordID] [numeric](18, 0) IDENTITY NOT NULL,
    [TableName] [varchar](30) NOT NULL,
    [TotalRecords] [int] NULL,
    [ArchivedRecords] [int] NULL,
 CONSTRAINT [PK_tblArchLogDetail] PRIMARY KEY CLUSTERED 
(
    [RecordID] ASC,
    [TableName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

If that's not your intent and you really want duplicate records in RecordID and TableName, put an identity field before RecordID and add it to the PRIMARY KEY constraint:

CREATE TABLE [dbo].[tblArchLogDetail](
    [ID] [bigint] IDENTITY NOT NULL
    [RecordID] [numeric](18, 0) NOT NULL,
    [TableName] [varchar](30) NOT NULL,
    [TotalRecords] [int] NULL,
    [ArchivedRecords] [int] NULL,
 CONSTRAINT [PK_tblArchLogDetail] PRIMARY KEY CLUSTERED 
(
    [ID] ASC,
    [RecordID] ASC,
    [TableName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

If those two solutions don't fix it, you have additional details that are required to answer this question.

Upvotes: 0

Related Questions