根据不同数据类型的另一列设置列的默认值

时间:2021-12-14 16:55:52

I'm creating a new table in SQL table designer, and I'd like the "Default Value or Binding" of the column to be based off the value of another column in the same table. So I'd like Column A to get it's value from a substring of Column B. Column A is a numeric data type, Column B is varchar. Right now I have the following, but get the message in screenshot 2...is there something wrong here? I also had the expression CONVERT(numeric (2,0), SUBSTRING(col_b,3,2)) and that gave the same message.

我正在SQL表设计器中创建一个新表,我希望列的“默认值或绑定”基于同一个表中另一列的值。所以我希望列A从列B的子字符串中获取它的值。列A是数字数据类型,列B是varchar。现在我有以下内容,但在屏幕截图2中得到消息......这里有什么问题吗?我也有表达CONVERT(数字(2,0),SUBSTRING(col_b,3,2))并给出相同的消息。

Screenshot 1:

截图1:

根据不同数据类型的另一列设置列的默认值

Screenshot 2:

截图2:

根据不同数据类型的另一列设置列的默认值

1 个解决方案

#1


9  

I don't think you can set the DEFAULT value of one column based on another column using the DEFAULT CONSTRAINT.

我不认为您可以使用DEFAULT CONSTRAINT基于另一列设置一列的DEFAULT值。

You may want to consider a computed column if all you're looking to do is replace a NULL value. http://msdn.microsoft.com/en-us/library/ms188300.aspx

如果您要查找的所有内容都替换为NULL值,则可能需要考虑计算列。 http://msdn.microsoft.com/en-us/library/ms188300.aspx

In extreme cases, you can do exactly what you want in a TRIGGER. Read up on INSTEAD OF INSERT triggers. http://technet.microsoft.com/en-us/library/ms175089(v=sql.105).aspx

在极端情况下,您可以在TRIGGER中完全按照自己的意愿行事。阅读INSTEAD OF INSERT触发器。 http://technet.microsoft.com/en-us/library/ms175089(v=sql.105).aspx

#1


9  

I don't think you can set the DEFAULT value of one column based on another column using the DEFAULT CONSTRAINT.

我不认为您可以使用DEFAULT CONSTRAINT基于另一列设置一列的DEFAULT值。

You may want to consider a computed column if all you're looking to do is replace a NULL value. http://msdn.microsoft.com/en-us/library/ms188300.aspx

如果您要查找的所有内容都替换为NULL值,则可能需要考虑计算列。 http://msdn.microsoft.com/en-us/library/ms188300.aspx

In extreme cases, you can do exactly what you want in a TRIGGER. Read up on INSTEAD OF INSERT triggers. http://technet.microsoft.com/en-us/library/ms175089(v=sql.105).aspx

在极端情况下,您可以在TRIGGER中完全按照自己的意愿行事。阅读INSTEAD OF INSERT触发器。 http://technet.microsoft.com/en-us/library/ms175089(v=sql.105).aspx