如何在Microsoft SQL中删除约束表?
问题描述:
我怎样才能删除表与约束,如何在Microsoft SQL中删除约束表?
use my_db0
if exists(select* from sys.tables where name='Tbl1')
drop table Tbl1 --cascade constraints;
Create table Tbl1(
nameID int primary key
)
if exists (select* from sys.tables where name='Tbl2')
drop table Tbl2
Create table Tbl2(
lastNameID int primary key,
nameID int foreign key references Tbl1(nameID)
)
答
您可以使用此查询:
Select Query =
'If EXISTS (Select * FROM sys.foreign_keys Where '
+ ' object_id = OBJECT_ID(N''' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' + QUOTENAME(fk.name) + ''')'
+ ' And parent_object_id = OBJECT_ID(N''' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' + OBJECT_NAME(fk.parent_object_id) + ''')) ' +
'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fk.parent_object_id))
+ ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + '; '
, [Schema] = sh.name, [Table] = OBJECT_NAME(fk.parent_object_id), [Constraint] = ob.name
From sys.foreign_keys as fk
Inner Join sys.objects as ob on ob.object_id = fk.parent_object_id
Inner Join sys.schemas as sh on ob.schema_id = sh.schema_id
Where ob.name in ('xxx', 'yyy');
这将输出:
- 跌落约束查询
- 架构名称
- 表名
- 约束名
然后,您可以dynamicaly在第一列执行查询:
Exec sp_executesql @sql
凡@sql来自查询列。一旦约束条件被删除,您可以删除表格。
答
表TBL2包含填充NameID作为外键,所以你必须先擦拭的数据TBL2,然后删除TBL1
if exists (select* from sys.tables where name='Tbl2')
drop table Tbl2
if exists(select* from sys.tables where name='Tbl1')
drop table Tbl1
答
在SQL Server 2016,你可以使用DROP IF EXISTS:
ALTER TABLE my_table
DROP CONSTRAINT IF EXISTS <name>
DROP TABLE IF EXISTS my_table
约万
[SQL DROP TABLE外键约束]的可能的复制(http://*.com/questions/1776079/sql-drop-table-foreign-key-constraint) – Peter
第一滴比约束桌子。 –