In SQL Server 2000/2005,
Is it possible to force the default value to be written to already existing rows when adding a new column to a table without using NOT NULL on the new column?
When using CREATE TABLE , you can specify default values for columns by typing DEFAULT and then the desired value after it. If a row is inserted that does not specify a value for that column, the database will fill it in with the default value instead.
ALTER TABLE table_name MODIFY COLUMN column_name datatype; The basic syntax of an ALTER TABLE command to add a NOT NULL constraint to a column in a table is as follows. ALTER TABLE table_name MODIFY column_name datatype NOT NULL; The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows.
CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated. DEFAULT (0)--Optional Default-Constraint. WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.
You need two statements. First create the column with not null. Then change the not null constraint to nullable
alter table mytable add mycolumn varchar(10) not null default ('a value')
alter table mytable alter column mycolumn varchar(10) null
I understand your question, but you are saying that for future records, NULL (unknown, indeterminate or whatever your semantics are) is acceptable (but if it is left off in an insert, there will be a default), but that for all the existing data, you are going to go ahead and assign it the default.
I would have to look hard at this situation and ask why you are even going to allow NULLs in future records at all - given none of the historical records will have it, and there is a default in place for future records.
I doubt it.
http://msdn.microsoft.com/en-us/library/ms190273(SQL.90).aspx
The approach recommended by Microsoft is as follows (taken from the url above)
UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL
ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With