Geek asks and answers

Removing a clustered index removes PK from a column

If I remove the clustered index (specified in the PK column), it will remove the PK attribute from the column definition. What a deal?

If I have a non-clustered index in the table and I delete the clustered index, then it DOES NOT remove the PK attribute from the column definition

+3
source share
1 answer

I assume that you are doing this from a GUI tool, not from an SQL statement. What actually happens when you try to reset the clustered index that was defined on the PC, it will first execute ALTER TABLE DROP CONSTRAINT, since it will not be able to execute the DROP INDEX statement on the index used by PK (see this MSDN article , second paragraph) . You should also not do this with an index without clustering.

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 a tool (SQL Management Studio): Right-click the PK_foo_3213EXXXXXXXX index from this table and make Script Index As → DROP ... and see what it will generate ...

This is actually:

/****** 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

, ( ) :

/****** 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

(Script DROP to), Script, :

/****** 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

( - ALTER TABLE DROP CONSTRAINT, - DROP INDEX).

SQL PK:

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

:

Msg 3723, 16, 4, 1 DROP INDEX 'dbo.foo.PK_foo_3213E83F7F60ED59'. PRIMARY KEY.

, SQL DROP CONSTRAINT.

, if... , PK , , PK . ..., , ( PK ).

Script DROP INDEX , ... , Script :). ... ALTER TABLE.... DROP... CONSTRAINT

+11

More articles:


All Articles