Unable to remove index in SQL Server

Question

When the user activates the FK index removal option, DDL is generated for it, and when the PK index removal option is activated, the DDL is created for this application.

Two tables were created with the example below.

CREATE TABLE [dbo].[TABLE3] 
(
    [COL] [VARCHAR](20) NOT NULL
)
GO

ALTER TABLE [dbo].[TABLE3]
    ADD CONSTRAINT [PK_TABLE3]
        PRIMARY KEY NONCLUSTERED ([COL] ASC)
GO

CREATE TABLE [dbo].[TABLE4] 
(
    [COL] [VARCHAR](20)
)
GO

ALTER TABLE [dbo].[TABLE4]
    ADD CONSTRAINT [FK_TABLE3_TO_TABLE4]
        FOREIGN KEY ([COL]) REFERENCES [dbo].[TABLE3] ([COL])
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
GO

If you select both the Delete FK Constraints option and the Delete FK Index option The following syntax is generated.

ALTER TABLE [dbo].[TABLE4]
    DROP CONSTRAINT IF EXISTS [FK_TABLE3_TO_TABLE4]
GO

ALTER TABLE [dbo].[TABLE3]
    DROP CONSTRAINT IF EXISTS [PK_TABLE3]
GO

DROP INDEX [dbo].[TABLE3].[PK_TABLE3]
GO

An error occurs for the DROP INDEX syntax here.

SQL Error [3701] [S0007]: Index 'dbo.TABLE3.PK_TABLE3' does not exist or you do not have permission to drop it.

My question is:

1. If SQL Server removes the constraint, will the index be removed as well?

2. The DROP INDEX statement can use IF EXISTS to prevent an error, but I know that IF EXISTS is supported from SQL Server 2014 or 2016.

Since the 2005 version of the application under development needs to be supported, the IF EXISTS syntax does not seem to be a clear solution.

Please advise on what to do.

For documentation for each version of SQL Server, refer to the following links.

SQL Server 2005: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms176118(v=sql.90)

SQL Server 2008: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms176118(v=sql.100)

SQL Server 2012: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms176118(v=sql.110)

Answer 1

Try following code:

ALTER TABLE dbo.TABLE4 DROP CONSTRAINT FK_TABLE3_TO_TABLE4
GO
ALTER TABLE dbo.TABLE3 DROP CONSTRAINT PK_TABLE3
Go

You can use following code to get you constraints name list in your database:

SELECT TABLE_NAME,
       CONSTRAINT_TYPE,CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

OR

SELECT TABLE_NAME,
       CONSTRAINT_TYPE,CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE LOWER(TABLE_NAME) <> N'sysdiagrams'

TABLE_NAME	CONSTRAINT_TYPE	CONSTRAINT_NAME
TABLE3	PRIMARY KEY	PK_TABLE3
TABLE4	FOREIGN KEY	FK_TABLE3_TO_TABLE4