September 9, 2009 at 1:03 am
ALTER TABLE dbo.myTable ADD CONSTRAINT DF_myTable_Q DEFAULT(0) FOR Quota
If I execute the above code, the default constraint will be set for the rows that will be inserting from now on...
what about existing values(NULL)? Do I have to set them to 0 manually?
Is there a way to set them to 0 when a default constraint is added?
Also the column should be set to NOT NULL.
Thanks,
KB
Thanks,
Santhosh
September 9, 2009 at 1:22 am
ALTER TABLE dbo.myTable ADD CONSTRAINT DF_myTable_Q DEFAULT(0) FOR Quota WITH VALUES
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 9, 2009 at 4:33 am
Paul White (9/9/2009)
ALTER TABLE dbo.myTable ADD CONSTRAINT DF_myTable_Q DEFAULT(0) FOR Quota WITH VALUES
WITH VALUES only works when you are also adding the column at the same time. Just adding the default constraint to an existing column doesn't change the null values to the default.
September 9, 2009 at 5:22 am
Ian Scarlett (9/9/2009)
Paul White (9/9/2009)
ALTER TABLE dbo.myTable ADD CONSTRAINT DF_myTable_Q DEFAULT(0) FOR Quota WITH VALUESWITH VALUES only works when you are also adding the column at the same time. Just adding the default constraint to an existing column doesn't change the null values to the default.
Well I've learnt something today then. :w00t:
I'm quite surprised the statement I wrote compiles and runs without error in that case - really I am. Books Online has ALTER TABLE ADD {table constraint} as accepting the WITH VALUES clause. It also says in the notes that WITH VALUES can only be specified in an ADD column clause. So, that's not strictly true then! (My statement doesn't add a new column.)
Also, how would one add a column at the same time as a table constraint? How would that ALTER table statement look?
Actually, my guess is that any constraint added with a new column is regarded as a table constraint, rather than a column constraint, but that not documented as far as I can see. I vaguely remember reading that there isn't any metadata difference between table and column constraints anyway - just to muddy things further.
Oh well. Back to the drawing board then. It would have been nice had it worked 🙁
So, we're stuck with updating the NULL column values manually before adding the new constraint...?
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 9, 2009 at 5:57 am
I wrote a detailed reply to this, but it got lost when the website went down, and I couln't summon the enthusiasm to write it all again.
I'm quite surprised the statement I wrote compiles and runs without error in that case - really I am.
Me too! Looks like the parser just treats it like a DEFAULT phrase regardless of where it's used.
Also, how would one add a column at the same time as a table constraint? How would that ALTER table statement look?
No different to a normal column definition. Pretty it isn't.
alter table dbo.myTable ADD fred int null CONSTRAINT DF_Fred DEFAULT 0 with values
So, we're stuck with updating the NULL column values manually before adding the new constraint...?
That was the conclusion I came to when writing my "lost post".
September 9, 2009 at 6:33 am
Shame about the lost post Ian. It is a sad reflection on this site that I automatically hit CTRL+A, CTRL+C before hitting the button!
Thanks for seeing what I was going on about. :w00t:
The only bit I think I didn't quite get across was the "how would one add a column at the same time as a table constraint" bit. The example you gave was the one I tried - it adds a column constraint 🙂
That's why I went on so much about table versus column constraints. Perhaps there is no difference, but the syntax guide in BOL certainly makes it look so.
I suppose I should ease up on BOL - it is an amazing piece of work, it's just that the odd errors and inconsistencies are bothersome.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 9, 2009 at 6:53 am
Paul White (9/9/2009)
The only bit I think I didn't quite get across was the "how would one add a column at the same time as a table constraint" bit. The example you gave was the one I tried - it adds a column constraint
Do you mean something like this?
alter table dbo.myTable ADD fred int null CONSTRAINT DFx DEFAULT 0 with values, constraint TBx check(fred < id)
September 9, 2009 at 6:59 am
Is manual update(from NULL to 0), the only solution ?
-
KB
Thanks,
Santhosh
September 9, 2009 at 7:07 am
KB (9/9/2009)
Is manual update(from NULL to 0), the only solution ?
There is rarely only one solution to any problem in SQL Server!
It might be easiest and as good as any other in this case, unless you have an extremely large table, in which case a bulk technique would be called for. Is that the case here?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 9, 2009 at 7:10 am
Paul White (9/9/2009)
KB (9/9/2009)
Is manual update(from NULL to 0), the only solution ?There is rarely only one solution to any problem in SQL Server!
It might be easiest and as good as any other in this case, unless you have an extremely large table, in which case a bulk technique would be called for. Is that the case here?
It has around 17 lacs of records.
So i think it can be done through a simple update statement
-
KB
Thanks,
Santhosh
September 9, 2009 at 7:12 am
Ian Scarlett (9/9/2009)
alter table dbo.myTable ADD fred int null CONSTRAINT DFx DEFAULT 0 with values, constraint TBx check(fred < id)
Almost, yes. But the DEFAULT...WITH VALUES is still the 'column constraint' there. The CHECK constraint after the comma looks more like a table constraint to me. Try reversing the order:
alter table dbo.myTable ADD fred int null constraint TBx check(fred < id), CONSTRAINT DFx DEFAULT 0 with values
[font="Courier New"]Msg 142, Level 15, State 2, Line 0
Incorrect syntax for definition of the 'TABLE' constraint.[/font]
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 9, 2009 at 7:13 am
KB (9/9/2009)
It has around 17 lacs of records.So i think it can be done through a simple update statement
Cool. What are 'lacs' though? :unsure:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 9, 2009 at 7:24 am
Paul White (9/9/2009)
Almost, yes. But the DEFAULT...WITH VALUES is still the 'column constraint' there. The CHECK constraint after the comma looks more like a table constraint to me.
Right, I am now confused:unsure:
Also, how would one add a column at the same time as a table constraint? How would that ALTER table statement look?
What sort of table constraint did you want to add at the same time as the column constraint?
BTW I think a lac is Indian for 100,000
September 9, 2009 at 7:26 am
Paul White (9/9/2009)
KB (9/9/2009)
It has around 17 lacs of records.So i think it can be done through a simple update statement
Cool. What are 'lacs' though? :unsure:
Its 1,700,000 rows.
Hmmm...Will a simple update statement do this?
Thanks,
Santhosh
September 9, 2009 at 7:27 am
KB (9/9/2009)
Its 1,700,000 rows.Hmmm...Will a simple update statement do this?
Yes 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply