SQL Server – Modifying Primary Key

Background

In past posts, I have spoken of time spent on finding out how unique indexes with ignore_dup_key enabled can help solve some vexing problems.

Posts

  1. Transact SQL – Error Message – SQLState = 23000, Native Error = 2627 – Violation of Primary Key Constraint
  2. Microsoft – SQL Server – unique Index – Pros and Cons (mostly cons in this particular case)

And, a nice rebuke by Paul White, SQL Server MVP

  1. A creative use of IGNORE_DUP_KEY

Reviewing Stats

With recurring bouts of insomnia I was reviewing WordPress stats and seeing which posts users clicked on and found the one about BCP/Ignore Dup Key.

 

Objective

I wanted to see if we could have done better and not have to create a unique index in the first place.

And, based on Stack Overflow, as always, found ways around it.  The particular Q\As are:

  1. Can I set ignore_dup_key on for a primary key?

 

Lab

Create Table

Code


use [tempdb]
go

if schema_id('uniqueIndex') is null
begin

	exec('create schema [uniqueIndex] authorization [dbo]')

end
go

if object_id('uniqueIndex.customer') is not null
begin

	drop table [uniqueIndex].[customer]

end
go

create table [uniqueIndex].[customer]
(

	  [accountID] varchar(30) not null
	, [lastname] varchar(60) not null
	, [firstname] varchar(60) not null

	, constraint [uniqueIndex.PK_Customer]
		primary key clustered
		(
			  [lastname]
			, [firstname]
		)

)
go

create index INDX_ACCOUNT
on [uniqueIndex].[customer]
(
	[accountID]
)
go

select
	   = 'Before'
	, name
	, type_desc
	, is_primary_key
	, [ignore_dup_key]

	from sys.indexes

where object_id = object_id('[uniqueIndex].[customer]')

 

Output

Index-Before

 

Add Data

Code


set nocount on
go

use [tempdb]
go

truncate table [uniqueIndex].[customer]
go

insert into [uniqueIndex].[customer]
(
     [accountID]
   , [lastname]
   , [firstname]
)
select '1', 'Joe', 'Smith'
union all
select '2', 'Paul', 'Young'
union all
select '3', 'Paul', 'Young'

go

Output

Image

CannotInsertDupKey

Text


Msg 2627, Level 14, State 1, Line 10
Violation of PRIMARY KEY constraint 'uniqueIndex.PK_Customer'. Cannot insert duplicate key in object 'uniqueIndex.customer'.
The statement has been terminated.

Remediation

Approach

As we are unable to modify a constraint, no alter constraint syntax, nor touch an implicit index, those indexes created via constraints ( primary key, unique constraint), we will have to rebuild the table and indicate that Duplicate keys should be ignored.

Please note that this only works on MS SQL Server v2008, as the rebuild statement was introduced in that version.

Code

Syntax


ALTER TABLE [table-name]
      REBUILD WITH
      (
        IGNORE_DUP_KEY = ON
      );

Sample


ALTER TABLE [uniqueIndex].[customer]
REBUILD WITH
(
    IGNORE_DUP_KEY = ON
);

Review Indexes


select
          = 'After'
       , name
       , type_desc
       , is_primary_key
       , [ignore_dup_key]

from sys.indexes

where object_id = object_id('[uniqueIndex].[customer]')

Output:

ImagesAfter

 

Add Data


set nocount on
go

use [tempdb]
go

truncate table [uniqueIndex].[customer]
go

insert into [uniqueIndex].[customer]
(
      [accountID]
    , [lastname]
    , [firstname]
)
select '1', 'Joe', 'Smith'
union all
select '2', 'Paul', 'Young'
union all
select '3', 'Paul', 'Young'

go

Review Data

Code


select *
from [uniqueIndex].[customer]

Output

reviewData

Origination

Though not widely noted, one can create primary keys with IGNORE_DUP_KEY during creation.

Code

Sample


create table [dbo].[studentTestAccount]
(
	  [studentID] varchar(100) not null

	, [addedBy] sysname not null
			constraint defaultStudentTestAccountAddedBy
				default system_user

	, [dateAdded] datetime not null
			constraint [defaultStudentTestAccountDateAdded]
				default getdate()

	, constraint PK_StudentTestAccount primary key clustered
		(
			[studentID]
		)
		with
		(
			IGNORE_DUP_KEY = ON
		)

)
go

 

Review

Code

Sample

	exec sp_help '[dbo].[studentTestAccount]'

Output

IgnoreDupKey

Explanation
  1. What you want to look into is the “ignore duplicate keys” in the Index Description cell of our Primary Key

 

Crediting

Crediting Jeff Atwood and Joel Spolsky, Stack Overflow.

Listening

Listening to Roland Orzabal and Curt Smith

Shout – Tears For Fears

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s