Creating a unique index based on multiple keys? SQL2k5

Question

Post reply

Creating a unique index based on multiple keys? SQL2k5

Jade Skaggs

SSC-Addicted

Points: 444
More actions
April 11, 2007 at 10:26 am

#123544

I am pretty new to SQL Server, so please forgive me if i don't know the lingo. Here's what I have been asked to do. Say we have the following table:
a b c
=====
1 a 1
1 b 1
1 b 2
1 a 2
2 a 1
2 a 2
As you can see, none of the columns are unique, but the whole row of the 3 columns are unique. Is it possible to create an index that determines duplicate index based on the sum of three columns? If so, how?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18842 More actions · Answer 1

Maybe you want a composite primary key:

ALTER TABLE YourTable

ADD CONSTRAINT PK_YourTable PRIMARY KEY (a, b, c)

This will stop any duplicates.

Erwin Dockx SSC Eights! Points: 852 More actions · Answer 2

You can also use a unique constraint, that way you can put your primary key on 1 column (datatype integer) for better performance when joining tables on that key.

This next section comes right out of the Books Online (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/e3e38fbc-1a6f-42c7-93a2-a93f24490d58.htm) and gives a good clue as to what the unique constraint is about:

You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.
Multiple UNIQUE constraints can be defined on a table, whereas only one PRIMARY KEY constraint can be defined on a table.
Also, unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column.
A UNIQUE constraint can be referenced by a FOREIGN KEY constraint.

The syntax is as follows:

alter table T_MYTABLE

add constraint UC_MYCONSTRAINT unique nonclustered ( F_COLUMN_A, F_COLUMN_B, F_COLUMN_C )

Hope this helps

_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.

You can find me on LinkedIn.
I support The Programmer's Bill of Rights.

MCITP, MCDBA, MCSD

Ken McKelvey SSCoach Points: 18842 More actions · Answer 3

>>that way you can put your primary key on 1 column (datatype integer) for better performance when joining tables on that key.

‘That would be an ecumenical matter!’

Personally I would think very carefully before doing this.

Eric Peterson Hall of Fame Points: 3192 More actions · Answer 4

I would highly recommend putting the primary key to a logical key ( datatype int) especially if the abc columns are longer than 10 bytes.

Second do some analysis on your index before creating

1) if you are always going to access the table thru the logical key, then make this the cluster index

2) which column (A, B, C) is the data going to be queried against? you may want to consider having a alt unique index ( ABC ) plus two others on columns B and C ( you dont need one on A bacause it is covered in your unique index.

my 2 cents

Eric