Dropping unnamed constraints

Question

I've created some foreign keys without an explicit name.

Then I've found SQL generated crazy names like FK__Machines__IdArt__760D22A7. Guess they will be generated with different names at different servers.

Is there any nice function to drop the unnamed FK constraints passing as arguments the tables and the fields in question?

Answer 1

This will generate a script to rename default constraints to use the pattern DF__table_name__column_name

SELECT 'EXEC sp_rename ''dbo.' + dc.name + ''', ''DF__' + t.name + '__' + c.name + '''' AS the_script,
    t.name AS table_name,
    c.name AS column_name,
    dc.name AS old_constraint_name
FROM
    sys.default_constraints dc
    INNER JOIN sys.tables t
        ON dc.parent_object_id = t.object_id
    INNER JOIN sys.columns c
        ON dc.parent_column_id = c.column_id
        AND t.object_id = c.object_id
WHERE
    dc.name <> 'DF__' + t.name + '__' + c.name

Answer 2

Neither of these worked for me so I had to come up with this to work on mssql server version both 12 and 14.

1. First, inspect the name given to the FK by the RDBMS, it has the same prefix and body but differs only in suffix hash.

2. Second, select names of these constraints.

3. Third, exec alter command that drops them.

4. Finally you can drop the column or table blocked by these

BEGIN TRANSACTION;                                                          

DECLARE @ConstraintName nvarchar(200)

SELECT @ConstraintName = name                                               
FROM sys.objects                                                            
WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT'                                  
    AND name LIKE 'FK__table_col_shortcut1___%'                             
EXEC('ALTER TABLE table1 DROP CONSTRAINT ' + @ConstraintName)                  

SELECT @ConstraintName = name                                               
FROM sys.objects                                                            
WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT'                                  
    AND name LIKE 'FK__table_col_shortcut2___%'                             
EXEC('ALTER TABLE table2 DROP CONSTRAINT ' + @ConstraintName)               

SELECT @ConstraintName = name                                               
FROM sys.objects                                                            
WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT'                                  
    AND name LIKE 'FK__table_col_shortcut3___%'                             
EXEC('ALTER TABLE table3 DROP CONSTRAINT ' + @ConstraintName)               

DROP TABLE table_referenced;                                                

COMMIT TRANSACTION;

Lesson learnt, I will always create constraints explicitly from now on!

Answer 3

Although Gunner's answer puts people on the right track if you want to drop an actual DEFAULT constraint rather than an FKey constraint (which is what brought ME here too!) there are problems with it.

I think this fixes them all. (T-SQL)

CREATE PROC #DropDefaultConstraint @SchemaName sysname, @TableName sysname, @ColumnName sysname
AS
BEGIN
    DECLARE @ConstraintName sysname;

    SELECT @SchemaName = QUOTENAME(@SchemaName)
         , @TableName  = QUOTENAME(@TableName);

    SELECT @ConstraintName = QUOTENAME(o.name)
      FROM sys.columns c 
      JOIN sys.objects o 
        ON o.object_id = c.default_object_id 
     WHERE c.object_id = OBJECT_ID(@SchemaName+'.'+@TableName) 
       AND c.name = @ColumnName;

    IF @ConstraintName IS NOT NULL
        EXEC ('ALTER TABLE ' + @SchemaName + '.' + @TableName + ' DROP CONSTRAINT ' + @ConstraintName + '');
END

Answer 4

This will let you drop a specific foreign key constraint based on tablename + column name

After trying out the other answers I just had a poke around in the system tables until I found something likely looking.

The one you want is Constraint_Column_Usage which according to the docs Returns one row for each column in the current database that has a constraint defined on the column.

I've joined it to sys.objects to just get foreign keys.

In a procedure (this borrows from the other answers. cheers guys!):

Create Proc yourSchema.dropFK(@SchemaName NVarChar(128), @TableName NVarChar(128), @ColumnName NVarChar(128))
as
Begin

    DECLARE @ConstraintName nvarchar(128)
    SET @ConstraintName = (
        select c.Constraint_Name
          from Information_Schema.Constraint_Column_usage c
          left join sys.objects o 
          on o.name = c.Constraint_Name
          where c.TABLE_SCHEMA = @SchemaName and 
                c.Table_name = @TableName and 
                c.Column_Name = @ColumnName and
                o.type = 'F'
    )   

    exec ('alter table [' + @SchemaName + '].[' + @TableName + '] drop constraint [' + @ConstraintName + ']')
End

Answer 5

For dropping an individual unnamed default constrain on a column use the following code:

DECLARE @ConstraintName VARCHAR(256)
SET @ConstraintName = (
     SELECT             obj.name
     FROM               sys.columns col 

     LEFT OUTER JOIN    sys.objects obj 
     ON                 obj.object_id = col.default_object_id 
     AND                obj.type = 'F' 

     WHERE              col.object_id = OBJECT_ID('TableName') 
     AND                obj.name IS NOT NULL
     AND                col.name = 'ColunmName'
)   

IF(@ConstraintName IS NOT NULL)
BEGIN
    EXEC ('ALTER TABLE [TableName] DROP CONSTRAINT ['+@ConstraintName+']')
END

If you want to do this for a default column, which is probably more common than the original question and I'm sure a lot of people will land on this from a Google search, then just change the line:

obj.type = 'F'

to

obj.type = 'D'

Answer 6

There is not a built in procedure to accomplish this, but you can build your own using the information in the information_schema views.

Table based example

Create Proc dropFK(@TableName sysname)
as
Begin

Declare @FK sysname
Declare @SQL nvarchar(4000)
Declare crsFK cursor for

select tu.Constraint_Name from 
information_schema.constraint_table_usage TU
LEFT JOIN SYSOBJECTS SO 
ON TU.Constraint_NAME = SO.NAME
where xtype = 'F'
and Table_Name = @TableName
open crsFK
fetch next from crsFK into @FK
While (@@Fetch_Status = 0)
Begin
    Set @SQL = 'Alter table ' + @TableName + ' Drop Constraint ' + @FK
    Print 'Dropping ' + @FK
    exec sp_executesql  @SQL
    fetch next from crsFK into @FK
End
Close crsFK
Deallocate crsFK
End