DEFAULT CONSTRAINT

            Using default constraint we can insert default value into a column, if no other value is specified for that particular column.

Applying constraint on a new table:

Syntax:

CREATE TABLE <Table name>

(

<Col 1> <Data type>,

<Col 2> <Data type>,

.

.

<Col n> <Data type> DEFAULT <Value>

)

Example:

CREATE TABLE DEFAULT_TB1

(

ID INT

,Name varchar(10) DEFAULT 'aa'

)

Data insertion:

Inserting value to the ‘Name’ column:

If we specified a value for name column then same value will be inserted.

INSERT INTO DEFAULT_TB1 (ID,Name) VALUES (102,'ab')

O/P: SELECT * FROM DEFAULT_TB1

Inserting default value to the ‘Name’ column:

If we are not specified a value for name column then default value will be inserted.

INSERT INTO DEFAULT_TB1 (ID) VALUES (102)

O/P: SELECT * FROM DEFAULT_TB1

Applying constraint on existing table:

Syntax:

ALTER TABLE <Table name> add constraint <Constraint name> DEFAUTL <Default value> FOR <Column name>

Example:

ALTER TABLE DEFAULT_TB2 ADD CONSTRAINT DFC_Name2 DEFAULT 'AA' FOR Name

O/P: SP_HELP DEFAULT_TB2

Drop constraint:

ALTER TABLE DEFAULT_TB DROP CONSTRAINT DFC_Name2