SQL DEFAULT: Default Constraint

The DEFAULT constraint is used to set a default value for a column when no value is specified for that column while inserting data. The DEFAULT constraint can be used while creating or altering a table.

Syntax to use DEFAULT while creating a table:

CREATE TABLE table_name (
    column1 datatype DEFAULT value,
    ....
);

Syntax to use DEFAULT while altering a table:

ALTER TABLE table_name 
ALTER COLUMN column_name SET DEFAULT value;

Example:

Let's create a table named Students with columns ID, Name, and Age, where Age has a default value of 18:

CREATE TABLE Students (
    ID int PRIMARY KEY,
    Name varchar(255),
    Age int DEFAULT 18
);

Now, if you insert a record without specifying the Age, the Age will default to 18.

INSERT INTO Students (ID, Name) VALUES (1, 'John Doe');

If you now retrieve the data with:

SELECT * FROM Students;

You will see that John Doe's age is set to 18 even though you didn't specify it when you inserted the record.

Note: DEFAULT can also be a function, such as CURRENT_TIMESTAMP, which sets the default value of the column to the current date and time.

Always consult the documentation of the specific SQL dialect you are using as there may be variations in the syntax and functionality.

Setting Default Values in SQL Columns:
- Description: Specifies a default value for a column that is used when a new row is inserted without explicitly specifying a value for that column.
- Code Example:
```
CREATE TABLE ExampleTable (
    Column1 INT DEFAULT 0,
    Column2 VARCHAR(255) DEFAULT 'DefaultText'
);
```
Using DEFAULT in CREATE TABLE Statement:
- Description: Sets default values for one or more columns during table creation.
- Code Example:
```
CREATE TABLE ExampleTable (
    Column1 INT DEFAULT 0,
    Column2 VARCHAR(255) DEFAULT 'DefaultText'
);
```
Modifying Existing Columns with DEFAULT in SQL:
- Description: Adds or modifies a default value for an existing column.
- Code Example:
```
ALTER TABLE ExampleTable
ALTER COLUMN Column1 SET DEFAULT 10;
```
Removing DEFAULT Constraint in SQL:
- Description: Removes the default value from a column.
- Code Example:
```
ALTER TABLE ExampleTable
ALTER COLUMN Column1 DROP DEFAULT;
```

Default Values for NULL in SQL:

Description: A default value can also be specified for NULL values.

Code Example:

CREATE TABLE ExampleTable (
    Column1 INT DEFAULT 0,
    Column2 VARCHAR(255) DEFAULT 'DefaultText',
    Column3 INT DEFAULT NULL
);

SQL DEFAULT Constraint and Data Integrity:
- Description: DEFAULT constraints contribute to data integrity by ensuring a valid default value when a new row is inserted.
- Code Example:
```
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE DEFAULT GETDATE()
);
```

Applying DEFAULT to Multiple Columns:

Description: Multiple columns can have their own default values.

Code Example:

CREATE TABLE ExampleTable (
    Column1 INT DEFAULT 0,
    Column2 VARCHAR(255) DEFAULT 'DefaultText',
    Column3 DATETIME DEFAULT GETDATE()
);

Default Constraint with INSERT Statement in SQL:
- Description: The default value is applied when an INSERT statement does not provide a value for the column.
- Code Example:
```
INSERT INTO ExampleTable (Column1, Column2)
VALUES (NULL, 'NewValue'); -- Column1 will take its default value
```