Export (0) Print
Expand All
9 out of 22 rated this helpful - Rate this topic

Delete Primary Keys

You can delete (drop) a primary key in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. When the primary key is deleted, the corresponding index is deleted.

In This Topic

Security

Permissions

Requires ALTER permission on the table.

Arrow icon used with Back to Top link [Top]

To delete a primary key constraint using Object Explorer

  1. In Object Explorer, expand the table that contains the primary key and then expand Keys.

  2. Right-click the key and select Delete.

  3. In the Delete Object dialog box, verify the correct key is specified and click OK.

To delete a primary key constraint using Table Designer

  1. In Object Explorer, right-click the table with the primary key, and click Design.

  2. In the table grid, right-click the row with the primary key and choose Remove Primary Key to toggle the setting from on to off.

    Note Note

    To undo this action, close the table without saving the changes. Deleting a primary key cannot be undone without losing all other changes made to the table.

  3. On the File menu, click Save table name.

Arrow icon used with Back to Top link [Top]

To delete a primary key constraint

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute. The example first identifies the name of the primary key constraint and then deletes the constraint.

    USE AdventureWorks2012;
    GO
    -- Return the name of primary key.
    SELECT name
    FROM sys.key_constraints
    WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'TransactionHistoryArchive';
    GO
    -- Delete the primary key constraint.
    ALTER TABLE Production.TransactionHistoryArchive
    DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID; 
    GO
    

For more information, see ALTER TABLE (Transact-SQL) and sys.key_constraints (Transact-SQL)

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.