Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 30th, 2007, 05:34 AM
Authorized User
 
Join Date: Dec 2006
Location: chennai, tn, India.
Posts: 62
Thanks: 0
Thanked 0 Times in 0 Posts
Default Constraint Vs Index

Could any one pls. tell me the exact difference between the Constraint and Index created on the table (SQL 2000). As i dont find any difference other than the syntax.

Chandru
__________________
Thanks,
Chandra
  #2 (permalink)  
Old November 30th, 2007, 08:34 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

The difference is really a semantic one. A constraint is used to enforce database integrity; an index is used to provide an alternative means of accessing a row. As implemented in SQL Server, there is no material difference, at least as far as a PRIMARY KEY or a UNIQUE constraint is concerned. Both are in fact implemented as an index, and the query optimizer will use them in its query plans just like an index.

The only difference I am aware of is that you use the DROP INDEX command to drop an index, whereas you must use ALTER TABLE to drop these constraints, which of course is merely a syntactical difference as you mention.

Of course, there are other kinds of constraints, such as CHECK constraints, which don't have anything to do with indexes.

Jeff Mason
je.mason@comcast.net
  #3 (permalink)  
Old December 4th, 2007, 02:14 AM
Authorized User
 
Join Date: Dec 2006
Location: chennai, tn, India.
Posts: 62
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your prompt response.

Chandru


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I have a constraint like this.....? callagga SQL Language 7 August 13th, 2008 01:08 AM
Identity Constraint in DataSet anujrathi Pro VB.NET 2002/2003 1 June 21st, 2006 04:50 AM
Cant ADD CONSTRAINT (ergent) melvik MySQL 3 April 18th, 2006 01:16 AM
constraint for 1-to-n relation j_yan MySQL 0 December 10th, 2004 11:03 PM
Constraint for names barkingstars Oracle 2 April 14th, 2004 01:29 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.