Removing clustered index removes PK from column

If I remove a clustered index (set on the PK column) it removes the PK attribute from the column definition. Whats the deal?

If I have a non-clustered index on the table and I remove the clustered index then it does NOT remove the PK attribute from the column def开发者_开发知识库inition

I am assuming you are doing this from GUI tool and not from SQL statement. What actually happens when you try to drop the clustered index that has been defined on a PK, it will do ALTER TABLE DROP CONSTRAINT first since it won't be able to do DROP INDEX statement on an index that is being used by the PK (see this MSDN article, second paragraph). You shouldn't be able to do this with Non-Clustered index as well.

Here is an example... I created a Foo table:

CREATE TABLE foo (id int primary key, value varchar(50))

this will create an automatic clustered index (i.e. PK_foo_3213EXXXXXXXXX)

Try to do this from the tool (SQL Management studio): Right click on the PK_foo_3213EXXXXXXXX index from that table and do Script Index As -> DROP to... and see what it will generate...

It's actually this:

/****** Object:  Index [PK__foo__3213E83F7F60ED59]    Script Date: 03/17/2011 11:49:57 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[foo]') AND name = N'PK__foo__3213E83F7F60ED59')
ALTER TABLE [dbo].[foo] DROP CONSTRAINT [PK__foo__3213E83F7F60ED59]
GO

Say if I add another index (unique non-clustered) to table like so:

/****** Object:  Index [test]    Script Date: 03/17/2011 11:55:46 ******/
CREATE UNIQUE NONCLUSTERED INDEX [test] ON [dbo].[foo] 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

And do the same (Script as DROP to), this is the script that will be generated:

/****** Object:  Index [test]    Script Date: 03/17/2011 11:54:48 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[foo]') AND name = N'test')
DROP INDEX [test] ON [dbo].[foo] WITH ( ONLINE = OFF )
GO

USE [dummy]
GO

Note the difference (the previous one is ALTER TABLE DROP CONSTRAINT, and the later is DROP INDEX).

If you try force SQL to do this on the PK clustered index:

DROP INDEX [PK__foo__3213E83F7F60ED59] ON [dbo].[foo]

you will get this:

Msg 3723, Level 16, State 4, Line 1 An explicit DROP INDEX is not allowed on index 'dbo.foo.PK_foo_3213E83F7F60ED59'. It is being used for PRIMARY KEY constraint enforcement.

Thus SQL is forced to do the DROP CONSTRAINT instead.

I am not sure what you mean by your second if statement... if you mean you have defined a PK on a non-clustered index and you have another clustered index that is not the PK and drop that clustered index... then yes... that's the behavior that will happen (your PK constraint will not be dropped).

For fun sake, try to script DROP INDEX on the PK that is on a non-clustered index... and guess what script it will generate :). Hint hint... ALTER TABLE .... DROP ... CONSTRAINT