SQL Server Flyway脚本-删除列约束问题 [英] SQL Server Flyway script - drop column constraint issue
本文介绍了SQL Server Flyway脚本-删除列约束问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用SQL Server,正在尝试删除一列。
表架构如下:
I'm working on SQL Server and am trying to drop a column. The table schema is as below:
CREATE TABLE [dbo].[XYZ](
[ID] [int] NOT NULL,
[DSC] [varchar](255) NULL,
[LOWER_LIMIT] [int] NOT NULL,
[UPPER_LIMIT] [int] NOT NULL,
CONSTRAINT [XP_XYZ] PRIMARY KEY CLUSTERED
(
[ID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
当我尝试删除列时:
ALTER TABLE [SENSOR]
DROP COLUMN LOWER_LIMIT;
在以下情况下,我被要求删除约束:
I'm asked to drop the constraint before:
The object 'DF__SENSOR__LOWER_LI__08B54D69' is dependent on column 'LOWER_LIMIT'.
Msg 4922, Level 16, State 9, Line 45
ALTER TABLE DROP COLUMN LOWER_LIMIT failed because one or more objects access this column.
现在我正在编写一个飞行脚本来删除列,直到我知道约束为止当约束在更高的环境中更改时,请运行drop命令,我尝试删除该列。
Now I'm writing a flyway script to drop the column and I would not know the constraint until I run the drop command as the constraint changes in higher environments I attempt to drop the column. How do I draft my flyway to drop this column?
推荐答案
最后,在Stackoverflow的帮助下,我能够执行以下操作:
Finally with the help of Stackoverflow's help I was able to do something like this:
IF EXISTS(SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'SENSOR'
AND COLUMN_NAME = N'LOWER_LIMIT')
BEGIN
DECLARE @sql NVARCHAR(MAX)
WHILE 1=1
BEGIN
SELECT TOP 1 @sql = N'alter table [SENSOR] drop constraint ['+dc.name+N']'
FROM sys.default_constraints dc
JOIN sys.columns c
ON c.default_object_id = dc.object_id
WHERE dc.parent_object_id = OBJECT_ID('[SENSOR]') AND c.name = N'LOWER_LIMIT'
IF @@ROWCOUNT = 0
BEGIN
PRINT 'DELETED Constraint on column LOWER_LIMIT'
BREAK
END
EXEC (@sql)
END;
ALTER TABLE [SENSOR] DROP COLUMN LOWER_LIMIT;
PRINT 'DELETED column LOWER_LIMIT'
END
ELSE
PRINT 'Column LOWER_LIMIT does not exist'
GO
原始帖子为这里
这篇关于SQL Server Flyway脚本-删除列约束问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文