Find Primary Key, Unique Key, or Foreign Key in a Table and Delete Keys Using SQL

CREATE TABLE [dbo].[tb_country](  
    [country_id] [int] IDENTITY(1,1) NOT NULL,  
    [country_name] [nvarchar](50) NULL,  
    [isActive] [bit] NULL,  
 CONSTRAINT [PK_tb_country] PRIMARY KEY CLUSTERED   
(  
    [country_id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],  
 CONSTRAINT [IX_tb_country] UNIQUE NONCLUSTERED   
(  
    [country_name] 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  
  
ALTER TABLE [dbo].[tb_country] ADD  CONSTRAINT [DF_tb_country_isActive]  DEFAULT ((1)) FOR [isActive]  
GO

CREATE TABLE [dbo].[tbl_state](    
    [state_id] [int] IDENTITY(1,1) NOT NULL,    
    [country_id] [int] NULL,    
    [state_name] [nvarchar](50) NULL,    
    [isActive] [bit] NULL,    
 CONSTRAINT [PK_tbl_state] PRIMARY KEY CLUSTERED     
(    
    [state_id] ASC    
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],    
 CONSTRAINT [IX_tbl_state] UNIQUE NONCLUSTERED     
(    
    [country_id] ASC,    
    [state_name] 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    
    
ALTER TABLE [dbo].[tbl_state] ADD  CONSTRAINT [DF_tbl_state_isActive]  DEFAULT ((1)) FOR [isActive]    
GO       
ALTER TABLE [dbo].[tbl_state]  WITH CHECK ADD  CONSTRAINT [FK_tbl_state_tb_country] FOREIGN KEY([country_id])    
REFERENCES [dbo].[tb_country] ([country_id])    
GO      
ALTER TABLE [dbo].[tbl_state] CHECK CONSTRAINT [FK_tbl_state_tb_country]    
GO       
ALTER TABLE [dbo].[tbl_state]  WITH CHECK ADD  CONSTRAINT [FK_tbl_state_tb_country1] FOREIGN KEY([country_id])    
REFERENCES [dbo].[tb_country] ([country_id])    
GO       
ALTER TABLE [dbo].[tbl_state] CHECK CONSTRAINT [FK_tbl_state_tb_country1]    
Go

CREATE TABLE [dbo].[tb_city](  
    [city_id] [int] IDENTITY(1,1) NOT NULL,  
    [state_id] [int] NULL,  
    [country_id] [int] NULL,  
    [city_name] [nvarchar](50) NULL,  
    [isActive] [bit] NULL,  
 CONSTRAINT [PK_tb_city] PRIMARY KEY CLUSTERED   
(  
    [city_id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],  
 CONSTRAINT [IX_tb_city] UNIQUE NONCLUSTERED   
(  
    [state_id] ASC,  
    [city_name] 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  
  
ALTER TABLE [dbo].[tb_city]  WITH CHECK ADD  CONSTRAINT [FK_tb_city_tb_country] FOREIGN KEY([country_id])  
REFERENCES [dbo].[tb_country] ([country_id])  
GO  
ALTER TABLE [dbo].[tb_city] CHECK CONSTRAINT [FK_tb_city_tb_country]  
GO  
ALTER TABLE [dbo].[tb_city]  WITH CHECK ADD  CONSTRAINT [FK_tb_city_tbl_state] FOREIGN KEY([state_id])  
REFERENCES [dbo].[tbl_state] ([state_id])  
GO  
ALTER TABLE [dbo].[tb_city] CHECK CONSTRAINT [FK_tb_city_tbl_state]  
GO

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  TABLE_SCHEMA ='dbo' 

To find the list of all Primary/Unique/Foreign Keys in a table, use the below query. It will provide all sets of keys in the table. We are working on tb_city.

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  TABLE_SCHEMA ='dbo' AND TABLE_NAME = 'tb_city'

In tb_city I have one Unique key, one Primary Key, and two Foreign keys there. You can filter these keys by using CONSTRAINT_TYPE in WHERE clause.

Delete Primary Key from a Table using SQL

To delete the primary key of a table, use the below query. Kindly confirm before that this Primary key is not attached to another table in reference.

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_NAME='tb_city' AND TABLE_SCHEMA ='dbo')  
BEGIN  
       DECLARE @CONSTRAINT_NAME NVARCHAR(1000)  
       SELECT @CONSTRAINT_NAME=CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_NAME = 'tb_city' AND TABLE_SCHEMA ='dbo'  
       DECLARE @sql NVARCHAR(1000) = 'ALTER TABLE tb_city  DROP CONSTRAINT ' + @CONSTRAINT_NAME;  
       EXEC (@sql);  
END

In the above query, I used IS EXISTS to find if there are any primary keys on the table. After execution of the above query Primary key will be deleted. When we search keys on that table, the result will be like below.

Now we have Unique and Foreign Keys on the table.

Delete Unique Keys from a Table using SQL

To delete Unique keys of a table, use the below query. Kindly confirm before that these Unique keys are not attached to another table in reference.

WHILE ((SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_NAME='tb_city' AND TABLE_SCHEMA ='dbo')>0)  
BEGIN  
    DECLARE @CONSTRAINT_NAME NVARCHAR(1000)  
    SELECT @CONSTRAINT_NAME=CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE='UNIQUE' AND TABLE_NAME = 'tb_city' AND TABLE_SCHEMA ='dbo'  
    DECLARE @sql NVARCHAR(1000) = 'ALTER TABLE tb_city  DROP CONSTRAINT ' + @CONSTRAINT_NAME;  
    EXEC (@sql);  
END

In the above query, I used WHILE loop to check that if any Unique key exists or not. We used the loop because there may be multiple unique keys in our table. After execution of the above query, all Unique keys will be deleted. When we search keys on that table, the result will be like below,

Now, we have only Foreign keys on a table.

Delete Foreign Keys from a Table using SQL

To delete Foreign keys of a table, use the below query.

WHILE ((SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_NAME='tb_city' AND TABLE_SCHEMA ='dbo')>0)  
BEGIN  
    DECLARE @CONSTRAINT_NAME NVARCHAR(1000)  
    SELECT @CONSTRAINT_NAME=CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_NAME = 'tb_city' AND TABLE_SCHEMA ='dbo'  
    DECLARE @sql NVARCHAR(1000) = 'ALTER TABLE tb_city  DROP CONSTRAINT ' + @CONSTRAINT_NAME;  
    EXEC (@sql);  
END

In the above query, I used WHILE loop to check that if any Foreign key exists or not. We used the loop because there may be multiple Foreign keys in our table. After execution of the above query, all Foreign keys will be deleted. When we search keys on that table, the result will be like below,

Now we don't have any keys in table.

Delete All Keys from a Table using SQL (Final Code)

DECLARE @CONSTRAINT_NAME NVARCHAR(1000) , @sql NVARCHAR(1000)  
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_NAME='tb_city' AND TABLE_SCHEMA ='dbo')    
BEGIN    
    SELECT @CONSTRAINT_NAME=CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_NAME = 'tb_city' AND TABLE_SCHEMA ='dbo'    
    SET @sql = 'ALTER TABLE tb_city  DROP CONSTRAINT ' + @CONSTRAINT_NAME;    
    EXEC (@sql);    
END  
  
WHILE ((SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_NAME='tb_city' AND TABLE_SCHEMA ='dbo')>0)    
BEGIN         
    SELECT @CONSTRAINT_NAME=CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE='UNIQUE' AND TABLE_NAME = 'tb_city' AND TABLE_SCHEMA ='dbo'    
    SET @sql = 'ALTER TABLE tb_city  DROP CONSTRAINT ' + @CONSTRAINT_NAME;    
    EXEC (@sql);    
END    
  
WHILE ((SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_NAME='tb_city' AND TABLE_SCHEMA ='dbo')>0)    
BEGIN    
    SELECT @CONSTRAINT_NAME=CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_NAME = 'tb_city' AND TABLE_SCHEMA ='dbo'    
    SET @sql = 'ALTER TABLE tb_city  DROP CONSTRAINT ' + @CONSTRAINT_NAME;    
    EXEC (@sql);    
END

Summary

Issues which we discussed in the introduction have been resolved successfully by queries. If you have some alternate way to achieve this kind of requirement then please let me know, or if you have some query then please leave your comments.