sql server constraint lookup Archives -

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;