Skip to content

delete-primary-keys.md

Latest commit

 

History

History
90 lines (62 loc) · 3.43 KB

delete-primary-keys.md

File metadata and controls

90 lines (62 loc) · 3.43 KB
title Delete Primary Keys | Microsoft Docs
ms.custom
ms.date 07/25/2017
ms.prod sql
ms.prod_service database-engine, sql-database
ms.reviewer
ms.technology table-view-index
ms.topic conceptual
helpviewer_keywords
removing primary keys
deleting primary keys
primary keys [SQL Server], deleting
ms.assetid c472e465-7bdd-4d74-8fc9-e47fca007ccb
author stevestein
ms.author sstein
monikerRange =azuresqldb-current||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

Delete Primary Keys

[!INCLUDEtsql-appliesto-ss2016-asdb-xxxx-xxx-md]

You can delete (drop) a primary key in [!INCLUDEssCurrent] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql]. When the primary key is deleted, the corresponding index is deleted.

In This Topic

Before You Begin

Security

Permissions

Requires ALTER permission on the table.

Using SQL Server Management Studio

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]
    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 Savetable name.

Using Transact-SQL

To delete a primary key constraint

  1. In Object Explorer, connect to an instance of [!INCLUDEssDE].

  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)