Unlike foreign keys, primary keys and unique indexes cannot be “untrusted”.
There’s no “NOCHECK” option for unique constraints. The closest there is, is the “ignore_dup_key” option. “Hmm… That sounds interesting”, you must be thinking.
But let me stop you right there, buddy. Read this before you go off doing something potentially dangerous… Very, VERY dangerous.
But it does NOT mean that you can create such an index on an existing table that already has duplicates:
So what does the “IGNORE_DUP_KEY” option mean when you enable it on a primary key or a unique index anyway?
It means that attempts to insert duplicate keys into the table will be literally ignored:
Note the “Duplicate key was ignored” message. This is what we see when we query from the table:
See how the first row was inserted successfully, but the second one was completely ignored.
So, if you have “ignore_dup_key” option enabled… Well, it’s up to you to think about whether it’s a good fit for you or not.
But the PK is still fully enforced. You will NOT be in danger of having duplicates.
In any case, it is potentially a very dangerous setting, because if it’s enabled, it means that attempts to insert a duplicate key WILL SUCCEED but they will be IGNORED.
So, essentially, a user might THINK that their insertion command succeeded, when in fact their (duplicate) data was completely ignored and thrown into oblivion. This poses a real danger of data loss that you may not even be aware of.
In contrast, when “ignore_dup_key” is disabled (which is the default), attempts to insert a duplicate row will result in an EXCEPTION, and thus the user will obviously know about it.
But either way, whether you have that option enabled or not, it’s just two different ways of enforcing the Primary Key / Unique Index.
Remediation
To find all the primary keys and unique indexes with IGNORE_DUP_KEY ON across all of your databases, you can use this script:
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
CREATE TABLE #tmp (DBName SYSNAME, TableName SYSNAME, KeyName SYSNAME);
EXEC sp_MSforeachdb '
INSERT INTO #tmp
SELECT ''?'', QUOTENAME(OBJECT_SCHEMA_NAME(object_id, DB_ID(''?''))) + ''.'' + QUOTENAME(OBJECT_NAME(object_id, DB_ID(''?''))), name
FROM [?].sys.indexes
WHERE is_unique = 1
AND [ignore_dup_key] = 1'
SELECT DBName, TableName, KeyName FROM #tmp
DROP TABLE #tmp
To set IGNORE_DUP_KEY to OFF on a unique index, you can use a command such as this:
ALTER INDEX UniqueIndexName ON TableName SET ( IGNORE_DUP_KEY = OFF )
Setting IGNORE_DUP_KEY to OFF on a Primary Key is more of a problem. You cannot use the ALTER INDEX command in order to do it.
You’ll have to actually drop and re-create the primary key with the setting you want.
It would look something like this:
ALTER TABLE TableName DROP CONSTRAINT PK_TableName GO ALTER TABLE TableName ADD CONSTRAINT PK_TableName PRIMARY KEY CLUSTERED ( MyIDColumn ASC ) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] GO
Of course, you could always leave the setting ON, and perhaps this setting gives you ideas on where you can use it to your advantage, and it might be a good thing. But you have to make sure that it sits right with your software’s logic, and you must be aware of the implications of turning this setting to ON (which I hope I explained well enough in this post).
Pingback: Find and fix untrusted Foreign Keys in all databases – Eitan Blumin's Blog