473,322 Members | 1,480 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

What's the difference? Unque Constraint and unique index, etc.?

All,

What's the difference between a unique contraint and unique?
sementically, if you want a column contain unique values, it is a
contraint. And an index is for searching/sort. The questions are:

1. Does a unique constraint interally use unique index?

2. If Yes to #1, I DO NOT need to create an index for search/sort
purpose, right?

3. If Yes to #2, What's better?

4. Also for Primary Key column, it is actually a special unique
contraint. Not need to create index on PK column for searching/sorting,
correct?

5. Also for FK contraint, no need to create an index for
searching/sorting?
Thanks

John

Jul 23 '05 #1
4 2850
1. Yes

2. Correct

3. It's often said that uniqueness is "better" enforced through constraints
rather than indexes - if only because people expect this to find uniqueness
as a property of the logical model (constraints) rather than the physical
implementation (indexes). For the same reason it's also possible that some
ER modeling tools may recognize unique constraints but not unique indexes.

Indexes do have one potential advantage. You can declare the IGNORE_DUP_KEY
option on an index but not on a constraint. That's not much advantage in my
view because there are few, if any, situations in which I think the
IGNORE_DUP_KEY option is a good idea. In SQL Server 2005 constraints have
the IGNORE_DUP_KEY option too!

4. Correct

5. Wrong. Indexes aren't automatically created on foreign keys and a foreign
key is usually a good candidate for creating an index.

--
David Portas
SQL Server MVP
--
Jul 23 '05 #2
David Portas (RE****************************@acm.org) writes:
Indexes do have one potential advantage. You can declare the
IGNORE_DUP_KEY option on an index but not on a constraint. That's not
much advantage in my view because there are few, if any, situations in
which I think the IGNORE_DUP_KEY option is a good idea. In SQL Server
2005 constraints have the IGNORE_DUP_KEY option too!


I agree that IGNORE_DUP_KEY is not very useful.

However, there is another index option which is not available for
constraints which is more useful and that is DROP_EXISTING. If you for
some reason want to change a clustered index, dropping it and then
creating the new definition of the index, SQL Server has to rebuild
the non-clustered indexes twice. (Because the NC indexes uses the
clustered index key as the row locator.) DROP_EXISTING makes it possible
to do the change in one step. (I don't have the SQL 2005 docs handy, so
I can't say whether this is available for constraints in SQL 2005.)

My personal strategy is to use a constraint if it reflects some logical
property of the table, and index if it just happens to be unique. (The
typical reason that an index "happens" to be unique is when the primary
key is included as the last column or similar.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
Good point Erland. I had forgotten about DROP_EXISTING.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #4
Just to add to the previous responses: the DROP_EXISTING feature is
indeed very useful. But there is no reason to shy away from using UNIQUE
constraints (or Primary Key constraints), because CREATE INDEX ... WITH
DROP_EXISTING will also work on indexes that enforce uniqueness of these
constraints.

Gert-Jan
Jul 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

26
by: Agoston Bejo | last post by:
I want to enforce such a constraint on a column that would ensure that the values be all unique, but this wouldn't apply to NULL values. (I.e. there may be more than one NULL value in the column.)...
112
by: Andy | last post by:
Hi All! We are doing new development for SQL Server 2000 and also moving from SQL 7.0 to SQL Server 2000. What are cons and pros for using IDENTITY property as PK in SQL SERVER 2000? Please,...
2
by: Mansoor Azam | last post by:
When I add a unique key constraint to column in SQL 6.5 why does it also create an index. e.g. In the table subaccounts I added a unique key constraint for the column login and SQL creates an index...
5
by: Kamil | last post by:
Hello What should I use for better perfomance since unique constraint always use index ? Thanks Kamil
4
by: serge | last post by:
I ran into a table that is used a lot. Well less than 100,000 records. Maybe not a lot of records but i believe this table is used often. The table has 26 fields, 9 indexes but no Primary Key at...
4
by: Dave | last post by:
Can you create a unique constraint on multiple columns, or does it have to be implemented as a unique index? If possible can someone please post some sample code? Thanks,
3
by: Prince Kumar | last post by:
Is there any way I can define an Unique constraint or unique index which allows more than one null values for the same column combination in DB2? ie, If my index is defined on (col3, col4) where...
10
by: Laurence | last post by:
Hi there, How to differentiate between unique constraint and unique index? These are very similar but I cannot differentiate them? Could someone give me a hand? Thanks in advance
15
by: Frank Swarbrick | last post by:
I have the following three tables DROP TABLE CALLTRAK.SERVICE_CODES @ CREATE TABLE CALLTRAK.SERVICE_CODES ( CODE CHAR(1) NOT NULL , CONSTRAINT SERVICE_CODES_PK PRIMARY KEY (CODE) ,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.