Default Constraints rather than constraining any data like primary key, foreign key or check constraints it just supply value to a column with default constraint if no other value passed to it during insert operation.
To create a default constraint on a new table
CREATE TABLE table1 ( col1 INT, col2 VARCHAR(20), col3 BIT CONSTRAINT df_col3 DEFAULT (0) ); go |
To add default constraint on existing table
ALTER TABLE table1 ADD CONSTRAINT df_col3 DEFAULT (0) FOR col3; go |
To see our default constraint is created properly we could check as
SELECT * FROM sys.default_constraints WHERE parent_object_id = OBJECT_ID('dbo.table1'); |
When adding a default constratin to a table with existing rows if we want to update null values with the new default value automatically we could add WITH VALUES argument as
ALTER TABLE table1 add Col4 INT CONSTRAINT df_col4 default (1) WITH VALUES; |
Read More
http://technet.microsoft.com/en-us/library/aa175912(v=sql.80).aspx
http://technet.microsoft.com/en-us/library/ms187742.aspx