Converting a Clustered Index on a PK Identity field to non-clustered

Hi there, I have a table that has an IDENTITY column and it is the PK of this table. By default SQL Server creates a unique clustered index on the PK, but this isn't what I wanted. I want to make a regular unique index on the column so I can make a clustered index on a different column.

If I try to uncheck the Clustered index option in EM I get a dialog that says "Cannot convert a clustered index to a nonclustered index using the DROP_EXISTING option.". If I simply try to delete the index I get the following "An explicit DROP INDEX is not allowed on index 'index name'. It is being used for PRIMARY KEY constraint enforcement.

So do I have to drop the PK constraint now? How does that affect all the tables that have FK relationships to this table?

ThanksYou can certainly drop the PK and recreate it non-clustered.

If you have FKs to this PK, you'll have to drop them first though, and then recreate them once you have recreated your PK.

This is one reason why using the UI for table design is not a good idea - better to write the DDL yourself so you know exactly what you are getting. :}|||Here are a couple of scripts to help you identify FKs related to the PK column that you have.

Instructions:
1. Make a backup of the DB before you do this.
2. Copy and paste the two snippets of code into QA.
3. Press CTRL+Shift+M to set the variables for the table name and the column ID of the column with the PK.
4. Run the CREATE script FIRST.
5. Copy and paste the results into another QA window.
6. Run the DROP script.
7. Copy and paste the drop script results into a QA window
8. Execute the drop statement
9. Alter your PK
10. Execute the CREATE script that was generated earlier.

/* DROP SPECIFIC FOREIGN KEYS */
print '-- Drop Specific Foreign Keys related to a field'
print ''

DECLARE @.fkName varchar(800), @.tabName varchar(800), @.owner varchar(800)
DECLARE @.pline varchar(8000)

DECLARE fkCursor CURSOR FOR
select distinct object_name(constid) FK_Name, object_name(fkeyid) as Local_Tab_Name, user_name(so.uid) as Local_Tab_Owner
from sysforeignkeys k inner join sysobjects so on
k.fkeyid = so.id
where k.rkeyid = object_id('<table_name, varchar(255), MyTable>')
and k.rkey = <col_id, int, 1>
order by object_name(fkeyid)

OPEN fkCursor

FETCH NEXT FROM fkCursor
INTO @.fkName, @.tabName, @.owner

WHILE @.@.FETCH_STATUS = 0
BEGIN

select @.pline = 'ALTER TABLE [' + @.owner + '].[' + @.tabName + '] '

select @.pline = @.pline + 'DROP CONSTRAINT [' + @.fkName + ']' +
CHAR(13) + CHAR(10) + 'go'
print @.pline

FETCH NEXT FROM fkCursor
INTO @.fkName, @.tabName, @.owner
END

CLOSE fkCursor
DEALLOCATE fkCursor
GO

Code to generate DDL for FKs

-- Generate Adds for SELECTED Foreign Keys in Database
print '-- Add Foreign Keys'
print ''

DECLARE @.fkName varchar(800), @.tabName varchar(800), @.refName varchar(800), @.fkOwner varchar(800), @.refOwner varchar(800)
DECLARE @.isDel int, @.isUpd int, @.isNotRepl int, @.isNotTrusted int, @.isDisabled int, @.fkCol varchar(8000), @.refCol varchar(8000)
DECLARE @.pline varchar(8000)

DECLARE fkCursor CURSOR FOR
select distinct object_name(constid) FK_Name, object_name(fkeyid) as Local_Tab_Name,
object_name(rkeyid) as Remote_Tab_Name,
OBJECTPROPERTY ( constid , 'CnstIsDeleteCascade' ) DeleteCascade,
OBJECTPROPERTY ( constid , 'CnstIsUpdateCascade' ) UpdateCascade,
OBJECTPROPERTY ( constid , 'CnstIsNotRepl' ) NotForReplication,
OBJECTPROPERTY ( constid , 'CnstIsNotTrusted' ) NotTrusted,
OBJECTPROPERTY ( constid , 'CnstIsDisabled' ) Disabled,
USER_NAME(fkso.uid) fkOwner,
USER_NAME(refso.uid) refOwner
from
sysforeignkeys k inner join sysobjects fkso on
k.fkeyid = fkso.id
inner join sysobjects refso on
k.rkeyid = refso.id
where
k.rkeyid = object_id('<table_name, varchar(255), MyTable>')
and k.rkey = <col_id, int, 1>
order by object_name(fkeyid)

OPEN fkCursor

FETCH NEXT FROM fkCursor
INTO @.fkName, @.tabName, @.refName, @.isDel, @.isUpd, @.isNotRepl, @.isNotTrusted, @.isDisabled, @.fkOwner, @.refOwner

WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.fkCol = NULL
SELECT @.fkCol = ISNULL(@.fkCol + ', ','') + '[' + col_name(fkeyid, fkey) + ']'
from sysforeignkeys
where object_name(constid) = @.fkName
order by keyno

select @.refCol = NULL
SELECT @.refCol = ISNULL(@.refCol + ', ','') + '[' + col_name(rkeyid, rkey) + ']'
from sysforeignkeys
where object_name(constid) = @.fkName
order by keyno
select @.pline = 'ALTER TABLE [' + @.fkOwner + '].[' + @.tabName + '] '

if @.isNotTrusted = 1
select @.pline = @.pline + 'WITH NOCHECK'
select @.pline = @.pline + ' ADD CONSTRAINT [' + @.fkName + ']' + CHAR(13) + CHAR(10) +
' FOREIGN KEY (' + @.fkCol + ') REFERENCES [' + @.refOwner + '].[' + @.refName +
'] (' + @.refCol + ')'

if @.isDel = 1
select @.pline = @.pline + CHAR(13) + CHAR(10) +
' ON DELETE CASCADE'
if @.isUpd = 1
select @.pline = @.pline + CHAR(13) + CHAR(10) +
' ON UPDATE CASCADE'
if @.isNotRepl = 1
select @.pline = @.pline + CHAR(13) + CHAR(10) +
' NOT FOR REPLICATION'
select @.pline = @.pline + CHAR(13) + CHAR(10) + 'go'
if @.isDisabled = 1
select @.pline = @.pline + CHAR(13) + CHAR(10) +
'ALTER TABLE [dbo].[' + @.tabName + ']' +
' NOCHECK CONSTRAINT [' + @.fkName + ']' +
CHAR(13) + CHAR(10) + 'go'
print @.pline

FETCH NEXT FROM fkCursor
INTO @.fkName, @.tabName, @.refName, @.isDel, @.isUpd, @.isNotRepl, @.isNotTrusted, @.isDisabled, @.fkOwner, @.refOwner
END

CLOSE fkCursor
DEALLOCATE fkCursor
GO

Regards,

hmscott|||Thanks for the tips. I normally do my DDL with T-SQL and not EM, but this was just some quick and dirty test stuff. I should have known that I would have to drop the FK constraints before dropping the PK constraint.