Find Primary/Unique/Foreign Keys And Delete Them In SQL

Introduction

Today, we will learn how to find the Primary/Unique/Foreign Keys in a table by a simple query, and delete these. Sometimes, we find the below issues while working on keys,

  1. We don’t have the database access to delete the keys on the Server by SSMS.
  2. Sometimes, the key's name on the live database and in the development database is not the same.
  3. A huge number of Unique Keys are there on a table. And, we need to delete all.
  4. A huge number of Foreign Keys are there on a table. And, we need to delete all the references.

To resolve the above issues, we can use the queries only and send them on the live server. We know only a table name there so we need to perform all the tasks by table name only.

We have the below database tables and their diagrams.

Database Tables Diagram
tb_country has the below definition:
  1. CREATE TABLE [dbo].[tb_country](  
  2.     [country_id] [int] IDENTITY(1,1) NOT NULL,  
  3.     [country_name] [nvarchar](50) NULL,  
  4.     [isActive] [bitNULL,  
  5.  CONSTRAINT [PK_tb_country] PRIMARY KEY CLUSTERED   
  6. (  
  7.     [country_id] ASC  
  8. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY],  
  9.  CONSTRAINT [IX_tb_country] UNIQUE NONCLUSTERED   
  10. (  
  11.     [country_name] ASC  
  12. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  13. ON [PRIMARY]  
  14. GO  
  15.   
  16. ALTER TABLE [dbo].[tb_country] ADD  CONSTRAINT [DF_tb_country_isActive]  DEFAULT ((1)) FOR [isActive]  
  17. GO  
tbl_state has the below definition:
  1. CREATE TABLE [dbo].[tbl_state](    
  2.     [state_id] [int] IDENTITY(1,1) NOT NULL,    
  3.     [country_id] [intNULL,    
  4.     [state_name] [nvarchar](50) NULL,    
  5.     [isActive] [bitNULL,    
  6.  CONSTRAINT [PK_tbl_state] PRIMARY KEY CLUSTERED     
  7. (    
  8.     [state_id] ASC    
  9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY],    
  10.  CONSTRAINT [IX_tbl_state] UNIQUE NONCLUSTERED     
  11. (    
  12.     [country_id] ASC,    
  13.     [state_name] ASC    
  14. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]    
  15. ON [PRIMARY]       
  16. GO    
  17.     
  18. ALTER TABLE [dbo].[tbl_state] ADD  CONSTRAINT [DF_tbl_state_isActive]  DEFAULT ((1)) FOR [isActive]    
  19. GO       
  20. ALTER TABLE [dbo].[tbl_state]  WITH CHECK ADD  CONSTRAINT [FK_tbl_state_tb_country] FOREIGN KEY([country_id])    
  21. REFERENCES [dbo].[tb_country] ([country_id])    
  22. GO      
  23. ALTER TABLE [dbo].[tbl_state] CHECK CONSTRAINT [FK_tbl_state_tb_country]    
  24. GO       
  25. ALTER TABLE [dbo].[tbl_state]  WITH CHECK ADD  CONSTRAINT [FK_tbl_state_tb_country1] FOREIGN KEY([country_id])    
  26. REFERENCES [dbo].[tb_country] ([country_id])    
  27. GO       
  28. ALTER TABLE [dbo].[tbl_state] CHECK CONSTRAINT [FK_tbl_state_tb_country1]    
  29. Go   

bl_city has the below definition:

  1. CREATE TABLE [dbo].[tb_city](  
  2.     [city_id] [int] IDENTITY(1,1) NOT NULL,  
  3.     [state_id] [intNULL,  
  4.     [country_id] [intNULL,  
  5.     [city_name] [nvarchar](50) NULL,  
  6.     [isActive] [bitNULL,  
  7.  CONSTRAINT [PK_tb_city] PRIMARY KEY CLUSTERED   
  8. (  
  9.     [city_id] ASC  
  10. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY],  
  11.  CONSTRAINT [IX_tb_city] UNIQUE NONCLUSTERED   
  12. (  
  13.     [state_id] ASC,  
  14.     [city_name] ASC  
  15. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  16. ON [PRIMARY]  
  17. GO  
  18.   
  19. ALTER TABLE [dbo].[tb_city]  WITH CHECK ADD  CONSTRAINT [FK_tb_city_tb_country] FOREIGN KEY([country_id])  
  20. REFERENCES [dbo].[tb_country] ([country_id])  
  21. GO  
  22. ALTER TABLE [dbo].[tb_city] CHECK CONSTRAINT [FK_tb_city_tb_country]  
  23. GO  
  24. ALTER TABLE [dbo].[tb_city]  WITH CHECK ADD  CONSTRAINT [FK_tb_city_tbl_state] FOREIGN KEY([state_id])  
  25. REFERENCES [dbo].[tbl_state] ([state_id])  
  26. GO  
  27. ALTER TABLE [dbo].[tb_city] CHECK CONSTRAINT [FK_tb_city_tbl_state]  
  28. GO  

I created a lot of Unique/Foreign Keys on the above tables. And only 3 Primary Keys  for 3 tables (Why there is only 1 Primary key per table, readers know very well).

Get the list of keys

To find the list of all Primary/Unique/Foreign Keys in a database, use the below query. It will provide all sets of keys in the database.
  1. SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  TABLE_SCHEMA ='dbo' 
Database Tables Diagram
 
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. 
  1. SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  TABLE_SCHEMA ='dbo' AND TABLE_NAME = 'tb_city'  
Database Tables Diagram
 
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

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.
  1. IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_NAME='tb_city' AND TABLE_SCHEMA ='dbo')  
  2. BEGIN  
  3.        DECLARE @CONSTRAINT_NAME NVARCHAR(1000)  
  4.        SELECT @CONSTRAINT_NAME=CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_NAME = 'tb_city' AND TABLE_SCHEMA ='dbo'  
  5.        DECLARE @sql NVARCHAR(1000) = 'ALTER TABLE tb_city  DROP CONSTRAINT ' + @CONSTRAINT_NAME;  
  6.        EXEC (@sql);  
  7. 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.

Database Tables Diagram

Now we have Unique and Foreign Keys on the table.
 
Delete Unique Keys

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. 
  1. WHILE ((SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_NAME='tb_city' AND TABLE_SCHEMA ='dbo')>0)  
  2. BEGIN  
  3.     DECLARE @CONSTRAINT_NAME NVARCHAR(1000)  
  4.     SELECT @CONSTRAINT_NAME=CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE='UNIQUE' AND TABLE_NAME = 'tb_city' AND TABLE_SCHEMA ='dbo'  
  5.     DECLARE @sql NVARCHAR(1000) = 'ALTER TABLE tb_city  DROP CONSTRAINT ' + @CONSTRAINT_NAME;  
  6.     EXEC (@sql);  
  7. 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,

Database Tables Diagram

Now, we have only Foreign keys on a table.

Delete Foreign Keys

To delete Foreign keys of a table, use the below query. 
  1. WHILE ((SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_NAME='tb_city' AND TABLE_SCHEMA ='dbo')>0)  
  2. BEGIN  
  3.     DECLARE @CONSTRAINT_NAME NVARCHAR(1000)  
  4.     SELECT @CONSTRAINT_NAME=CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_NAME = 'tb_city' AND TABLE_SCHEMA ='dbo'  
  5.     DECLARE @sql NVARCHAR(1000) = 'ALTER TABLE tb_city  DROP CONSTRAINT ' + @CONSTRAINT_NAME;  
  6.     EXEC (@sql);  
  7. 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,

Database Tables Diagram

Now we don't have any keys in table.

Final Code to Delete all Keys
  1. DECLARE @CONSTRAINT_NAME NVARCHAR(1000) , @sql NVARCHAR(1000)  
  2. IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_NAME='tb_city' AND TABLE_SCHEMA ='dbo')    
  3. BEGIN    
  4.     SELECT @CONSTRAINT_NAME=CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_NAME = 'tb_city' AND TABLE_SCHEMA ='dbo'    
  5.     SET @sql = 'ALTER TABLE tb_city  DROP CONSTRAINT ' + @CONSTRAINT_NAME;    
  6.     EXEC (@sql);    
  7. END  
  8.   
  9. WHILE ((SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_NAME='tb_city' AND TABLE_SCHEMA ='dbo')>0)    
  10. BEGIN         
  11.     SELECT @CONSTRAINT_NAME=CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE='UNIQUE' AND TABLE_NAME = 'tb_city' AND TABLE_SCHEMA ='dbo'    
  12.     SET @sql = 'ALTER TABLE tb_city  DROP CONSTRAINT ' + @CONSTRAINT_NAME;    
  13.     EXEC (@sql);    
  14. END    
  15.   
  16. WHILE ((SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_NAME='tb_city' AND TABLE_SCHEMA ='dbo')>0)    
  17. BEGIN    
  18.     SELECT @CONSTRAINT_NAME=CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_NAME = 'tb_city' AND TABLE_SCHEMA ='dbo'    
  19.     SET @sql = 'ALTER TABLE tb_city  DROP CONSTRAINT ' + @CONSTRAINT_NAME;    
  20.     EXEC (@sql);    
  21. 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.