Adding Multiple Indexes

• mdouglass

  Default port

  Points: 1412

  More actions

  April 10, 2008 at 6:55 am

  #188996

  Hi all,

  I have a very simple table e.g. tblInventory with the following fields:

  part_number - nvarchar(50)

  location - nvarchar(10)

  I would like to have two unique indexes on both fields that will allow me to have duplicate (part_number) providing they are in different locations e.g.

  partxyz - manchester

  partxyz - liverpool

  If I add a primary key to part_number then I cannot add duplicate part numbers, I need the two fields to work together!

  SQL Server 2000 & SQL Server 2005

  Many thanks.

  Mike

• psa

  Old Hand

  Points: 336

  More actions

  April 10, 2008 at 7:06 am

  #800947

  Hi,

  you could make a primary key consisting of both fields.

  Try to avoid nvarchar type in primary keys and indexes if possible.

  PSA

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737757

  More actions

  April 10, 2008 at 7:37 am

  #800976

  It's a compound index and contains two fields. Just one index that's unique.

• mdouglass

  Default port

  Points: 1412

  More actions

  April 10, 2008 at 7:46 am

  #800982

  It worked perfect thank you all!

  Great stuff...

  Mike