将列默认值绑定到 SQL 2005 中的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/442503/
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
Bind a column default value to a function in SQL 2005
提问by Luk
I have a column containing items that can be sorted by the user:
我有一列包含可由用户排序的项目：
DOC_ID DOC_Order DOC_Name
1 1 aaa
2 3 bbb
3 2 ccc
I'm trying to figure out a way to properly initialize DOC_Order when the entry is created. A good value would either be the corresponding DO-CID (since it is autoassigned), or MAX(DOC-ORDER) + 1
我试图找出一种在创建条目时正确初始化 DOC_Order 的方法。一个好的值要么是相应的 DO-CID（因为它是自动分配的），要么是 MAX(DOC-ORDER) + 1
After a bit of googling I saw it was possible to assign a scalar function's return to the default column.
经过一番谷歌搜索后，我发现可以将标量函数的返回值分配给默认列。
CREATE FUNCTION [dbo].[NEWDOC_Order]
(
)
RETURNS int
AS
BEGIN
RETURN (SELECT MAX(DOC_ORDER) + 1 FROM DOC_Documents)
END
But each of my tries using MS SQL Management studio ended in a "Error validating the default for column 'DOC_Order'" message.
但是我每次使用 MS SQL Management Studio 的尝试都以“错误验证‘DOC_Order’列的默认值”消息结束。
Any idea of what the exact SQL syntax to assign a function to DEFAULT is?
知道将函数分配给 DEFAULT 的确切 SQL 语法是什么吗？
回答by cmsjr
The syntax to add a default like that would be
添加这样的默认值的语法是
alter table DOC_Order
add constraint
df_DOC_Order
default([dbo].[NEWDOC_Order]())
for DOC_Order
Also, you might want to alter your function to handle when DOC_Order is null
此外，当 DOC_Order 为 null 时，您可能希望更改函数以进行处理
Create FUNCTION [dbo].[NEWDOC_Order]
(
)
RETURNS int
AS
BEGIN
RETURN (SELECT ISNULL(MAX(DOC_ORDER),0) + 1 FROM DOC_Documents)
END
回答by Luk
IF someone wants to do it using the interface, typing
如果有人想使用界面来做这件事，输入
[dbo].[NEWDOC_Order]()
does the trick. You apparently need all brackets or it will reject your input.
诀窍。您显然需要所有括号，否则它会拒绝您的输入。
回答by Tony L.
Here's screen shots to do it through SQL Server Management Studio GUI:
以下是通过 SQL Server Management Studio GUI 执行此操作的屏幕截图：
- Right click on table and select
Design
- 右键单击表格并选择
Design
- Select DOC_Ordercolumn (or other column needing default) in the table's design view to see properties
- 在表的设计视图中选择DOC_Order列（或其他需要默认的列）以查看属性
- Update
Default Value or Bindingwith function name with brackets like so:
Default Value or Binding用带括号的函数名称更新，如下所示：
Note: as Luk stated, all brackets are needed including the schema (dboin this case).
注意：正如 Luk 所说，需要所有括号，包括架构（在本例中为dbo）。
