Drop and recreate primary key

Question

I want to change a primary key/index on a database table to change the index option ignore_dup_key to 'on'.

According to this question " Can I set ignore_dup_key on for a primary key? " I need to drop the index and create a new one.

Before I drop the index, how can I get the command to recreate it? Then I'll just change the ignore_dup_key option.

Right click the primary key in SSMS, and choose Script -> As Create -> To New Window — Andomar, May 15 '12 at 11:20
Get the script from source control, you do have all your database objects and code under source control, right? — Pondlife, May 15 '12 at 11:28
The disciple approached his master and spake thusly: "Master, I have a primary key upon my table, and I want to ignore duplicates thereupon. How may I accomplish this?" At this the master smiled gently and said "A donut...without a hole...is a danish". — Bob Jarvis - Слава Україні, May 15 '12 at 11:33
Pondlife, the primary key was created along with the table `create table .. primary key`. I have that query under source control, but it's no good if I want to keep the table and change only the key. — Colonel Panic, May 15 '12 at 11:34

score 2 · Accepted Answer · answered May 15 '12 at 17:05

2

Right click the primary key in SSMS, and choose Script -> As Create -> To New Window

answered May 15 '12 at 17:05

Andomar

232,371
49
380
404

This no longer works in 17.3. (Script alternative doesn't appear on right-click.) – Henrik Erlandsson Sep 26 '19 at 08:52

score 1 · Answer 2 · edited May 23 '17 at 12:16

1

As indicated by a commenter on another related question, you can enable IGNORE_DUP_KEY for the table:

ALTER TABLE [TableName] REBUILD WITH (IGNORE_DUP_KEY = ON);

and revert:

ALTER TABLE [TableName] REBUILD WITH (IGNORE_DUP_KEY = OFF);

edited May 23 '17 at 12:16

Community

1
1

answered Dec 10 '12 at 14:28

mcNux

1,472
1
15
13

score 0 · Answer 3 · answered May 15 '12 at 14:37

I did not think SQL would allow a PK with IGNORE_DUP_KEY = on so I tested on SQL 2008 R2.
Via script could create a table with PK IGNORE_DUP_KEY = on. But even with drop and create could not change a PK from off to on. What is interesting is the script ran with no error but it did not change the PK from off to on. You may find different results in your environment.

score 0 · Answer 4 · answered May 15 '12 at 14:54

0

To Drop the Index You can run

DROP INDEX [IndexName] ON [TableName]

To Create the index with Ignore_Dup_Key option

CREATE UNIQUE INDEX [IndexName] ON [TableName]([ColumnNam])
WITH (IGNORE_DUP_KEY = ON)

answered May 15 '12 at 14:54

Turbot

5,095
1
22
30

Drop and recreate primary key

4 Answers4