Set the default value of a column based on another column of a different data type

sql-server database-design sqldatatypes
kyle_13 picture kyle_13 · Oct 23, 2014 · Viewed 17.8k times · Source

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.

Screenshot 1:

enter image description here

Screenshot 2:

enter image description here

Answer

Raj More picture Raj More · Oct 23, 2014

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

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

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

Related questions

How are varchar values stored in a SQL Server database?

My fellow programmer has a strange requirement from his team leader; he insisted on creating varchar columns with a length of 16*2n. What is the point of such restriction? I can suppose that short strings (less than 128 chars for example) …

Read More
Difference between clustered and nonclustered index

I need to add proper index to my tables and need some help. I'm confused and need to clarify a few points: Should I use index for non-int columns? Why/why not I've read a lot about clustered and non-clustered …

Read More
How to create materialized views in SQL Server?

I am going to design a DW and I heard about materialized views. Actually I want to create a view and it should update automatically when base tables are changed. Can anyone explain with an query example..

Read More