SQL DEFAULT Constraint

SQL DEFAULT Constraint

The SQL DEFAULT constraint is used to provide a default value for a column when no value is specified. This command is essential for ensuring that a column always has a valid value, even if the user does not provide one.

---

Syntax

-- To create a default constraint when creating a table
CREATE TABLE table_name (
    column1 datatype DEFAULT default_value,
    column2 datatype,
    ...
);

-- To add a default constraint to an existing table
ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;

• CREATE TABLE: This is the SQL keyword used to create a new table.
• ALTER TABLE: This is the SQL keyword used to modify an existing table.
• DEFAULT: This is the SQL keyword used to define a default value for a column.
• column1, column2, ...: These specify the names of the columns in the table.
• datatype: This specifies the type of data the column can hold, such as INTEGER, VARCHAR, DATE, etc.
• default_value: This specifies the default value for the column.
• table_name: This specifies the name of the table to create or modify.
• column_name: This specifies the name of the column to set the default value for.

---

Example

Let's go through a complete example that includes creating a database, creating a table with a DEFAULT constraint, and inserting data into the table.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table with a DEFAULT Constraint

In this step, we create a table named employees within the previously created database, defining a DEFAULT constraint on the hire_date column to default to the current date if no date is provided.

USE example_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    hire_date DATE DEFAULT CURRENT_DATE
);

Here, we define the employees table with columns for id, first_name, last_name, email, and hire_date. The hire_date column has a DEFAULT constraint to default to the current date.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email) VALUES ('John', 'Doe', 'john.doe@example.com');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Smith', 'jane.smith@example.com', '2023-02-01');
INSERT INTO employees (first_name, last_name, email) VALUES ('Alice', 'Johnson', 'alice.johnson@example.com');
INSERT INTO employees (first_name, last_name, email) VALUES ('Bob', 'Brown', 'bob.brown@example.com');

Here, we insert four rows of data into the employees table. The hire_date column for the first, third, and fourth rows will default to the current date because no date was provided.

Step 4: Adding a DEFAULT Constraint to an Existing Table

This step involves adding a DEFAULT constraint to an existing table named products to set the status column to 'available' by default.

ALTER TABLE products
ALTER COLUMN status SET DEFAULT 'available';

This command adds a DEFAULT constraint to the status column of the products table, ensuring that the status is set to 'available' if no value is provided.