Joetjah Joetjah -4 years ago 59
MySQL Question

Dropping foreign key using variable as name

I use the following:

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;


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

Answer Source
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 = 'ALTER TABLE TheTable DROP CONSTRAINT ' + @constraintName;
EXECUTE (@runString);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download