Ignore Duplicate Keys Disabled

I have a situation where it makes sense to have Ignore Duplicate Keys On. The problem is that the option is disabled for the index and I can't figure out why. The index is a Primary Key Unique Clustered index.

What am I missing? By the way, I am able to create the index with a script and IGNORE_DUP_KEY = ON, but don't understand why I can't accomplish the same thing with the user interface.

Indexes/Keys Dialog
LVL 11
TechMommyAsked:
Who is Participating?
 
TechMommyAuthor Commented:
Just as an added comment, I just noticed that it does appear to be consistently available in the Index Properties dialog. So, I seem to be able to always set it there graphically.

Alternative for Setting Ignore Duplicate Keys
That basically solves my problem, but if anyone knows why it isn't consistently available in the Indexes/Keys dialog, I'd be really curious to know the answer.
0
 
BuggyCoderCommented:
remove primary key from table, a primary key means it will not allow duplicate keys in that column. how can you have a primary key and thus unique clustered index and allow duplicate keys as well....
0
 
TechMommyAuthor Commented:
Ignore Duplicate Keys does NOT allow duplicate rows to be inserted. It simply suppresses the error message associated with the procedure causing the error and instead produces a warning message (see BOL). I am using it because I want to reject the duplicates without rolling back the entire Insert statement. It works perfectly for other tables, but is for some reason disabled for this table. Once I set it in script it works beautifully. The primary key is respected by the error is suppressed.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Anthony PerkinsCommented:
The index is a Primary Key Unique Clustered index.
A primary key is a CONSTRAINT and not an INDEX.  (SQL Server enforces a Primary Key CONSTRAINT with a clustered or non-clustered unique index)
0
 
TechMommyAuthor Commented:
So why is the option disabled via the user interface, but scripts just fine, and why once I script it does it show up in the UI as Yes, but is still disabled?
0
 
Anthony PerkinsCommented:
There are many attributes in the UI that are not enabled.  But feel free to post your SQL script.
0
 
TechMommyAuthor Commented:
The curious thing is that the Ignore Duplicate Keys feature is SOMETIMES available via the user interface (for certain indexes), and not for others. I don't see a difference between the indexes that it is available for and those that it isn't. I can certainly use my script (which appears below), but I was hoping to get some incite as to why it is available sometimes and not others.

ALTER TABLE [dbo].[tblWorkers] ADD  CONSTRAINT [PK_tblWorkers] PRIMARY KEY CLUSTERED 
(
	[WorkerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Open in new window

1
 
TechMommyAuthor Commented:
This question was never answered. As mentioned in my previous comment, I was able to solve my own problem, but never figured out the source of the inconsistent behavior.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.