I have this sql:

ALTER TABLE dbo.ChannelPlayerSkins DROP CONSTRAINT FK_ChannelPlayerSkins_Channels

but apparently, on some other databases we use, the constraint has a different name. How do I check if there's a constraint with the name FK_ChannelPlayerSkins_Channels.

2

15 Answers

try this:

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='FK_ChannelPlayerSkins_Channels'

-- EDIT --

When I originally answered this question, I was thinking "Foreign Key" because the original question asked about finding "FK_ChannelPlayerSkins_Channels". Since then many people have commented on finding other "constraints" here are some other queries for that:

--Returns one row for each CHECK, UNIQUE, PRIMARY KEY, and/or FOREIGN KEY
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='XYZ'
--Returns one row for each FOREIGN KEY constrain
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME='XYZ'
--Returns one row for each CHECK constraint
SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS WHERE CONSTRAINT_NAME='XYZ'

here is an alternate method

--Returns 1 row for each CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY, and/or DEFAULT
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint ,SCHEMA_NAME(schema_id) AS SchemaName ,OBJECT_NAME(parent_object_id) AS TableName ,type_desc AS ConstraintType FROM sys.objects WHERE type_desc LIKE '%CONSTRAINT' AND OBJECT_NAME(OBJECT_ID)='XYZ'

If you need even more constraint information, look inside the system stored procedure master.sys.sp_helpconstraint to see how to get certain information. To view the source code using SQL Server Management Studio get into the "Object Explorer". From there you expand the "Master" database, then expand "Programmability", then "Stored Procedures", then "System Stored Procedures". You can then find "sys.sp_helpconstraint" and right click it and select "modify". Just be careful to not save any changes to it. Also, you can just use this system stored procedure on any table by using it like EXEC sp_helpconstraint YourTableNameHere.

7

Easiest way to check for the existence of a constraint (and then do something such as drop it if it exists) is to use the OBJECT_ID() function...

IF OBJECT_ID('dbo.[CK_ConstraintName]', 'C') IS NOT NULL ALTER TABLE dbo.[tablename] DROP CONSTRAINT CK_ConstraintName

OBJECT_ID can be used without the second parameter ('C' for check constraints only) and that may also work, but if your constraint name matches the name of other objects in the database you may get unexpected results.

IF OBJECT_ID('dbo.[CK_ConstraintName]') IS NOT NULL ALTER TABLE dbo.[tablename] DROP CONSTRAINT CK_ConstraintName

OBJECT_ID can also be used with other "constraints" such as Foreign Key constraints or Primary Key constraints, etc. For best results, always include the appropriate object type as the second parameter for the OBJECT_ID function:

Constraint Object Types:

• C = CHECK constraint
• D = DEFAULT (constraint or stand-alone)
• F = FOREIGN KEY constraint
• PK = PRIMARY KEY constraint
• R = Rule (old-style, stand-alone)
• UQ = UNIQUE constraint

Also note that the schema is often required. The schema of constraints generally takes the schema of the parent table.

Failure to put your constraints (or whatever you are checking) in brackets when using this method may also cause a false negative -- if your object uses unusual characters (such as a .), the brackets are required.

3

If you are looking for other type of constraint, e.g. defaults, you should use different query (From How do I find a default constraint using INFORMATION_SCHEMA? answered by devio). 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"

0

IF (OBJECT_ID('FK_ChannelPlayerSkins_Channels') IS NOT NULL)

1

Are you looking at something like this, below is tested in SQL Server 2005

SELECT * FROM sys.check_constraints WHERE
object_id = OBJECT_ID(N'[dbo].[CK_accounts]') AND
parent_object_id = OBJECT_ID(N'[dbo]. [accounts]')

Just something to watch out for......

In SQL Server 2008 R2 SSMS, the "Script Constraint as -> DROP And CREATE To" command produces T-SQL like below

USE [MyDatabase]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DEF_Detail_IsDeleted]') AND type = 'D')
BEGIN
ALTER TABLE [Patient].[Detail] DROP CONSTRAINT [DEF_Detail_IsDeleted]
END
GO
USE [MyDatabase]
GO
ALTER TABLE [Patient].[Detail] ADD CONSTRAINT [DEF_Detail_IsDeleted] DEFAULT ((0)) FOR [IsDeleted]
GO

Out of the box, this script does NOT drop the constraint because the SELECT returns 0 rows. (see post Microsoft Connect).

The name of the default constraint is wrong but I gather it also has something to do with the OBJECT_ID function because changing the name doesn't fix the problem.

To fix this, I removed the usage of OBJECT_ID and used the default constraint name instead.

(SELECT * FROM dbo.sysobjects WHERE [name] = (N'DEF_Detail_IsDeleted') AND type = 'D')

1

I use the following query to check for an existing constraint before I create it.

IF (NOT EXISTS(SELECT 1 FROM sysconstraints WHERE OBJECT_NAME(constid) = 'UX_CONSTRAINT_NAME' AND OBJECT_NAME(id) = 'TABLE_NAME')) BEGIN
...
END

This queries for the constraint by name targeting a given table name. Hope this helps.

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

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

0

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.

0

I use this to check for and remote constraints on a column. It should have everything you need.

DECLARE @ps_TableName VARCHAR(300) , @ps_ColumnName VARCHAR(300)
SET @ps_TableName = 'mytable'
SET @ps_ColumnName = 'mycolumn'
DECLARE c_ConsList CURSOR LOCAL STATIC FORWARD_ONLY FOR SELECT 'ALTER TABLE ' + RTRIM(tb.name) + ' drop constraint ' + sco.name AS csql FROM sys.Objects tb INNER JOIN sys.Columns tc on (tb.Object_id = tc.object_id) INNER JOIN sys.sysconstraints sc ON (tc.Object_ID = sc.id and tc.column_id = sc.colid) INNER JOIN sys.objects sco ON (sc.Constid = sco.object_id) where tb.name=@ps_TableName AND tc.name=@ps_ColumnName
OPEN c_ConsList
FETCH c_ConsList INTO @ls_SQL
WHILE (@@FETCH_STATUS = 0) BEGIN IF RTRIM(ISNULL(@ls_SQL, '')) <> '' BEGIN EXECUTE(@ls_SQL) END FETCH c_ConsList INTO @ls_SQL
END
CLOSE c_ConsList
DEALLOCATE c_ConsList

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

0

SELECT tabla.name as Tabla, restriccion.name as Restriccion, restriccion.type as Tipo, restriccion.type_desc as Tipo_Desc
FROM {DATABASE_NAME}.sys.objects tabla
INNER JOIN {DATABASE_NAME}.sys.objects restriccion
ON tabla.object_id = restriccion.parent_object_id
WHERE tabla.type = 'U' - Solo tablas creadas por el usuario.
AND restriccion.type = 'UQ' --Tipo de Restriccion UNIQUE
ORDER BY tabla.name, restriccion.type_desc 

2

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 mentions that it was available since SQL Server 2016.

The SQL Server docs mention it here under the ALTER TABLE page, and not under this 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.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy