Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    40

    Question Unanswered: unique constraint/index

    I'm trying to weight the pros and cons of unique constraints and unique indexes. I understand that creating a unique constraint also creates an index. If that is the case, why not just use a unique index? Could someone give me an example of when you would want an unique constraint over an unique indexes

    Thanks in advance

  2. #2
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Re: unique constraint/index

    As with anything - space is always an issue

    You may want to enfore uniqueness within a database for referiential intergrity but do not want the disk space issues that go with having an index on the column as well.

    Cheers

  3. #3
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Angry Re: unique constraint/index

    Having had a look at BOL it looks like it does create an index on a column with a unique constaint on it.

    Not sure what the answer to your question is then

    Sorry ?

    Anyone ??

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It won't create a clustered index...

    It also makes up the INDEX Name for you...

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(
    	  Col1 int NOT NULL UNIQUE
    	, Col2 int NOT NULL UNIQUE
    	, Col3 int NOT NULL UNIQUE
    	, UNIQUE (Col1,Col2,Col3)
    	, PRIMARY KEY (Col1)
    )
    GO
    
    sp_Help myTable99
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2004
    Posts
    40
    wont this make a clustered index?

    CREATE TABLE myTable99(
    Col1 int NOT NULL UNIQUE clustered
    , Col2 int NOT NULL UNIQUE
    , Col3 int NOT NULL UNIQUE
    , UNIQUE (Col1,Col2,Col3)
    , PRIMARY KEY (Col1)
    )
    GO

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes on Column1 but that's a primary key constraint...just pointing it out what it does...

    did you cut and paste and execute the code?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jan 2004
    Posts
    40
    yeah, i did run the code

    maybe i'm not understanding what you said. You mean creating a unique constraint will create a non clustered index and creating a unique index will create a clustered index?

    anyhow, it seems like i can set any index to be clustered, whether it is an index i created, or an index created from a primary/unique constraint.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well not really...you can only have 1 per table...usually it's wise to make it on the PK...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •