June 17, 2008 at 4:14 am
I have a table that looks something like this:
CREATE TABLE Test_Defaults
( column_a INT
, column_b VARCHAR(50)
, column_c VARCHAR(50)
,column_d INT
) ;
GO
INSERT INTO Test_Defaults
VALUES (1,'abc','def') ;
GO
My question here is:
I need to set defaults for the first three columns in this table. How can I alter this table in a single query statement?
Is this the correct way to do it:
ALTER TABLE Test_Defaults
ADD CONSTRAINT col_b_def
DEFAULT 'no value' FOR column_b,
DEFAULT 'still no value' FOR column_c,
DEFAULT 0 FOR column_a
Why do we name all constraints by the same name here? Aren't all three supposed to be independednt of each other?!
I've also seen he following statement:
ALTER TABLE Test_Defaults
ALTER COLUMN column_a SET DEFAULT 0
but it gives me an error:
Incorrect syntax near the keyword 'SET'.
Can someone correct this for me?
Also, how can I use SET DEFAULT for multiple columns in a single statement?
June 17, 2008 at 4:20 am
I'm sorry about the topic name typo! I meant DEFAULT CONSTRAINT 🙁
June 17, 2008 at 4:42 am
>> Why do we name all constraints by the same name here?
Same name is not used for all the constraints. It is used for only for the first one For rest of them, system generated name is used.
You can write as follows:
ALTER TABLE Test_Defaults
ADD CONSTRAINT col_b_def DEFAULT 'no value' FOR column_b,
CONSTRAINT col_c_def DEFAULT 'still no value' FOR column_c,
CONSTRAINT col_d_def DEFAULT 0 FOR column_a
June 17, 2008 at 4:55 am
Ok! That makes sense! Thanks Suresh!
So that's the way I'm supposed to ALTER a table to set default values to multiple columns then? Especially since I'm looking for a single statement query...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply