我希望从另一张表中删除外键,以便插入我想要的值。
由于我在数据库方面很新,请告诉我正确的SQL查询语句来删除或移除外键值。
我希望从另一张表中删除外键,以便插入我想要的值。
由于我在数据库方面很新,请告诉我正确的SQL查询语句来删除或移除外键值。
试试以下方法
ALTER TABLE <TABLE_NAME> DROP CONSTRAINT <FOREIGN_KEY_NAME>
SELECT name FROM sys.foreign_keys
查看数据库中的所有外键。祝你好运! - demoncodemonkeyALTER TABLE [TableName] DROP CONSTRAINT [CONSTRAINT_NAME]
但是,要小心,一旦你这样做了,你可能再也没有机会了,你应该阅读一些基础的数据库书籍,看看为什么我们需要外键。
要从数据库中删除所有约束:
SELECT 'ALTER TABLE ' + Table_Name +' DROP CONSTRAINT ' + Constraint_Name
FROM Information_Schema.CONSTRAINT_TABLE_USAGE
在您完全删除约束之前,请考虑(暂时)禁用约束。
如果您查看表创建TSQL,您将看到以下内容:
ALTER TABLE [dbo].[dbAccounting] CHECK CONSTRAINT [FK_some_FK_constraint]
ALTER TABLE [dbo].[dbAccounting] NOCHECK CONSTRAINT [FK_some_FK_constraint]
...然后插入/更新一些违反约束的值,然后通过运行原始的CHECK
语句来重新启用它。
(过去我曾经不得不这样做来清理我继承的糟糕设计的系统。)
删除表中的所有外键:
USE [Database_Name]
DECLARE @FOREIGN_KEY_NAME VARCHAR(100)
DECLARE FOREIGN_KEY_CURSOR CURSOR FOR
SELECT name FOREIGN_KEY_NAME FROM sys.foreign_keys WHERE parent_object_id = (SELECT object_id FROM sys.objects WHERE name = 'Table_Name' AND TYPE = 'U')
OPEN FOREIGN_KEY_CURSOR
----------------------------------------------------------
FETCH NEXT FROM FOREIGN_KEY_CURSOR INTO @FOREIGN_KEY_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @DROP_COMMAND NVARCHAR(150) = 'ALTER TABLE Table_Name DROP CONSTRAINT' + ' ' + @FOREIGN_KEY_NAME
EXECUTE Sp_executesql @DROP_COMMAND
FETCH NEXT FROM FOREIGN_KEY_CURSOR INTO @FOREIGN_KEY_NAME
END
-----------------------------------------------------------------------------------------------------------------
CLOSE FOREIGN_KEY_CURSOR
DEALLOCATE FOREIGN_KEY_CURSOR
根据你所使用的数据库,语法可能有所不同。
如果你正在使用Oracle,你需要按照其他用户告诉你的方式进行操作:
ALTER TABLE table_name DROP CONSTRAINT fk_name;
但是如果你使用的是MySQL,那么这将会给你一个语法错误,相反地,你可以输入:
ALTER TABLE table_name DROP INDEX fk_name;
首先使用
show create table table_name;
查看您的表的描述结构。
在那里,您可以查看与该表中使用的外键相关的约束条件。 首先使用
删除相应的约束条件。alter table table_name drop constraint constraint_name;
然后删除你想要的相应外键或列...祝你好运!
Declare @SchemaName VarChar(200) = 'Schema Name'
Declare @TableName VarChar(200) = 'Table name'
-- Find FK in This table.
SELECT
'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + FK.name + ']'
+ ''') AND parent_object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].['
+ OBJECT_NAME(FK.parent_object_id) + ']' + ''')) ' +
'ALTER TABLE ' + OBJECT_SCHEMA_NAME(FK.parent_object_id) +
'.[' + OBJECT_NAME(FK.parent_object_id) +
'] DROP CONSTRAINT ' + FK.name
, S.name , O.name, OBJECT_NAME(FK.parent_object_id)
FROM sys.foreign_keys AS FK
INNER JOIN Sys.objects As O
ON (O.object_id = FK.parent_object_id )
INNER JOIN SYS.schemas AS S
ON (O.schema_id = S.schema_id)
WHERE
O.name = @TableName
And S.name = @SchemaName
-- Find the FKs in the tables in which this table is used
SELECT
' IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + FK.name + ']'
+ ''') AND parent_object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].['
+ OBJECT_NAME(FK.parent_object_id) + ']' + ''')) ' +
' ALTER TABLE ' + OBJECT_SCHEMA_NAME(FK.parent_object_id) +
'.[' + OBJECT_NAME(FK.parent_object_id) +
'] DROP CONSTRAINT ' + FK.name
, S.name , O.name, OBJECT_NAME(FK.parent_object_id)
FROM sys.foreign_keys AS FK
INNER JOIN Sys.objects As O
ON (O.object_id = FK.referenced_object_id )
INNER JOIN SYS.schemas AS S
ON (O.schema_id = S.schema_id)
WHERE
O.name = @TableName
And S.name = @SchemaName
另外,您还可以从SQL Server Management Studio中删除外键约束。 如果命令不起作用,您可以尝试一下。
希望这有所帮助