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 |
|
ajreynolds
Starting Member
9 Posts |
 Posted - 2009-10-05 : 15:27:45
|
| We have a database where all of the tables have unique indexes and all values are non-null, but none of the tables have primary keys. To enable replication, the tables need to have a primary key. When I do an "alter table" and add a primary key, it adds a new unique index to go along with it, but I don't want a new index. I want to use one of the existing unique indexes as the primary key. Is there a way to set an existing unique index as the primary key without dropping and recreating the index that I want to use as primary key?Thanks,Andy |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-10-05 : 15:43:30
|
| Not that I know of. Primary Keys are manifested as Indexes in SQL. So you have to create the index. If you choose to use the same name as the existing unique constraint/index then you'll have to drop and recreate. But, you can certainly just create a new PK with a new name and leave the unique one (but I assume you'd just drop that index). |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
ajreynolds
Starting Member
9 Posts |
Posted - 2009-10-05 : 18:43:02
|
| That's exactly it. Some of the tables are pretty huge, hundreds of thousands, if not millions, of records. We also don't want to add more indexes as more indexes = more index updates when you add records.If it comes down to it, we will drop and recreate the appropriate indexes as primary keys, but we were hoping to avoid the downtime that would involve. We've done this on an Oracle based system before and it was a simple matter of doing an alter table and Oracle switched the correct index for us.Andy |
|
|
|
|
|
|
Switching unique index to primary key