我有以下mysql命令来删除外键约束:
ALTER TABLE network_profile DROP FOREIGN KEY 'FK98875604AC3BAD33';
但是我宁愿使用SELECT语句先发现外键constraint_id,并在ALTER TABLE命令中使用它,但未成功。这可能吗?
尝试过:
ALTER TABLE network_profile DROP FOREIGN KEY (SELECT constraint_name FROM information_schema.key_column_usage WHERE column_name = 'mt_check_list' AND table_schema = 'mydb');
还尝试了:
SELECT @constraint_name := constraint_name FROM information_schema.key_column_usage WHERE column_name = 'mt_check_list' AND table_schema = 'mydb';
ALTER TABLE network_profile DROP FOREIGN KEY @constraint_name;
请帮忙?
不能在ALTER语句中使用变量。但是,您可以构建一个字符串,从该字符串准备一个语句,然后执行该语句:
SELECT CONCAT(
'ALTER TABLE `network_profile` DROP FOREIGN KEY `',
constraint_name,
'`'
) INTO @sqlst
FROM information_schema.key_column_usage
WHERE table_name = 'network_profile'
AND column_name = 'mt_check_list';
PREPARE stmt FROM @sqlst;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @sqlst = NULL;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句