Logic in a default constraint?

Question

Post reply

Logic in a default constraint?

Khades

SSCrazy

Points: 2067
More actions
March 10, 2015 at 6:36 pm

#301945

We have just a regular identity column present. The requirement is to add another column which needs to have the value of 'A' + DATE of when it was inserted + ID
Something like this:
CREATE TABLE #Temp(ID INT IDENTITY(1,1), Name VARCHAR(50), CalculatedColumn VARCHAR(30))
ALTER TABLE #Temp
ADD CONSTRAINT DF_CC DEFAULT 'A' + CONVERT(VARCHAR(10), GETDATE(), 112) + CONVERT(VARCHAR(10), ID, 0)
FOR CalculatedColumn
Can't use another column in a default apparently. It will also not let me to do this with a persisted computed column since I'm using Varchar. Is there any other way to do this besides making the processes that insert into this table do the logic?

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

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

tomaz.kastrun SSCrazy Points: 2141 More actions · Answer 1

Hi,

you can use this:

create table #temp

(

id int identity(1,1)

,name varchar(50)

,calc as char(65) + convert(varchar(10), getdate(),112) + convert(varchar(20), id, 0)

)

and it should work.

Best, Tomaž

Tomaž Kaštrun | twitter: @tomaz_tsql | Github: https://github.com/tomaztk | blog: https://tomaztsql.wordpress.com/

Khades SSCrazy Points: 2067 More actions · Answer 2

tomaz.kastrun (3/11/2015)
Hi,
you can use this:
create table #temp
(
id int identity(1,1)
,name varchar(50)
,calc as char(65) + convert(varchar(10), getdate(),112) + convert(varchar(20), id, 0)
)
and it should work.
Best, Tomaž

The problem with this is that every time you do a select it will have a different value. We need the date to be the time when the record was inserted and not when you select it.

Lowell SSC Guru Points: 323518 More actions · Answer 3

Khades i tried with a user defined function as the default as wlel, doesn't work;

i think you're stuck with adding an trigger for insert in order to get your default value.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Khades SSCrazy Points: 2067 More actions · Answer 4

Lowell (3/11/2015)
Khades i tried with a user defined function as the default as wlel, doesn't work;
i think you're stuck with adding an trigger for insert in order to get your default value.

Thanks, that's actually a pretty good idea.

I'm going to speak with architecture to see if it's a good idea for us to also add a create_date column with a getdate() default, and the computed column just concatenates the two. If not, I'll go with the trigger.

Dwain Camps SSC Guru Points: 86978 More actions · Answer 5

Why can't you do this?

CREATE TABLE #Temp

(

ID INT IDENTITY(1,1)

,Name VARCHAR(50)

,InsertedDT DATETIME DEFAULT(GETDATE())

,CalculatedColumn AS ('A' + CONVERT(VARCHAR(10), InsertedDT, 112) + RIGHT(1000000000+ID, 9))

);

INSERT INTO #Temp (Name)

SELECT 'ABC';

SELECT *

FROM #Temp;

GO

DROP TABLE #Temp;

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St