SQL DEFAULT Constraint – Tutorial with Examples

A SQL DEFAULT constraint is used to provide a default value for a column in a table. This value is inserted into the column if no value is specified when a new row is added to the table. The DEFAULT constraint is useful for providing a default value for columns that do not require a specific value to be entered. The SQL DEFAULT constraint can be added to a table during table creation or after the table has been created. In this article, we will explore the SQL DEFAULT constraint in detail and discuss its use in different relational database management systems such as MySQL, SQL Server, Oracle, and MS Access.

SQL DEFAULT Constraint Syntax

The syntax for adding a DEFAULT constraint to a table during table creation is as follows:

CREATE TABLE table_name (
  column_name data_type DEFAULT default_value,
  ...
);

Where:

• table_name: The name of the table to which the DEFAULT constraint will be added.
• column_name: The name of the column to which the DEFAULT constraint will be added.
• data_type: The data type of the column.
• default_value: The default value that will be inserted into the column if no value is specified.

For example, if you have a table named employee and you want to ensure that the status column has a default value of “Active” if no value is specified, you would add the following DEFAULT constraint:

CREATE TABLE employee (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  status VARCHAR(50) DEFAULT 'Active'
);

To add a DEFAULT constraint to an existing table, use the following SQL statement:

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;

SQL DEFAULT Constraint in MySQL

In MySQL, the DEFAULT constraint can be added to a table during table creation or after the table has been created. The following example demonstrates how to add a DEFAULT constraint to a table during table creation:

CREATE TABLE employee (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  status VARCHAR(50) DEFAULT 'Active'
);

To add a DEFAULT constraint to an existing table, use the following SQL statement:

ALTER TABLE employee
ALTER COLUMN status SET DEFAULT 'Active';

SQL DEFAULT Constraint in SQL Server

In SQL Server, the DEFAULT constraint can be added to a table during table creation or after the table has been created. The following example demonstrates how to add a DEFAULT constraint to a table during table creation:

CREATE TABLE employee (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  status VARCHAR(50) DEFAULT 'Active'
);

To add a DEFAULT constraint to an existing table, use the following SQL statement:

ALTER TABLE employee
ADD CONSTRAINT default_status
DEFAULT 'Active' FOR status;

Note that in SQL Server, the ALTER COLUMN statement cannot be used to add a DEFAULT constraint to an existing column. Instead, you must use the ADD CONSTRAINT statement.

SQL DEFAULT Constraint in Oracle

In Oracle, the DEFAULT constraint can be added to a table during table creation or after the table has been created. The following example demonstrates how to add a DEFAULT constraint to a table during table creation:

CREATE TABLE employee (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(50),
  age NUMBER,
  status VARCHAR2(50) DEFAULT 'Active'
);

To add a DEFAULT constraint to an existing table, use the following SQL statement:

ALTER TABLE employee
MODIFY status DEFAULT 'Active';

SQL DEFAULT Constraint in MS Access

In MS Access, the DEFAULT constraint can be added to a table during table creation or after the table has been created. The following example demonstrates how to add a DEFAULT constraint to a table during table creation:

CREATE TABLE employee (
  id AUTOINCREMENT PRIMARY KEY,
  name TEXT,
  age INTEGER,
  status TEXT DEFAULT 'Active'
);

To add a DEFAULT constraint to an existing table, use the following steps:

1. Open the table in Design View.
2. Select the column to which you want to add the DEFAULT constraint.
3. In the Field Properties section, enter the default value in the Default Value property.
4. Save the changes to the table.

Conclusion

In this article, we have discussed the SQL DEFAULT constraint and how to use it in different relational database management systems such as MySQL, SQL Server, Oracle, and MS Access. The DEFAULT constraint is a useful tool for providing a default value for columns in a table, which can help ensure that data is entered consistently and correctly. Whether you are creating a new table or modifying an existing one, the DEFAULT constraint can be a valuable addition to your database design.