Solved

Ignore Duplicate Keys Disabled

Posted on 2012-03-27
8
1,404 Views
Last Modified: 2014-04-22
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
0
Comment
Question by:TechMommy
  • 5
  • 2
8 Comments
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37775564
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
 
LVL 11

Author Comment

by:TechMommy
ID: 37778155
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37778168
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
 
LVL 11

Author Comment

by:TechMommy
ID: 37778192
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37779894
There are many attributes in the UI that are not enabled.  But feel free to post your SQL script.
0
 
LVL 11

Author Comment

by:TechMommy
ID: 37780288
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

0
 
LVL 11

Accepted Solution

by:
TechMommy earned 0 total points
ID: 37780292
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
 
LVL 11

Author Closing Comment

by:TechMommy
ID: 40014329
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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now