Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
steve65
Starting Member
5 Posts |
 Posted - 2003-09-08 : 10:25:01
|
I have been trying to create a unique index with a certain name so I issued the SQL below:
CREATE UNIQUE INDEX pk_table_one ON dbo.table_one(col_one,col_two,col_three)
But when I then try to put on the constraints show in the next line I am not able to because the constraint pk_table_one already exist.
ALTER TABLE dbo.table_one ADDCONSTRAINT pk_table_one PRIMARY KEY (col_one,col_two,col_three)
If I issue just the ALTER TABLE command I am able to create an index with the name that I want but the index gets created as a clustered index. I am not used to using cluster so I was not sure how this would impact my performance.Thanks in advance for any help,Steve |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-09-08 : 10:36:35
|
| You can't have more than one thing with the same name. Also, having both a primary key and an index defined on the same set of columns is pointless, as the primary key acts as an index. You only need one or the other. A clustered index will generally provide superior SELECT performance over a non-clustered index assuming the same criteria etc. If you do not have another more preferable clustered index, you may as well have the primary key be the clustered index. If you don't want the primary key to be your clustered index, then either create the clustered index first with a different name, or do your primary key thuslyALTER TABLE dbo.table_one ADDCONSTRAINT pk_table_one PRIMARY KEY NONCLUSTERED (col_one,col_two,col_three)-------Moo. :) |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-08 : 11:00:54
|
I'd be interested in RDBMS background...Because in DB2 you HAVE to create the index, separate from the constraint..but in BOL?quote:
A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints ensure unique data, they are often defined for identity column.
WOW!Brett8-)SELECT @@POST=NewId()That's correct! It's an AlphaNumeric! |
|
|
|
|
|
|
|
|
Unique Index Question