In DEFAULT, how to refer to another column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

In DEFAULT, how to refer to another column

Author

Topic

TimSinnott
Starting Member

48 Posts

Posted - 2002-10-03 : 18:57:24

Hi all,

When I set the DEFAULT of a one column, I want to utilize the value of another column. Example:

......
create table t1 (
col1 int identity(1,1) PRIMARY KEY,
col2 int DEFAULT (col1 + 100)
)
.....

But this doesn't work. Does anybody know how?

Thanks in advance.

Tim

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-03 : 19:04:05

DEFAULTs can't reference another column, but you can do it with a trigger:

CREATE TRIGGER SetCol2 ON t1 FOR INSERT, UPDATE AS
IF EXISTS (SELECT * FROM inserted WHERE col2 IS NULL)
BEGIN
UPDATE t1 SET col2=I.col1+100
FROM t1 INNER JOIN inserted I ON t1.col1=I.col1
WHERE I.col2 IS NULL
END

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-10-03 : 19:06:03

Tim,
I think you could also set that field to IDENTITY as well, and just have it start at 101 instead of 1.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

TimSinnott
Starting Member

48 Posts

Posted - 2002-10-03 : 19:16:27

Thanks, Michael. Your's is the most efficient solution to the stated problem, alright.

Rob, thanks. That trigger idea is perfect. That's what I really needed to know. Many thanks.

Tim

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-03 : 19:18:15

Unfortunately:

A. You can only have one identity column per table;
B. Even if you could have more, you couldn't explicitly INSERT a value into it. I imagine he wants the option of inserting a different value than 100+col1.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-10-03 : 19:46:23

Rob,
I guess that shows you how much I use identities :) I just started using them. I've been a GUID man in the past, but I'm moving more towards natural keys and identities.

One must wonder what in the world that "identity + 100" field is. I'm guessing some sort of order number or something?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

TimSinnott
Starting Member

48 Posts

Posted - 2002-10-03 : 20:26:03

Michael, my main question or need is how to set a Default for one column with reference to a value of one or more other columns.

I made up the simplified example in order to communicate my question, but I guess my example was a bit confusing. :|

It looks like a trigger gives me an enormous amount of flexibility in doing what I need to do, though. I hadn't thought of that.

Thanks for your help. :)

Tim

Edited by - TimSinnott on 10/03/2002 23:03:27

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-03 : 22:22:15

how bout


create table t1 (
col1 int identity(1,1) NOT FRIGGIN NULL PRIMARY KEY,
col2 as col1 + 100 )

Jay White
{0}

r937
Posting Yak Master

112 Posts

Posted - 2002-10-03 : 22:41:13

another option? (sorry, not tested):

to allow col2 to be null

for those instances where it should be equal to col1 # 100, leave it null

for those instances where it needs to be assigned a different value, assign a different value (otherwise, why have this column at all?)

declare a view with

CASE WHEN COL2 IS NULL
THEN COL1 # 100
ELSE COL2 END AS COL2

please note the hash characters above should be plus signs -- how do you type a plus sign and not have it disappear?

rudy
http://rudy.ca/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-04 : 09:50:48

quote:
please note the hash characters above should be plus signs -- how do you type a plus sign and not have it disappear?

Actually, the plus signs only disappear in the preview window, they show up fine in the regular window.

The Snitz code uses cookies to transfer the text to the preview window, and a "+" is converted to a space due to character escaping.

r937
Posting Yak Master

112 Posts

Posted - 2002-10-04 : 10:06:52

+++++ thanks +++++

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources