SQL DEFAULT Constraint

The DEFAULT constraint is used to provide a default value for a column in a table. If the INSERT INTO statement does not specify a value for a column with a DEFAULT constraint, the default value will be used instead.

Here's an example of how to create a table with a DEFAULT constraint:

1CREATE TABLE Orders (
2   OrderID int,
3   CustomerID int,
4   OrderDate date DEFAULT getdate()
5);

In this example, the OrderDate column will use the current date as the default value if no value is specified during an INSERT INTO statement.

Here's an example of inserting a new row into the Orders table:

1INSERT INTO Orders (OrderID, CustomerID)
2VALUES (1, 10);

In this example, the OrderDate column will use the default value of the current date because no value was specified during the INSERT INTO statement.

SQL DEFAULT Constraint on ALTER TABLE

To alter a DEFAULT constraint, use the ALTER TABLE statement:

1ALTER TABLE Orders ALTER COLUMN OrderDate SET DEFAULT '2022-01-01';

In this example, the default value for the OrderDate column is being changed from the current date to the specified date of '2022-01-01'.

SQL DROP DEFAULT Constraint

To drop a DEFAULT constraint, use the ALTER TABLE statement:

1ALTER TABLE Orders ALTER COLUMN OrderDate DROP DEFAULT;

In this example, the DEFAULT constraint is being dropped from the OrderDate column.