Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Vancouver, BC
    Posts
    35

    Unanswered: 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?

    Thanks
    INSERT INTO Jokes (statement) VALUES ('Here is my witty SQL signature');

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    You 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. :}

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    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.

    Code:
    /* 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
    Code:
    -- 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
    Have you hugged your backup today?

  4. #4
    Join Date
    Dec 2002
    Location
    Vancouver, BC
    Posts
    35
    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.
    INSERT INTO Jokes (statement) VALUES ('Here is my witty SQL signature');

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •