ADD Constraint - set default 0

  • 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


    Human Knowledge Belongs To The World !!

  • ALTER TABLE dbo.myTable ADD CONSTRAINT DF_myTable_Q DEFAULT(0) FOR Quota WITH VALUES

  • 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.

  • Ian Scarlett (9/9/2009)


    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.

    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

  • 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".

  • 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 (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)

  • Is manual update(from NULL to 0), the only solution ?

    -

    KB

    Thanks,
    Santhosh


    Human Knowledge Belongs To The World !!

  • 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 (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


    Human Knowledge Belongs To The World !!

  • 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]

  • 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 (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

  • 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


    Human Knowledge Belongs To The World !!

  • KB (9/9/2009)


    Its 1,700,000 rows.

    Hmmm...Will a simple update statement do this?

    Yes 🙂

Viewing 15 posts - 1 through 15 (of 26 total)

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