How to Easily Drop Primary Key Constraint on SQL Server

Sometimes you will need to drop the Primary Key constraint because due to the changes in your database structure.

For example if you have created a table below with the Primary Key constraint name PK_tblPlannerGroup then later you can use this name to drop the constraint.

CREATE TABLE [dbo].[tblPlannerGroup](
    [PlanningPlantID] [int] NOT NULL,
    [PlannerGroupID] [varchar](5) COLLATE Latin1_General_CI_AS NOT NULL,
    [PlannerGroupName] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
 CONSTRAINT [PK_tblPlannerGroup] PRIMARY KEY CLUSTERED 
(
    [PlanningPlantID] ASC,
    [PlannerGroupID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tblPlannerGroup]  WITH CHECK ADD CONSTRAINT [FK_PlannerGroup_PlanningPlant] FOREIGN KEY([PlanningPlantID])
REFERENCES [dbo].[tblPlanningPlant] ([PlanningPlantID])
GO
ALTER TABLE [dbo].[tblPlannerGroup] CHECK CONSTRAINT [FK_PlannerGroup_PlanningPlant]

To drop the constraint use this syntax:
ALTER TABLE [dbo].[tblPlannerGroup]
DROP CONSTRAINT [PK_tblPlannerGroup]
GO
To find out the name of your primary key constraint try this script:

select object_name (parent_obj) ObjectName, name
from sysobjects
where xtype = ‘PK’
and parent_obj = (object_id(‘owner_name.table_name’))
In above script, change owner_name and table_name.
You can also find out via the SQL Server Management Studio. From Object Explorer -> expand databases -> expand tables -> expand your table_name -> expand key, and here you will see the primary key for that table.

Comments (0)

Write comment

Your Contact Details:

Name:

Email:

Website:

Comment:

Title:

UBBCode:

Message:

Security

Please input the anti-spam code that you can read in the image.

< Prev		Next >

"