sql - 如何删除特定列的外键

Question

我创建了一个外键，但没有指定名称，因此 sql server 使用自动生成的名称创建了它。现在我想删除具有该外键的列。问题是我不知道这个外键的名称。有没有办法删除特定表中特定列的所有外键？

到目前为止，我发现这个脚本删除了列的所有默认约束

DECLARE @tableName VARCHAR(MAX)
DECLARE @ConstraintName nvarchar(200)
DECLARE @columnName VARCHAR(MAX)

SET @tableName = 'tablename'
SET @columnName = 'columnname'
SELECT @ConstraintName = Name
FROM SYS.DEFAULT_CONSTRAINTS
WHERE PARENT_OBJECT_ID = OBJECT_ID(@tableName) 
AND PARENT_COLUMN_ID = (
    SELECT column_id FROM sys.columns
    WHERE NAME = @columnName AND object_id = OBJECT_ID(@tableName))
IF @ConstraintName IS NOT NULL
    BEGIN
 EXEC('ALTER TABLE '+@tableName+' DROP CONSTRAINT ' + @ConstraintName)
 END

ALTER TABLE [tablename] DROP COLUMN columnname
GO

但它对外键约束没有帮助。

Answer 1

如果您想获得有关 FK 的更多信息，特别是有关特定方案和表的信息，您可以使用。

 SELECT 
    t.Name as TableName,
    c.name as ColumnName, 
    fk.name as FK_NAME 

 FROM sys.foreign_keys as fk
 inner join sys.tables as t on fk.parent_object_id = t.object_id
 inner join sys.columns as c on c.object_id = t.object_id
 inner join sys.schemas as sc on t.schema_id = sc.schema_id
 WHERE sc.name = 'Schema' and t.name = 'Table' and c.name = 'Column'

如果您只对某些专栏感兴趣，那么您可以使用Ross Presser答案。

此外，如果您想删除所有 fk 约束，您可以执行以下命令:

  Declare @sql  nvarchar(4000)
  SET @sql = N'';

  SELECT @sql = @sql + '
  ALTER TABLE [' + sc.NAME + '].[' + OBJECT_NAME(fk.parent_object_id) + ']' + ' DROP  CONSTRAINT ' + '[' + fk.NAME + ']
   '
  FROM sys.foreign_keys as fk
  inner join sys.tables as t on fk.parent_object_id = t.object_id
  inner join sys.columns as c on c.object_id = t.object_id
  inner join sys.schemas as sc on t.schema_id = sc.schema_id
  WHERE sc.name = 'schemaName'  and c.name = 'columnName' -- you can include and fk name
  ORDER BY fk.NAME
  PRINT @sql;
--EXEC sys.sp_executesql @sql;