Article
04/05/2007

This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Unique Constraints and Unique Indexes

Ron Talmage

Most of you realize what an SQL Server MVP is—someone who's an SQL Server guru and who's willing to share his or her expertise with others. Both of SQL Server Professional's regular columnists, Ron Talmage and Tom Moreau, have achieved that coveted status, and this month's column shows the sort of work that goes into it. (MVPs are often chosen based on their unpaid contributions to newsgroups.) For more on MVPs, check out www.mvps.org/about/.

Just recently, the following question came up in the microsoft.public.sqlserver.server newsgroup:

Can someone tell me the difference between a "unique index" and a "unique constraint"? The SQL Server documentation says that a unique constraint automatically creates a unique index to preserve the no-duplicate integrity of the column; but it doesn't say anything about what else is added to the checking of the column by the unique constraint.

The short answer is that a unique index is just an index, whereas a unique constraint is a unique index that's listed as a constraint object in the database. In the sysobjects table, the unique constraint will have an xtype value of "UQ." But does a unique constraint have any additional behavior that a unique index doesn't have—or vice versa? The answer to that question, it turns out, takes a big of digging.

Index vs. constraint

First, let's verify to ourselves that a unique index and a unique constraint really do have the same effect on a table by adding both of them to a sample table and inspecting the results. Let's choose the publishers table in the pubs database and create the index and the constraint on the pub_name column, which ought to be unique for each publisher. (The primary key of the table is pub_id.) First, we verify that there isn't already a unique index or a unique constraint on the table by executing:

  EXEC sp_helpindex publishers

and:

  EXEC sp_helpconstraint publishers

These commands show that there is a primary key constraint (and its index), but no unique index or constraint on the pub_name column.

Now let's add the unique index:

  CREATE UNIQUE INDEX uix_pub_name ON publishers(pub_name)

(Note that this is simply the standard syntax for creating an index.)

To add the unique constraint takes a little more work, because there's no example in the ALTER TABLE page of Books Online (BOL). It turns out that unique constraints follow the same pattern as that of adding a primary key constraint:

  ALTER TABLE publishers 
  ADD CONSTRAINT uqc_pub_name 
  UNIQUE (pub_name)

Now when we execute:

  exec sp_helpindex publishers

we see results shown in Table 1.

Table 1. Output from sp_helpindex publishers after we've created a unique index.

index_name	index_description	index_keys
UPKCL_pubind	Clustered, unique, primary key located on PRIMARY	pub_id
uix_pub_name	Non-clustered, unique located on PRIMARY	pub_name
uqc_pub_name	Non-clustered, unique, unique key located on PRIMARY	pub_name

In this output, the only difference between the unique index uix_pub_name and the unique constraint uqc_pub_name is that the constraint has the additional clause "unique key" in its description. So is there any extra property that a unique constraint might have that a unique index wouldn't?

Maybe there is. Both the ALTER TABLE command and the ObjectProperty() function address the issue of disabling constraints. So what about a unique constraint? Can you disable it, like you can some other constraints? If you could, that would make a unique constraint something more than just a unique index.

Disabling a constraint

To find out more about disabling constraints, we can return to the ALTER TABLE command in BOL. However, the syntax of the command is very complex, and there aren't any examples having to do with disabling unique constraints. Finding out what constraints can be disabled—and how to detect them—takes a lot more digging.

Also interesting is the fact that the ObjectProperty() function has a "CnstIsDisabled" parameter for determining whether or not a constraint is disabled. We can try it out on our newly created unique index:

  SELECT ObjectProperty(object_id('uqc_pub_name'),
'CnstIsDisabled')

This returns a 0, indicating that the constraint we just created indeed is not disabled.

Well, okay, can we disable it? I must confess that reading the ALTER TABLE command syntax and examples left me clueless as to how to disable a unique constraint. Not to mention, I was equally confused as to how to attempt to disable a primary key or foreign key constraint. I remember, though, that foreign key constraints can definitely be disabled, so I started looking there.

In the BOL topic "Creating and Modifying FOREIGN KEY Constraints," buttons exist for disabling a FOREIGN KEY constraint for INSERT and UPDATE statements. If you click on this topic's T-SQL button, it just takes you back to the ALTER TABLE page. But the Enterprise Manager button takes you to a page describing how to disable a foreign key constraint using the Design Table Properties dialog.

Turning on Profiler and running that dialog revealed the correct ALTER TABLE syntax for disabling a foreign key constraint, in this case for the foreign key constraint on titleauthors referencing au_id in the authors table.

  ALTER TABLE dbo.titleauthor
  NOCHECK CONSTRAINT FK__titleauth__au_id__0519C6AF

(Modify this syntax to disable a check constraint.)

So now it's just a question of trying the same for our unique constraint:

  ALTER TABLE publishers
  NOCHECK CONSTRAINT uqc_pub_name

Executing this apparently succeeds because no error messages are returned. Now let's try our test again:

  SELECT ObjectProperty(object_id('uqc_pub_name'), 
'CnstIsDisabled')

It still returns a 0. Unlike a check constraint, it appears you can't disable a unique constraint.

Confirmation

There's more confirmation of this conclusion by running the following:

  exec sp_helpconstraint publishers

In the Table 2 output, I've abbreviated the results a bit, leaving off the status_for_replication and constraint_keys columns, so we can focus on the status_enabled column.

Table 2. Abbreviated output from sp_helpconstraint publishers after attempting to disable a unique constraint.

constraint_type	constraint_name	status_enabled
CHECK on column pub_id	CK__publisher__pub_i__0DAF0CB0	Enabled
DEFAULT on column country	DF__publisher__count__0EA330E9	(n/a)
PRIMARY KEY (clustered)	UPKCL_pubind	(n/a)
UNIQUE (non-clustered)	uqc_pub_name	(n/a)

The "n/a" values for the default, primary key, and unique constraints status_enabled column help confirm that disabling any of those constraints isn't allowed.

What about the sp_helpconstraint system stored procedure and the foreign keys? The publishers table doesn't have any foreign keys, but the titleauthor table does, and running this command:

  ALTER TABLE dbo.titleauthor
  NOCHECK CONSTRAINT FK__titleauth__au_id__0519C6AF

will disable one of its foreign keys. Table 3 depicts a similarly abbreviated output for:

  exec sp_helpconstraint titleauthor

Table 3. Abbreviated output from sp_helpconstraint titleauthor after attempting to disable a foreign key.

constraint_type	constraint_name	status_enabled
FOREIGN KEY	FK__titleauth__au_id__164452B1	Disabled
FOREIGN KEY	FK__titleauth__title__173876EA	Enabled
PRIMARY KEY (clustered)	UPKCL_taind	(n/a)

At this point, our experiments prove that although foreign key constraints can be enabled and disabled, primary key constraints can't. It would seem that, since it can't be disabled, having the status of a constraint doesn't give the unique constraint any difference in behavior over a simple unique index.

Unique constraint as an index

Okay, are there any features that an index has that a unique constraint doesn't? For example, you can make a unique index clustered—can you do that for a unique constraint? Further, according to BOL, with a CREATE INDEX, you can specify (for both SQL Server 7.0 and 2000) the PAD_INDEX, FILLFACTOR, IGNORE_DUP_KEY, DROP_EXISTING, and STATISTICS_NORECOMPUTE options, and (in SQL Server 2000 only) the SORT_IN_TEMPDB.

It turns out that you can only use the FILLFACTOR option with the ALTER TABLE statement when adding a unique constraint. For example, the following won't work:

  ALTER TABLE publishers 
  ADD CONSTRAINT uqc_pub_name 
  UNIQUE (pub_name)
  WITH IGNORE_DUP_KEY

The error message is:

  Server: Msg 155, Level 15, State 1, Line 1
'IGNORE_DUP_KEY' is not a recognized ALTER TABLE option.

This is an important difference, then, between a unique constraint and a unique index. The ALTER TABLE statement doesn't allow several index creation options.

In addition, both SQL Server 7.0 and 2000 give us the sp_indexoption system stored procedure that allows us to specify locking options on indexes. SQL7 allows us to inspect and set "AllowRowLocks" and "AllowPageLocks," and SQL 2000 gives us the additional options "DisAllowRowLocks" and "DisAllowPageLocks." But these apply just as well to a unique constraint. For example, this:

  exec sp_indexoption 'publishers.uqc_pub_name',
'AllowRowLocks'

returns the value:

  Option Status Table Name 
------------- -----------
True          publishers

Notice that this is working for the unique constraint—not just the index.

So we've made an important discovery here: The index creation options other than FILLFACTOR that are available for a unique index are not available for a unique constraint.

Confusing language

The language surrounding ALTER TABLE and ObjectProperty() on the issue of disabling a constraint is confusing. On the one hand, we can use ALTER TABLE ... DISABLE only with triggers. (ObjectProperty() has the ExecIsTriggerDisabled parameter to detect that state.) On the other hand, ALTER TABLE ... NOCHECK must be used to disable check and foreign key constraints, and ObjectProperty()'s CnstIsDisabled option must be used to detect those states.

What's confusing is that you can disable check and foreign key constraints, but you can't use ALTER TABLE ... DISABLE. We have to use ALTER TABLE ... NOCHECK instead. But, to detect their state, we useObjectProperty()'s "CnstIsDisabled"!

One wonders if it wouldn't be more intuitive and consistent if we could issue this:

  ALTER TABLE dbo.titleauthor
  DISABLE CONSTRAINT FK__titleauth__au_id__0519C6AF

in addition to, or instead of, this:

  ALTER TABLE dbo.titleauthor
  NOCHECK CONSTRAINT FK__titleauth__au_id__0519C6AF

By the way, ObjectProperty()'s CnstIsDisabled works not only with check and FK constraints, but even with default constraints. For example, this shows how the ObjectProperty() command inspects a default constraint:

  SELECT ObjectProperty(object_id
('DF__publisher__count__0EA330E9') , 'CnstIsDisabled')

It also returns a 0. So running the ALTER TABLE ... NOCHECK command won't disable primary key, unique constraint, or default constraints.

It also seems odd that ALTER TABLE ... NOCHECK doesn't return an error when applied to primary key, unique, and default constraints. The command runs—and no rows or messages are returned—but nothing happens to the constraint, either.

In summary, we can safely conclude that there's no practical difference between a unique constraint and a unique index other than the fact that the unique constraint is also listed as a constraint object in the database. Since a unique constraint can't be disabled, having the status of a constraint doesn't give the unique constraint any additional behavior beyond a unique index. However, there are several index creation options that aren't available to the ALTER TABLE command that creates a unique constraint.

Download RON1001.SQL

To find out more about SQL Server Professional and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the October 2001 issue of SQL Server Professional. Copyright 2001, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.

Share via