SQL Default Value: How To with Examples

The SQL DEFAULT constraint is a constraint that can be added to tables to specify a default value for a column. The default value is used for the column’s value when one is not specified (for example, when you insert a row into the table without specifying a value for the column).

What is the DEFAULT Database Keyword?

It’s a keyword that specifies the default value for a column. The default value is the value used when no value is provided for an Insert or an Update statement.

Using the DEFAULT keyword will create a “default constraint” on the table. You can add a default constraint either when creating a table or after the table already exists.

Default values can be NULL, or they can be a value that matches the data type of the column (number, text, date, for example).

Let’s see how to implement this in different databases and see some examples.

 

Oracle

Create Table

To add a DEFAULT constraint to a column in a table when you create it in Oracle, add the default database keyword to the Create Table statement:

CREATE TABLE tablename (
  columnname datatype DEFAULT defaultvalue
);

For example, to set a person’s employment status to a default of “Hired”:

CREATE TABLE employee (
  id NUMBER,
  first_name VARCHAR2(200),
  last_name VARCHAR2(200),
  employment_status VARCHAR2(20) DEFAULT 'Hired'
);

We can see on the last line, where employment_status is defined, there is a DEFAULT keyword after the data type. We also specify the default value of ‘Hired’ in single quotes.

 

Alter Table

You can also add a default constraint to a table when it already exists. You can do this using the ALTER TABLE statement.

ALTER TABLE employee
MODIFY employment_status DEFAULT 'Hired';

This command adds a default value of Hired to the column employment_status.

 

Remove the Default

To remove the default constraint from the column, run an Alter Table statement.

ALTER TABLE employee
ALTER COLUMN employment_status DROP DEFAULT;

This will remove the default constraint from the employment_status column.

 

SQL Server

Create Table

To add a DEFAULT constraint to a column in a table when you create it in SQL Server, add it to the Create Table statement:

CREATE TABLE tablename (
  columnname datatype DEFAULT defaultvalue
);

You can also use a named constraint, which means it is easier for you to find and modify it later. Without specifying the name, SQL Server will provide a random name.

To create a default constraint with a name:

CREATE TABLE tablename (
  columnname datatype CONSTRAINT constraintname DEFAULT defaultvalue
);

For example, to set a person’s employment status to a default of “Hired”:

CREATE TABLE employee (
  id INTEGER,
  first_name VARCHAR(200),
  last_name VARCHAR(200),
  employment_status VARCHAR(20) CONSTRAINT df_emp_hired DEFAULT 'Hired'
);

We can see on the last line, where employment_status is defined, there is a DEFAULT database keyword after the data type. We also specify the default value of ‘Hired’ in single quotes.

The constraint name is df_emp_hired. Whenever we look at the data dictionary tables or output from scripts and see this name, we know what it refers to.

 

Alter Table

You can make changes to an existing table to add a default value in SQL Server by using the ALTER TABLE statement.

For example:

ALTER TABLE employee
ADD CONSTRAINT df_emp_hired
DEFAULT 'Hired' FOR employment_status;

This command adds a new constraint called df_emp_hired which ensures the default value of the employment_status column is ‘Hired’.

 

Remove the Default

To remove the default from a column in SQL Server:

ALTER TABLE employee
ALTER COLUMN employment_status DROP DEFAULT;

This will remove the default constraint from the employment_status column.

 

MySQL

Create Table

To add a DEFAULT constraint to a column in a table when you create it in MySQL, add it to the Create Table statement:

CREATE TABLE tablename (
  columnname datatype DEFAULT defaultvalue
);

Using the same example as above, you can set a person’s employment status to a default of “Hired”:

CREATE TABLE employee (
  id INT,
  first_name VARCHAR(200),
  last_name VARCHAR(200),
  employment_status VARCHAR(20) DEFAULT 'Hired'
);

This means that any time you insert a new record and don’t specify the employment_status, then the value is set to “Hired”.

 

Alter Table

You can make changes to an existing table to add a default value in MySQL by using the ALTER TABLE statement. It looks a little different to other databases.

For example:

ALTER TABLE employee
ALTER employment_status SET DEFAULT 'Hired';

This command sets the default value of the employment_status column to “Hired”.

 

Remove the Default

To remove the default from a column in MySQL:

ALTER TABLE employee
ALTER employment_status DROP DEFAULT;

This will remove the default constraint from the employment_status column.

 

PostgreSQL

Create Table

You can add a DEFAULT constraint to a column in a table when you create it in PostgreSQL. To do this, add it to the Create Table statement:

CREATE TABLE tablename (
  columnname datatype DEFAULT defaultvalue
);

Using the same example as above, you can set a person’s employment status to a default of “Hired”:

CREATE TABLE employee (
  id INTEGER,
  first_name TEXT,
  last_name TEXT,
  employment_status TEXT DEFAULT 'Hired'
);

 

Alter Table

You can add a default value to a column in a table by using the ALTER TABLE statement.

For example:

ALTER TABLE employee
ALTER COLUMN employment_status SET DEFAULT 'Hired';

This command sets the default value of the employment_status column to “Hired”.

 

Remove the Default

To remove the default from a column in MySQL:

ALTER TABLE employee
ALTER COLUMN employment_status DROP DEFAULT;

This will remove the default constraint from the employment_status column.

 

Conclusion

Setting a default value in SQL can be done when you create the table or on an existing table’s column. The default value setting can also be removed from a table. This works mostly the same in each database.

If you have any questions, feel free to use the comments section below.