Search code snippets, questions, articles...

Add a Boolean column in MySQL table with default value

Adding a Boolean column in MySQL with a default value is a simple process that can be completed in a few steps. You can add the column to the existing table or while creating a new table. We will explain all the processes that can be used to add the boolean column in the MYSQL table with a default value - true or false.
Best JSON Validator, JSON Tree Viewer, JSON Beautifier at same place. Check how cool is the tool

Add a Boolean column with a default value in an existing MySQL table

If your table already exists in the database and you want to add a boolean column with a default value (true or false), then you can use the MySQL queries explained below. 

For example - If we have a table named user and we want to add a new column is_active that has a data type Boolean and we want to assign a default value to it then we can use the below MySQL query.

Add with default value false(0)

ALTER TABLE user
ADD COLUMN is_active BOOLEAN DEFAULT 0

Add with default value true(1)

ALTER TABLE user
ADD COLUMN is_active BOOLEAN DEFAULT 1

Adding a Boolean column with a default value to an existing MySQL table can be done in a few simple steps.

First, use the ALTER TABLE command to add the new column.

Next, assign a default value 0(false) or 1(true) to the column and run the MySQL query.

Finally, use the SELECT command to verify that the new column has been added and that the default value is correct.

If you want to read more about ALTER TABLE query click here.

Add boolean type column with default value while creating the table

To add a boolean type column with a default value while creating a table in MySQL, the following SQL syntax can be used:

Syntax

CREATE TABLE table_name (
    column_name BOOLEAN NOT NULL default 0
);

For example, if we want to create a table named student in our MySQL database with a column named is_active then we can use the below MySQL query.

CREATE TABLE student (
    is_active BOOLEAN NOT NULL default 0
);

In the above query, we have assigned a false value to the column is_active in the create table SQL query.

If you want to read more about creating tables in MySQL, click the below link.

Create table with constraints MySQL query

What is the meaning of adding default value to a column in MySQL? 

Adding a default value to a column in MySQL means that if no value is specified for that column when a new record is inserted, the default value will be used.

For example, if you have a table named employee that has multiple columns in it. If there is a column in it that contains a default value then whenever a new row will be inserted to this table and you have not assigned any value to the default value column then the default value will be automatically inserted for that row.

-- Add in existing table
ALTER TABLE table_name
ADD COLUMN is_active BOOLEAN DEFAULT 1

-- Add while creating the table
CREATE TABLE table_name (
    is_active BOOLEAN NOT NULL default 1
);
Was this helpful?
0 Comments