How do you create a table with a DEFAULT constraint to provide default values for columns?
GraceDv
Posted by GraceDv
Last Updated: August 04, 2024
table constraint
To create a table with a DEFAULT constraint in SQL, you define the DEFAULT value directly in the column specification during the CREATE TABLE statement. The DEFAULT constraint allows you to specify a value that will be used when no value is provided for that column during an INSERT operation. Here's the general syntax for creating a table with one or more columns having default values: 
CREATE TABLE table_name (
    column1 datatype DEFAULT default_value,
    column2 datatype DEFAULT default_value,
    column3 datatype,
    ...
);
Example
Here’s an example of creating a table called employees where the status column has a default value of 'active', and the hire_date column has a default value of the current date: 
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    status VARCHAR(10) DEFAULT 'active',
    hire_date DATE DEFAULT CURRENT_DATE
);
Explanation of the Example:
- employee_id: An integer that serves as the primary key. - first_name: A variable character field that cannot be null. - last_name: Another variable character field that cannot be null. - status: A variable character field with a default value of 'active'. If no value is provided during insertion, this value will be used. - hire_date: A date field with a default value of the current date. If no date is specified during insertion, the CURRENT_DATE is used.
Inserting Data
When you insert data into the employees table, if you omit the status or hire_date, the default values will automatically be applied: 
INSERT INTO employees (employee_id, first_name, last_name) 
VALUES (1, 'John', 'Doe');

INSERT INTO employees (employee_id, first_name, last_name, status) 
VALUES (2, 'Jane', 'Smith', 'inactive');
In the first INSERT statement, status will default to 'active' and hire_date will default to the current date. In the second statement, status will be explicitly set to 'inactive', while hire_date will still default to the current date. This feature of SQL helps in maintaining consistent data and simplifies the INSERT operations by reducing the number of required fields in some cases.
Default Image