标签云

微信群

扫码加入我们

WeChat QR Code

How can a column with a default value be added to an existing table in SQL Server 2000 / SQL Server 2005?


and this one as well w3schools.com/sql/sql_alter.asp

2019年08月19日29分28秒

Keep in mind that if the column is nullable, then null will be the value used for existing rows.

2019年08月19日29分28秒

Thecrocodilehunter Nullable column means that you can insert Null for the columns value. If it's not a nullable column, you have to insert some value of that data type. So, for existing records, Null will be inserted in them and in new records, your default value will be inserted unless otherwise specified. Make sense?

2019年08月19日29分28秒

I like this answer a little better than dbugger's because it explicitly names the default constraint. A default constraint is still created using dbugger's syntax, except its name is auto-generated. Knowing the exact name is handy when writing DROP-CREATE scripts.

2019年08月19日29分28秒

Vertigo That is ONLY true if the column is NOT NULL. Please try this: create table blah(a int not null primary key clustered); insert blah values (1), (2); alter table blah add b int null constraint df_blah_b default (0); select * from blah; You will see 2 NULL values for column b.

2019年08月19日29分28秒

Use WITH VALUES to update existing nullable rows. See MSDN: "If the added column allows null values and WITH VALUES is specified, the default value is stored in the new column, added to existing rows."

2019年08月19日29分28秒

The problem with that answer is that the default value is only valid for new records. Existing records will still have NULL value.

2019年08月19日29分28秒

You will find that is not the case.Otherwise the constraint would be violated.

2019年08月19日29分28秒

Columns in existing rows are filled with the default value.A little empirical test will prove it.

2019年08月19日29分28秒

Just to clarify - if "NOT NULL" is omitted from the command, the value for existing rows will NOT be updated and will remain NULL.If "NOT NULL" is included in the command, the value for existing rows WILL be updated to match the default.

2019年08月19日29分28秒

For multiple columnsALTER TABLE table_1 ADDcol_1 int NOT NULL DEFAULT(1), col_2 int NULL

2019年08月19日29分28秒

This is a key point.It's easy to assume a column with a DEFAULT constraint will always have a value - that is, not be NULL, even though NOT NULL isn't specified.

2019年08月19日29分28秒

tkocmathla uh, I was not talking about the BIT datatype, I was talking about this particular BIT column. Look at the answer, the column is declared as NOT NULL.

2019年08月19日29分28秒

This won't work if the table already has content because the new "not nullable" column is created before the default value constraint

2019年08月19日29分28秒

My +1 goes for the snippet after the first GO which adds a constraint to an existing column.

2019年08月19日29分28秒

this add null!has to be not null before

2019年08月19日29分28秒

baaroz , this works with NOT NULL:ALTER TABLE MYTABLE ADD MYNEWCOLUMN VARCHAR(200) NOT NULL DEFAULT 'SNUGGLES'

2019年08月19日29分28秒

any SQL sample about it ?

2019年08月19日29分28秒

FYI, explicitly naming the constraint using a standard naming scheme (e.g. "DF_Table_Column"), for ease of maintenance. Otherwise, locating the constraint requires more work.

2019年08月19日29分28秒

I like the NOT EXISTS check before attempting to alter the table.Very good solution.Some additional commentary on how this works would make it even more useful.

2019年08月19日29分28秒

Maybe replace the image with actual code? Hard to copy and paste as-is.

2019年08月19日29分28秒

DavidFaber now you can copy.

2019年08月19日29分28秒

This does not add any value over the already existing answers from years ago.

2019年08月19日29分28秒

This does not add any value over the already existing answers from years ago.

2019年08月19日29分28秒