删除未命名约束

Question

我创建了一些没有明确名称的外键。

然后我发现生成的SQL名称很奇怪，例如FK__Machines__IdArt__760D22A7。猜测在不同的服务器上会生成不同的名称。

有没有一个好用的函数可以通过传递表和相关字段作为参数来删除未命名的外键约束？

- gmalggievato

Answer 1

要在列上删除单个未命名的默认约束，请使用以下代码：

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

如果您想为默认列执行此操作，这可能比原始问题更常见，我相信很多人会通过Google搜索到这个问题，那么只需更改以下行：

obj.type = 'F'

到

obj.type = 'D'

- Gunner

---

这对我不起作用 - default_object_id 对于我想要的列是空白的。 - JonnyRaa

Answer 2

没有内置的程序可以完成这个任务，但您可以使用information_schema视图中的信息构建自己的程序。

基于表的示例

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

- cmsjr

---

如果你想要让名称更容易读懂，你也可以使用 sp_rename 方法来采用上述策略，而不是仅仅删除它们。 - Tom H

有没有办法避免从表中删除所有未命名的约束？例如：“dropFK 'Machines'，'IdArticle'，'Articles'”，其中IdArticle是PK，而Articles是所引用的表？ - nano

当然，您只需要深入系统表一点，特定的限制可以通过连接到sysforeignkeys来实现。 - cmsjr

这将删除表上的所有外键。 - JonnyRaa

这是正确的，但同样也正确的是，不存在未命名的约束。 - cmsjr

你假设了一个特定的数据库。是哪一个？看起来像是 MS SQL。我们应该相应地更新标签。 - jpmc26

Answer 3

虽然Gunner的答案为想要删除实际DEFAULT约束而不是FKey约束（这也是我来到这里的原因！）的人指明了正确的方向，但它存在问题。

我认为以下方法可以解决所有问题。（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

- bielawski

Answer 4

这将让您根据表名+列名删除特定的外键约束。

在尝试其他答案后，我只是在系统表中四处查找，直到找到了一些可能看起来不错的东西。

您想要的是 Constraint_Column_Usage，根据文档，返回当前数据库中每个具有在该列上定义约束的列的一行。 我已将其与 sys.objects 连接以获取外键。

在过程中（这借鉴了其他答案。感谢大家！）：

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

- JonnyRaa

Answer 5

对我来说，这两个都不起作用，所以我必须想出这个方法来处理 MSSQL 服务器版本 12 和 14。

1. 首先，检查 RDBMS 给出的 FK 名称，它具有相同的前缀和主体，但仅在后缀哈希中不同。

2. 其次，选择这些约束的名称。

3. 第三，执行删除它们的 alter 命令。

4. 最后，您可以删除被这些约束阻止的列或表。

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;

我学到了教训，从现在开始我会始终明确地创建约束！

- helvete

Answer 6

这将生成一个脚本，将默认约束重命名为使用模式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

- wvmitchell