How to check if a Constraint exists in Sql server?

If you are looking for other type of constraint, e.g. defaults, you should use different query
(From

[To see links please register here]

answered by [devio][1]). Use:

SELECT * FROM sys.objects WHERE type = 'D' AND name = @name

to find a default constraint by name.

I've put together different 'IF not Exists" checks in my post "[DDL 'IF not Exists" conditions to make SQL scripts re-runnable"][2]





[1]:

[To see links please register here]

[2]:

[To see links please register here]

IF EXISTS(SELECT TOP 1 1 FROM sys.default_constraints WHERE parent_object_id = OBJECT_ID(N'[dbo].[ChannelPlayerSkins]') AND name = 'FK_ChannelPlayerSkins_Channels')
BEGIN
DROP CONSTRAINT FK_ChannelPlayerSkins_Channels
END
GO

You can use the one above with one caveat:

IF EXISTS(
SELECT 1 FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID(N'dbo.TableName')
AND name = 'CONSTRAINTNAME'
)
BEGIN
ALTER TABLE TableName DROP CONSTRAINT CONSTRAINTNAME
END

Need to use the `name = [Constraint name]` since a table may have multiple foreign keys and still not have the foreign key being checked for

`INFORMATION_SCHEMA` is your friend. It has all kinds of views that show all kinds of schema information. Check your system views. You will find you have three views dealing with constraints, one being `CHECK_CONSTRAINTS`.

In mySql you need to make sure you are querying the right database!
Hence `table_schema=DATABASE()`

Here are my functions using knex to check if a specific Foreign Key or Index defined in specific database and table

const isFKExists = async (knex, tableName, fkName) => {
const result = await knex.raw(
`SELECT COUNT(*) AS 'isExists' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE table_schema=DATABASE() AND table_name='${tableName}' AND CONSTRAINT_NAME='${fkName}' AND CONSTRAINT_TYPE = 'FOREIGN KEY'`
)
return (result[0][0].isExists === 1)
}

const isIndexExists = async (knex, tableName, indexName) => {
const result = await knex.raw(
`SELECT COUNT(*) AS 'isExists' FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema=DATABASE() AND table_name='${tableName}' AND index_name='${indexName}'`
)
return (result[0][0].isExists > 0)
}

Enjoy 😊

As of SQL Server 2016, you can just use the `IF EXISTS` keywords.

```
ALTER TABLE dbo.ChannelPlayerSkins
DROP CONSTRAINT IF EXISTS FK_ChannelPlayerSkins_Channels
```

I'm using SQL Server 2019, but [this][1] mentions that it was available since SQL Server 2016.

The SQL Server docs mention it [here][2] under the `ALTER TABLE` page, and not under [this][3] Delete Check Constraints page. I'm not sure why.

> IF EXISTS
Applies to: SQL Server (SQL Server 2016 (13.x) and later) and Azure SQL Database.
Conditionally drops the column or constraint only if it already exists.


[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]