Set Default Values for Columns in SQL

1. Overview

Default values for SQL tables are useful for managing our data. Each database type uses a unique style for altering the default value, so there is no standard SQL syntax for changing a column’s default values. Consequently, we’ll explore setting a default value when creating a table and then cover how to alter it using commands specific to SQL Server, PostgreSQL, and MySQL.

In this tutorial, we’ll learn how to add default values to a new or existing column in a relational database.

2. Setting Default on Table Creation

Setting a default value for a column can help maintain data integrity. Additionally, it can simplify the data insertion process by not requiring the same initial value for every new record.

The easiest way to set a default value that works for all the database management systems (DBMS) is when creating the table. For example, in our University schema, we can set “Yes” as the default value for the column is_active on the Course table:

CREATE TABLE Course
(
    id VARCHAR (10) PRIMARY KEY NOT Null,
    name VARCHAR(60),
    textbook VARCHAR(100),
    credits INT,
    is_active VARCHAR(10) DEFAULT 'Yes',
    department_id INT,
    CONSTRAINT course_department_id_fkey FOREIGN KEY(department_id) REFERENCES Department(id),
    UNIQUE (id)
);

So, whenever a new Course record is created, it’s active by default. Therefore, when inserting a new course we can omit the is_active column:

INSERT INTO Course (id, name, textbook, credits, department_id) 
VALUES  ('CS111-2', 'Introduction to Operating Systems II', 'OS by Tanenbaum', 7, '1');

Then, we’ll query our newly inserted record:

SELECT *
FROM Course
WHERE id='CS111-2';

We see that the column has the default value:

| id      | name                                 | textbook        | credits | is_active | department_id |
| ------- | ------------------------------------ | --------------- | ------- | --------- | ------------- |
| CS111-2 | Introduction to Operating Systems II | OS by Tanenbaum | 7       | Yes       | 1             |

3. Alter Default in SQL Server

When dealing with existing tables, we must sometimes alter them to add or change a default value. Unfortunately, there isn’t a standard way to accomplish this, as each DBMS has a different syntax.

First, let’s see how to alter a table to add a default value in SQL Server. We can change the default value of the is_active column in the Course table by altering the table and adding a new constraint:

ALTER TABLE
    Course
ADD
    CONSTRAINT DF_Course_IsActive DEFAULT 'Yes' FOR is_active;

The constraint we created is named DF_Course_IsActive, and we can always refer to it by name. For example, if we need to remove the default value, we can drop the constraint by name:

ALTER TABLE
    Course DROP CONSTRAINT DF_Course_IsActive;

This will remove the default value we set previously.

3.1. Query Existing Constraints in SQL Server

While setting a new constraint seems easy, we must check for and remove any existing default constraints. Otherwise, we’ll encounter an error. In short, even if it looks like we’re altering the column is_active, constraints are not intrinsic properties of columns. So, we must find any previous default constraints:

SELECT
    name
FROM
    sys.default_constraints
WHERE
    parent_object_id = OBJECT_ID('Course')
    AND parent_column_id = COLUMNPROPERTY(OBJECT_ID('Course'), 'is_active', 'ColumnId');

This returns the names of all the constraints:

| name               |
| ------------------ |
| DF_Course_IsActive |

Once we have the name, we can drop the constraint the same way we did before.

4. Alter Default in PostgreSQL

Now, let’s move on to PostgreSQL

Similarly, in PostgreSQL, we have to use the ALTER TABLE statement to set a default value for the is_active column in the Course table:

ALTER TABLE
    Course
ALTER COLUMN
    is_active
SET
    DEFAULT 'Yes';

However, the syntax for changing a default value in PostgreSQL is different from SQL Server. Moreover, in PostgreSQL, we directly set the default value for the column without the need to check for an existing default constraint. We also might want to remove an existing default:

ALTER TABLE
    Course
ALTER COLUMN
    is_active DROP DEFAULT;

Again, we directly modify the default on the column and drop the default from is_active.

5. Alter Default in MySQL

Finally, let’s explore MySQL.

The ALTER TABLE in MySQL is identical to PostgreSQL:

ALTER TABLE
    Course
ALTER COLUMN
    is_active
SET
    DEFAULT 'Yes';

MySQL also doesn’t require us to check or remove an existing default constraint before changing it. We can also remove an existing default value by dropping it from the column:

ALTER TABLE
    Course
ALTER COLUMN
    is_active DROP DEFAULT;

6. Conclusion

In this article, we’ve learned how to set and remove default values for an existing column in SQL Server, PostgreSQL, and MySQL. Additionally, we saw that the syntax for updating a default value can vary based on the DBMS.