mysql alter table drop constraint_SQL DROP TABLE外键约束

不，如果确实有外键引用它，这将不会丢掉你的表。

要获得引用你的表的所有外键关系，你可以使用这个SQL(如果你在SQL Server 2005以上)：

SELECT * FROM sys.foreign_keys WHERE referenced_object_id = object_id('Student')

如果有的话，在这里用这个语句，你可以创buildSQL语句来实际删除这些FK关系：

SELECT 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']' FROM sys.foreign_keys WHERE referenced_object_id = object_id('Student')

在SQL Server Management Studio 2008(R2)及更新版本中，您可以右键单击

DB – >任务 – >生成脚本

select你想要删除的表格。

select“保存到新的查询窗口”。

点击高级button。

设置脚本DROP和创build脚本DROP。

将脚本外键设置为True。

点击OK。

点击下一步 – >下一步 – >完成。

查看脚本，然后执行。

如果先放下“子”表，则外键也将被丢弃。 如果您首先尝试删除“父”表，则会得到“无法删除对象”a，因为它被FOREIGN KEY约束引用。 错误。

这是使用sp_MSdropconstraints过程正确删除所有表的另一种方法。 我能想到的最短的代码是：

exec sp_MSforeachtable "declare @name nvarchar(max); set @name = parsename('?', 1); exec sp_MSdropconstraints @name"; exec sp_MSforeachtable "drop table ?";

如果是SQL Server，则必须先删除约束，然后才能删除表。

这里有另外一种方法可以删除表格本身的所有约束，使用涉及FOR XML PATH('')的连接技巧，允许将多个input行合并到一个输出行中。 应该在任何SQL 2005及更高版本上工作。

为了安全起见，我已经将EXECUTE命令留下了注释。

DECLARE @SQL NVARCHAR(max) ;WITH fkeys AS ( SELECT quotename(s.name) + '.' + quotename(o.name) tablename, quotename(fk.name) constraintname FROM sys.foreign_keys fk JOIN sys.objects o ON fk.parent_object_id = o.object_id JOIN sys.schemas s ON o.schema_id = s.schema_id ) SELECT @SQL = STUFF((SELECT '; ALTER TABLE ' + tablename + ' DROP CONSTRAINT ' + constraintname FROM fkeys FOR XML PATH('')),1,2,'') -- EXECUTE(@sql) SELECT @SQL = STUFF((SELECT '; DROP TABLE ' + quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES FOR XML PATH('')),1,2,'') -- EXECUTE(@sql)

这里是一个完整的脚本来实现一个解决scheme：

create Procedure [dev].DeleteTablesFromSchema ( @schemaName varchar(500) ) As begin declare @constraintSchemaName nvarchar(128), @constraintTableName nvarchar(128), @constraintName nvarchar(128) declare @sql nvarchar(max) -- delete FK first declare cur1 cursor for select distinct CASE WHEN t2.[object_id] is NOT NULL THEN s2.name ELSE s.name END as SchemaName, CASE WHEN t2.[object_id] is NOT NULL THEN t2.name ELSE t.name END as TableName, CASE WHEN t2.[object_id] is NOT NULL THEN OBJECT_NAME(d2.constraint_object_id) ELSE OBJECT_NAME(d.constraint_object_id) END as ConstraintName from sys.objects t inner join sys.schemas s on t.[schema_id] = s.[schema_id] left join sys.foreign_key_columns d on d.parent_object_id = t.[object_id] left join sys.foreign_key_columns d2 on d2.referenced_object_id = t.[object_id] inner join sys.objects t2 on d2.parent_object_id = t2.[object_id] inner join sys.schemas s2 on t2.[schema_id] = s2.[schema_id] WHERE t.[type]='U' AND t2.[type]='U' AND t.is_ms_shipped = 0 AND t2.is_ms_shipped = 0 AND s.Name=@schemaName open cur1 fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName while @@fetch_status = 0 BEGIN set @sql ='ALTER TABLE ' + @constraintSchemaName + '.' + @constraintTableName+' DROP CONSTRAINT '+@constraintName+';' exec(@sql) fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName END close cur1 deallocate cur1 DECLARE @tableName nvarchar(128) declare cur2 cursor for select s.Name, p.Name from sys.objects p INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id] WHERE p.[type]='U' and is_ms_shipped = 0 AND s.Name=@schemaName ORDER BY s.Name, p.Name open cur2 fetch next from cur2 into @schemaName,@tableName while @@fetch_status = 0 begin set @sql ='DROP TABLE ' + @schemaName + '.' + @tableName exec(@sql) fetch next from cur2 into @schemaName,@tableName end close cur2 deallocate cur2 end go

@mark_s发布的稍微更通用的版本，这对我有帮助

SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(k.parent_object_id) + '.[' + OBJECT_NAME(k.parent_object_id) + '] DROP CONSTRAINT ' + k.name FROM sys.foreign_keys k WHERE referenced_object_id = object_id('your table')

只需插入你的表名，并执行它的结果。

如果我想删除我的数据库中的所有表

那么删除整个数据库就容易多了：

DROP DATABASE WorkerPensions

如果你不介意丢失你的表，你可以使用一个简单的查询来一次删除多个表：

SET foreign_key_checks = 0; DROP TABLE IF EXISTS table_a,table_b,table_c,table_etc; SET foreign_key_checks = 1;

通过这种方式，无论您在查询中使用表格的顺序如何，

如果有人会说如果你有一个有很多表的数据库这不是一个好的解决scheme的事实：我同意！

Removing Referenced FOREIGN KEY Constraints Assuming there is a parent and child table Relationship in SQL Server: --First find the name of the Foreign Key Constraint: SELECT * FROM sys.foreign_keys WHERE referenced_object_id = object_id('States') --Then Find foreign keys referencing to dbo.Parent(States) table: SELECT name AS 'Foreign Key Constraint Name', OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id) AS 'Child Table' FROM sys.foreign_keys WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND OBJECT_NAME(referenced_object_id) = 'dbo.State' -- Drop the foreign key constraint by its name ALTER TABLE dbo.cities DROP CONSTRAINT FK__cities__state__6442E2C9; -- You can also use the following T-SQL script to automatically find --and drop all foreign key constraints referencing to the specified parent -- table: BEGIN DECLARE @stmt VARCHAR(300); -- Cursor to generate ALTER TABLE DROP CONSTRAINT statements DECLARE cur CURSOR FOR SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id) + ' DROP CONSTRAINT ' + name FROM sys.foreign_keys WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND OBJECT_NAME(referenced_object_id) = 'states'; OPEN cur; FETCH cur INTO @stmt; -- Drop each found foreign key constraint WHILE @@FETCH_STATUS = 0 BEGIN EXEC (@stmt); FETCH cur INTO @stmt; END CLOSE cur; DEALLOCATE cur; END GO --Now you can drop the parent table: DROP TABLE states; --# Command(s) completed successfully.

如果要使用外键使用DROP已被其他表引用的表

DROP TABLE *table_name* CASCADE CONSTRAINTS;

我认为它应该为你工作。