使用变量作为名称删除外键 [英] Dropping foreign key using variable as name

问题描述

我使用以下内容:

DECLARE @ConstraintName varchar(255);
SELECT @ConstraintName = CONSTRAINT_NAME 
FROM 
   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE 
WHERE 
   TABLE_NAME = 'TheTable' 
   AND COLUMN_NAME = 'TheColumn';

alter table TheTable drop constraint @ConstraintName;

但是，@ ConstraintName附近的语法不正确.这可能是一件小事，但我无法弄清楚.我应该更改什么，以便取消约束?

But, it has incorrect syntax near @ConstraintName. It might be a small thing but I can't figure it out. What should I change so the constraint will be dropped?

SQLFiddle: http://sqlfiddle.com/#!2/6709e/3

SQLFiddle: http://sqlfiddle.com/#!2/6709e/3

推荐答案

DECLARE @constraintName VARCHAR(50);
DECLARE @runString VARCHAR(2000);

select @constraintName = CONSTRAINT_NAME
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = 'TheTable' AND COLUMN_NAME = 'TheColumn';


SET @runString = CONCAT('ALTER TABLE TheTable DROP CONSTRAINT ', @constraintName);
PREPARE stmt1 FROM @runString ;
EXECUTE stm1;
DEALLOCATE PREPARE stmt1;

这篇关于使用变量作为名称删除外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！