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

Code Alone
2y
42.6k
0
11

Article

Introduction

Today, we will learn how to find a Primary Key, a Unique key, or a Foreign Key in a table using SQL. We will also learn how to delete a primary key, unique key, or a foreign key from a table using SQL. SQL Server supports ten types of keys. Sometimes, we find the below issues while working on keys,

We don’t have the database access to delete the keys on the Server by SSMS.
The keys nare available on production database and in the development database keys are not the same.
There are several Unique Keys on a table and we need to delete them all.
How to delete several Foreign Keys from a table and their references using SQL.

To resolve the above issues, we can simply use SQL queries and update 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:

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

tbl_state has the below definition:

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

bl_city has the below definition:

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

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 primary key, unique key, foreign key on a table

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.

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

Get the list of primary key, unique key, foreign key on a table

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'

Get the list of primary key, unique key, foreign key on a table

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.

Delete Primary Key from a Table using SQL

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,

Delete Unique Keys from a Table using SQL

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,

Delete Foreign Keys from a Table using SQL

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.