CODEWITHSUNDEEP
sqlsql-servert-sqlssms
Colonel Panic
Colonel Panic

Reputation: 137544

Drop and recreate primary key

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.

Upvotes: 2

Views: 9491

Answers (4)

mcNux
mcNux

Reputation: 1501

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);

Upvotes: 1

Andomar
Andomar

Reputation: 238078

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

Upvotes: 2

Turbot
Turbot

Reputation: 5227

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)

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

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.

Upvotes: 0

Related Questions