根据其他数据类型的另一列设置列的默认值 [英] Set the default value of a column based on another column of a different data type

问题描述

我正在SQL表设计器中创建一个新表，我希望该列的默认值或绑定基于同一表中另一列的值。因此，我希望列A从列B的子字符串中获取其值。列A是数字数据类型，列B是 varchar 。现在，我有以下内容，但是在屏幕截图2中得到了消息...这里有问题吗？我也有表达式 CONVERT（numeric（2,0），SUBSTRING（col_b，3,2）），它给出了相同的消息。

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.

截屏1：

截屏2：

推荐答案

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

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

您可能想要如果您只想替换一个NULL值，请考虑计算列。 http://msdn.microsoft.com/en-us/library/ms188300.aspx

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

在极端情况下，您可以完全按照触发条件进行操作。阅读INSTEAD OF INSERT触发器。 http://technet.microsoft.com/en-us/library/ms175089 （v = sql.105）.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

这篇关于根据其他数据类型的另一列设置列的默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！