SQL 如何检查默认值约束是否存在？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13818837/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check if the Default Value Constraint exists?
提问by user960567
I am using SQL server 2008. I need to find if default value constraint does not exist then create it. Here is what I have tried.
我正在使用 SQL Server 2008。我需要查找是否不存在默认值约束，然后创建它。这是我尝试过的。
IF (NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='MyConstraint'))
BEGIN
ALTER TABLE [XX] ADD CONSTRAINT [MyConstraint] DEFAULT ((-1)) FOR [XXXX]
END
GO
回答by RichardTheKiwi
if not exists (
select *
from sys.all_columns c
join sys.tables t on t.object_id = c.object_id
join sys.schemas s on s.schema_id = t.schema_id
join sys.default_constraints d on c.default_object_id = d.object_id
where t.name = 'table'
and c.name = 'column'
and s.name = 'schema')
....
回答by David Sherret
I find this to be easier:
我觉得这更容易：
IF OBJECT_ID('SchemaName.MyConstraint', 'D') IS NULL
BEGIN
-- create it here
END
回答by Grzegorz Skoczylas
if not exists(select 1 from sys.default_constraints where name = 'SchemaName.MyConstraint')
begin
-- create it here
end
回答by Jerry Dodge
I was a bit puzzled as to why this simple task was so complicated. In my case, I don't have constraint names - only table and column names. I want to check if they already have a default before trying to add one.
我有点不明白为什么这个简单的任务会如此复杂。就我而言，我没有约束名称——只有表名和列名。我想在尝试添加一个之前检查他们是否已经有一个默认值。
After a bit more digging, I came up with this:
经过更多的挖掘，我想出了这个：
IF (SELECT Column_Default FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MY_TABLE' AND COLUMN_NAME = 'MY_COLUMN') is NULL
BEGIN
ALTER TABLE [dbo].[MY_TABLE]
ADD DEFAULT ('') FOR [MY_COLUMN]
END
GO
I have to implement this in a ginormous boilerplate script, so the shorter the better.
我必须在一个巨大的样板脚本中实现它，所以越短越好。
回答by MAXE
Search the system table where the default costraints of the database are stored, without the schema name:
搜索存储数据库默认 cotraint 的系统表，不带模式名称：
IF EXISTS(SELECT 1 FROM sys.default_constraints WHERE [name] = 'MyConstraint')
print 'Costraint exists!';
ELSE
print 'Costraint doesn''t exist!';
回答by Haim Raman
The following works for me on SQL Server 2016.
以下在 SQL Server 2016 上对我有用。
Assuming I have a table named MY_TABLE and a column MY_COLIUMN. I would like to add a constrain (default to '-1' ) on MY_COLIUMN that need to add the constrain on.
假设我有一个名为 MY_TABLE 的表和一个 MY_COLIUMN 列。我想在需要添加约束的 MY_COLIUMN 上添加一个约束（默认为 '-1' ）。
/* Test for the specific column */
IF EXISTS (select 1 from sys.all_columns c where c.object_id= OBJECT_ID(N'MY_TABLE') and c.name='MY_COLIUMN')
BEGIN
/* Add default if not exits */
IF NOT EXISTS (
select 1 from sys.default_constraints c where c.object_id =
(
select default_object_id from sys.all_columns c where c.object_id = OBJECT_ID(N'MY_TABLE') and c.name='MY_COLIUMN'
)
)
BEGIN
ALTER TABLE MY_TABLE
ADD DEFAULT '-1' FOR MY_COLIUMN;
END
END
GO
回答by KornMuffin
I've used the following in the past:
我过去使用过以下内容：
DECLARE @default sysname
SELECT @default = object_name( cdefault ) FROM syscolumns WHERE id = object_id( 'DBO.TABLE' ) AND name = 'COLUMN'
IF ( not @default is null )
BEGIN
...
END
