Timeline for Why does PK constraint require a separate index when I already have a suitable unique clustered index?
Current License: CC BY-SA 3.0
21 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Apr 13, 2017 at 12:42 | history | edited | CommunityBot |
replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
|
|
| Aug 6, 2015 at 6:56 | vote | accept | sharptooth | ||
| Aug 6, 2015 at 2:56 | answer | added | Erik | timeline score: 4 | |
| Aug 4, 2015 at 13:26 | comment | added | Mikael Eriksson | One place to be wary about is client code/ORMs/ADO batch update etc. that automatically builds update and delete statements. They could possibly rely on the existence of a PK. | |
| Aug 4, 2015 at 13:23 | answer | added | Kenneth Fisher | timeline score: 3 | |
| Aug 4, 2015 at 13:19 | comment | added | Mikael Eriksson | My guess is you won't break anything but don't take my word for it, test it first. | |
| Aug 4, 2015 at 13:13 | comment | added | sharptooth | @MikaelEriksson I'm not sure I won't break anything. I'd rather have PK changed to use the clustered index if that's possible. | |
| Aug 4, 2015 at 13:13 | answer | added | Rob Farley | timeline score: 6 | |
| Aug 4, 2015 at 12:57 | comment | added | JNK | @MikaelEriksson it is in other versions of SQL Server | |
| Aug 4, 2015 at 12:55 | comment | added | Mikael Eriksson | How about dropping the primary key and just keep the unique constraint? I'm not able to test but I think the unique constraint is enough to allow foreign key constraints against the table. | |
| Aug 4, 2015 at 12:55 | comment | added | JNK | @ypercube azure does not support heaps apparently: social.msdn.microsoft.com/Forums/azure/en-US/… | |
| Aug 4, 2015 at 12:53 | comment | added | sharptooth | @ypercube Maybe it's possible with on-prem server, but surely not with SQL Azure. | |
| Aug 4, 2015 at 12:53 | comment | added | ypercubeᵀᴹ | @sharptooth that's not how SQL-Server works. You can drop the Clustered index from a table and the table and data will stay (it will be converted to a heap). | |
| Aug 4, 2015 at 12:47 | comment | added | sharptooth | @a_horse_with_no_name I have an existing table and so I cannot just drop the clustered index - the index stores my table. | |
| Aug 4, 2015 at 12:36 | comment | added | Martin Smith | Oracle has a "using index" clause for constraints I believe so they can be coupled to existing unique indexes. One use case for this in SQL Server would be to avoid having to create an unnecessary unique constraint in the supertype/subtype pattern. example here | |
| Aug 4, 2015 at 12:18 | comment | added | ypercubeᵀᴹ |
If you want to keep one (CI) index, you can drop the JobItemsIndex and convert the PK to use a clustered index.
|
|
| Aug 4, 2015 at 12:07 | comment | added | user1822 |
Why don't you simply remove the CREATE INDEX statement from the script?
|
|
| Aug 4, 2015 at 11:56 | comment | added | sharptooth | @a_horse_with_no_name This is legacy, that's why. Is it possible to tell SQL Server to use the existing clustered index for the PK constraint perhaps? | |
| Aug 4, 2015 at 11:50 | comment | added | user1822 | The question is: why did you add the unique index if you already have a PK on that column? | |
| Aug 4, 2015 at 11:30 | comment | added | Aaron Bertrand | Because you told SQL Server to create a separate index, and it did what you told it to. You can create 50 identical indexes if you want, too; SQL Server will create and maintain them all. | |
| Aug 4, 2015 at 11:28 | history | asked | sharptooth | CC BY-SA 3.0 |