MariaDB Add Column With Default Value

In this article, we’ll look at MariaDB’s ALTER TABLE ADD Column statements, which let you add columns with default values to an already-existing table.

On larger tables, we will also comprehend the effects of creating a column with a default value and later adding and modifying the column with a value.

You have to add columns with a default value to the already-existing tables far too frequently as a database developer. It seems like it shouldn’t be a big deal to add a column to the MariaDB Server database table.

You might occasionally be adding the column directly from the MariaDB dbForge Studio. That might be acceptable for a tiny table or a table with few transactions, I suppose.

We are going to cover the following topics:

• MariaDB Add Column With Default Value
• How to add Default Value To Column in MariaDB
• How to Add Column in MariaDB Table
• MariaDB Add Column With Default Value Foreign Key

MariaDB Add Column With Default Value

You can add, remove, or modify the columns in an existing table using the ALTER TABLE statement. In this section, we add a new column with a default value.

The syntax is given below.

ALTER TABLE table_name
ADD column_name datatype DEFAULT default_value;

Where,

• ALTER TABLE: It is the keyword for adding, removing, or modifying the existing table for the column.
• table_name: It is the name of the table where the column will be added, removed, or modified.
• ADD column_name datatype: It is used to specify the column name with the data type of that column using the ADD keyword.
• DEFAULT default_value: It is used to provide the default value for the column.

Let’s take an example by following the below steps:

Below shown the description of the customer’s table of the database classiccars

Use the below query to add the new column name rating to the table customers.

ALTER TABLE customers
ADD rating INT DEFAULT 0;

Check the table description using the below query.

DESCRIBE customers;

From the above output, we can see the column rating contains a default value of 0 in a column Default. This is how to add a column with a default value.

Read: MariaDB Vs SQL Server – Detailed Comparison

Add Column in MariaDB Table

We have already learned how to add the column with the default value in the above subsection. In this section, we will add only the new column without any default value to the existing table.

The syntax is given below.

ALTER TABLE table_name
ADD column_name datatype;

Where,

• ALTER TABLE: It is the keyword for adding, removing, or modifying the existing table for the column.
• table_name: It is the name of the table where the column will be added, removed, or modified.
• ADD column_name datatype: It is used to specify the column name with the data type of that column using the ADD keyword.

Let’s take an example and add the column to the table by following the below steps:

Use the below query to add the new column name rating to the table customers.

ALTER TABLE customers
ADD rating ;

Check the table description using the below query.

DESCRIBE customers;

From the above output, we can see the column rating is added using the ALTER TABLE. This is how to add a column to an existing table.

Read: MariaDB Date Greater Than

How to add Default Value To Column in MariaDB

Until now we have learned how to add columns with or without the default value. Now in this section, we will learn how to set the default for the existing column.

The syntax is given below.

ALTER TABLE table_name
ALTER column_name SET DEFAULT value;

Where,

• ALTER TABLE: It is the keyword for adding, removing, or modifying the existing table for the column.
• table_name: It is the name of the table where the column will be added, removed, or modified.
• ALTER column_name: It is used to specify the column name for which the default value will be assigned.
• SET DEFAULT value: It is used to set a default value for the column.

Let’s take an example by following the below steps:

Use the below query to set the default value of 1 to the column rating of table customers that we have created in the above subsections.

ALTER TABLE customers
ALTER rating SET DEFAULT 1;

Check the assigned value to the column rating using the below query.

DESCRIBE customers;

From the above output, we can see the column rating contains a default value of 1 in a column Default. This is how to set the default value of the already existing column.

Read: MariaDB Check Empty String

MariaDB Add Column With Default Value Foreign Key

In this section. we are going to add the column with the default value foreign key. we already discussed in the above subsection how to add a column with a default value.

First, we will create a two-table productReview and ProductRating, then set the default value of the column that is going to be a foreign key. Let’s follow the below steps:

Use the below query to create a table productReview and productRating.

CREATE TABLE productReview (productName VARCHAR(80), productComment VARCHAR(200),productCode INT PRIMARY KEY);
CREATE TABLE productRating (customerName VARCHAR(100), country VARCHAR(150), rating INT);

In the above query, create table proudctReview that contains columns productName, productComment, and productCode which is the primary key of the table. Then creating another table productRating with columns customerName, country, and rating.

Set the default value of the column rating of table productRating that we have created in the above query.

ALTER TABLE productRating
ALTER rating SET DEFAULT 0;

Make the column rating a foreign key using the below query.

ALTER TABLE productRating 
ADD FOREIGN KEY (rating) REFERENCES productReview(productCode);

Now check the column rating for the foreign key using the below query.

DESCRIBE productRating;

The above output shows the column rating as a foreign key, this is how to add a column with a default value and foreign key.

Also, take a look at some more MariaDB tutorials.

• MariaDB Check If Rows Exists
• MariaDB Insert Into Select
• How to Add Column in Maria
• MariaDB Order By Decreasing
• How to Delete a Database in MariaDB
• How to Create Trigger in MariaDB

We have learned how to add the column with or without default values, also learned how to make a column a foreign key, and set the default value using the ALTER TABLE statement.

• MariaDB Add Column With Default Value
• How to add Default Value To Column in MariaDB
• How to Add Column in MariaDB Table
• MariaDB Add Column With Default Value Foreign Key