create table, set default column value to value of another column

Question

create table, set default column value to value of another column

Marcus Farrugia

SSCarpal Tunnel

Points: 4718
More actions
March 11, 2015 at 8:16 am

#315642

Hi, when creating a new table. How can I set the default value of the column to equal the value of another column in the same table?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 23104 More actions · Answer 1

There's probably quite a few ways of doing this but to give a useful answer we'll need some sample data please. How are you creating and populating the tables?

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Marcus Farrugia SSCarpal Tunnel Points: 4718 More actions · Answer 2

So in the table below, I would like the appKey value to = riKey value as a default value ...

CREATE TABLE [dbo].[riKeySchema](

[keyID] [int] IDENTITY(1,1) NOT NULL,

[riKey] [varchar](250) NOT NULL,

[appKey] [varchar](250) NULL,

[appName] [varchar](250) NULL,

[appVersion] [varchar](250) NULL,

[appFlavour] [varchar](250) NULL,

[dateAdded] [datetime] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[riKeySchema] ADD CONSTRAINT [DF_riKeySchema_appKey] DEFAULT (select riKey) FOR [appKey]

GO

ALTER TABLE [dbo].[riKeySchema] ADD CONSTRAINT [DF_riKeySchema_dateAdded] DEFAULT (getdate()) FOR [dateAdded]

GO

Neil Burton SSC-Insane Points: 23104 More actions · Answer 3

Could you not do that as part of your insert statement? It's a bit of a chicken and egg situation otherwise.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Marcus Farrugia SSCarpal Tunnel Points: 4718 More actions · Answer 4

Hi BWFC, yes I could and use a coalesce in a select, this question came to mind when I was creating the table, and thought it would be possible, thanks for taking time to respond ...

Neil Burton SSC-Insane Points: 23104 More actions · Answer 5

You're welcome, I'm glad I could help.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537