Day 40 – SQL DEFAULT Constraint
In SQL, a DEFAULT constraint is used to set a default value for a column when a new row is inserted into the table and the value for that column is not provided. The DEFAULT constraint is defined at the time of creating the table or added later using the ALTER TABLE statement.
The syntax for defining a DEFAULT constraint when creating a table is as follows:
CREATE TABLE table_name (
column1 datatype DEFAULT default_value,
column2 datatype,
column3 datatype,
.....
);
In the above syntax, column1 is the name of the column on which the DEFAULT constraint is defined, datatype is the data type of the column, and default_value is the value that will be assigned to the column if no value is specified during insertion.
The syntax for adding a DEFAULT constraint to an existing table is as follows:
ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;
In the above syntax, column_name is the name of the column on which the DEFAULT constraint is being added, and default_value is the value that will be assigned to the column if no value is specified during insertion.
For example, let’s say we have a table named “employees” with a column “hire_date”, and we want to set the default value of hire date to the current date when a new row is inserted into the table. We can define a DEFAULT constraint on the hire_date column as follows:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
hire_date DATE DEFAULT CURRENT_DATE,
salary DECIMAL(10,2)
);
In the above example, we have defined a DEFAULT constraint on the hire_date column to set the default value to the current date if no value is provided during insertion.
When a DEFAULT constraint is defined, if no value is specified during insertion, the default value will be assigned to the column. If a value is provided during insertion, the specified value will be assigned to the column instead of the default value.
