Add Primary Key enforced with Existing Unique Index: How To
in [Programming SQL]
|
From: bill on 29 May 2010 21:17 Thanks Gert, I wrote a query to do this, but was hoping to avoid the build. Oracle can do this, so I thought SQL Server might be able to as well. It's good to know for sure, so thanks for the info. Thanks, Bill On May 29, 3:06 pm, Gert-Jan Strik <sorrytoomuchspamalre...(a)xs4all.nl> wrote: > Bill, > > What you want is not possible. Whenever you create a Primary Key > constraint or Unique constraint, SQL Server will create a unique index > to enforce the constraint. This index cannot be prevented or dropped, it > is tied to the constraint. > > So if you have to create Primary Keys, your best approach would be to > drop the existing unique indexes afterwards. Or drop them before you > create the Primary Key in case the existing index is a clustered index. > > -- > Gert-Jan > > > > bill wrote: > > > Got a bunch of tables from an exernal app that don't have Primary Keys > > defined, but they do have unique indexes. I need to add a primary key > > for replication. > > > I would like to add a primary key to these tables, but I want to say > > "Don't make a new index to enforce the primary key, just use the > > existing unique index." > > > I can't find any indication in the docs that SQL Server can do this, > > but thought I would check. > > > Thanks, > > > Bill- Hide quoted text - > > - Show quoted text -
From: Erland Sommarskog on 30 May 2010 16:14 bill (billmaclean1(a)gmail.com) writes: > I wrote a query to do this, but was hoping to avoid the build. Oracle > can do this, so I thought SQL Server might be able to as well. It's > good to know for sure, so thanks for the info. I found this Connect item that you may want to vote for: https://connect.microsoft.com/SQLServer/feedback/details/481447/convert-a-candidate-key-with-unique-index-into-a-pk-without-creating-a-new-index It may also be worth adding a comment about Oracle being able to do this. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|
Pages: 1 Prev: Add Primary Key enforced with Existing Unique Index: How To Next: insert into syntax error |