MariaDB Alter Table Add Foreign Key

Adding a foreign key constraint to a table in MariaDB is a simple process that involves using the ALTER TABLE statement with the ADD FOREIGN KEY clause.

In this MariaDB tutorial, we will explore the various options that you can use when adding a foreign key constraint to a table in MariaDB, such as the ON DELETE, which determines what action should be taken if the referenced row is deleted or updated.

We will also discuss some common issues that you may encounter when working with foreign keys in MariaDB, such as the ALTER TABLE statement not working or the foreign key constraint is violated.

This MariaDB tutorial is intended for users who are familiar with the basic concepts of database management and who want to learn how to add foreign key constraints to tables in MariaDB.

By the end of the tutorial, you should have a good understanding of how to add foreign key constraints to tables in MariaDB and how to troubleshoot common issues that may arise.

• How to MariaDB Alter Table Add Foreign Key
• MariaDB Alter Table Add Foreign Key Example
• How to MariaDB Alter Table Add Foreign Key Default Value
• MariaDB Alter Table Add Foreign Key Not Working

MariaDB Alter Table Add Foreign Key

In MariaDB, you can use the ALTER TABLE statement to add a foreign key constraint to an existing table.

• A foreign key constraint is a rule that is used to prevent invalid data from being inserted into a table. It specifies that the values in a column (or set of columns) must be taken from the values in a column (or set of columns) in another table.
• To add a foreign key constraint to a table using the ALTER TABLE statement, you need to specify the name of the table that you want to modify, the name of the column (or set of columns) that you want to define as a foreign key and the name of the column (or set of columns) in the referenced table that the foreign key column (or set of columns) should match.

The syntax is given below.

ALTER TABLE table_name
ADD FOREIGN KEY (column_name)
REFERENCES referenced_table(referenced_column);

Where,

• table_name: The table_name parameter is the name of the table that you want to modify.
• column_name: The column_name parameter is the name of the column (or set of columns) that you want to define as a foreign key.
• referenced_table: The referenced_table parameter is the name of the table that the foreign key column (or set of columns) should reference.
• referenced_column: The referenced_column parameter is the name of the column (or set of columns) in the referenced table that the foreign key column (or set of columns) should match.

Let’s take an example to add the foreign key to the existing table.

First, check the table orders for the existing columns.

From the output, we can see that table orders contain information about order_date, amount, and country. In the table, most of the orders are from the USA and the United Kingdom.

Now add the new column to the table orders that are going to be the foreign key column using the below code.

ALTER TABLE orders
ADD COLUMN customer_id int;

Add the foreign key column using the below code.

ALTER TABLE orders
ADD FOREIGN KEY (customer_id)
REFERENCES customers(id);

This query will add a foreign key constraint to the orders table, specifying that the customer_id column in the orders table must match the id column in the customers table.

Read: MariaDB Primary Key With Examples

MariaDB Alter Table Add Foreign Key Example

To add a foreign key constraint to a table in MariaDB, you can use the ALTER TABLE statement with the ADD FOREIGN KEY clause.

Let’s take the example of how to use the ALTER TABLE statement to add a foreign key constraint to a table in MariaDB.

ALTER TABLE order_items
ADD FOREIGN KEY fk_order_id (order_id)
REFERENCES orders(id)
ON DELETE SET NULL;

In the above query ALTER TABLE statement will add a foreign key constraint to the order_items table, specifying that the order_id column in the order_items table must match the id column in the orders table.

The ON DELETE SET NULL option specifies that if a row in the orders table is deleted, the corresponding values in the order_id column of the order_items table will be set to NULL.

Take one more example which is shown below.

ALTER TABLE products
ADD FOREIGN KEY fk_category_id (category_id)
REFERENCES categories(id)
ON DELETE NO ACTION;

In the above query ALTER TABLE statement will add a foreign key constraint to the products table, specifying that the category_id column in the products table must match the id column in the categories table.

The ON DELETE NO ACTION option specifies that if a row in the categories table is deleted, no action will be taken on the corresponding rows in the products table.

Read: MariaDB Vs SQLite

MariaDB Alter Table Add Foreign Key Default Value

In MariaDB, you can use the ALTER TABLE statement to add a default value to a foreign key column in an existing table. A default value is a value that will be used for the column if no value is specified when inserting a new row into the table.

• To add a default value to a foreign key column using the ALTER TABLE statement, you need to specify the name of the table that you want to modify, the name of the column that you want to add the default value to, and the default value that you want to use.

The syntax is given below.

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;

Where,

• table_name: The table_name parameter is the name of the table that you want to modify.
• column_name: The column_name parameter is the name of the column to that you want to add the default value.
• SET DEFAULT: The command to set the default value for that column.
• default_value: The default_value parameter is the default value that you want to use.

Let’s take an example of how to use the ALTER TABLE statement to add a default value to a foreign key column in a table in MariaDB.

ALTER TABLE orders
ALTER COLUMN customer_id SET DEFAULT 1;

The above query ALTER TABLE statement will add a default value of 1 to the customer_id column in the orders table. If no value is specified for the customer_id column when inserting a new row into the orders table, the default value of 1 will be used instead.

It is important to note that you can only add a default value to a foreign key column if the referenced column in the referenced table has a default value. Otherwise, the default value for the foreign key column will not be set.

Read: MariaDB Add Column With Default Value

MariaDB Alter Table Add Foreign Key Not Working

There are several reasons why the ALTER TABLE statement might not work when you are trying to add a foreign key constraint to a table in MariaDB. Here are a few common issues that can cause this problem, along with some tips for troubleshooting and fixing them.

• The referenced table does not exist: If you are trying to add a foreign key constraint that references a table that does not exist, the ALTER TABLE statement will fail. Make sure that the table that you are referencing in the REFERENCES clause of the ALTER TABLE statement actually exists in your database.
• The referenced column does not exist: If you are trying to add a foreign key constraint that references a column that does not exist in the referenced table, the ALTER TABLE statement will fail. Make sure that the column that you are referencing in the REFERENCES clause of the ALTER TABLE statement actually exists in the referenced table.
• The data types of the foreign key and referenced columns do not match: If the data types of the foreign key and referenced columns do not match, the ALTER TABLE statement will fail. Make sure that the data types of the foreign key and referenced columns are compatible.
• The foreign key column is not set to accept NULL values: If the foreign key column is not set to accept NULL values and there are NULL values in the column, the ALTER TABLE statement will fail. You can either update the NULL values in the foreign key column to valid values, or you can set the foreign key column to accept NULL values by using the NULL keyword in the ADD FOREIGN KEY clause of the ALTER TABLE statement.
• There are already rows in the table with invalid foreign key values: If there are already rows in the table with foreign key values that do not match any of the values in the referenced column of the referenced table, the ALTER TABLE statement will fail. You will need to update the invalid foreign key values in the table to valid values before you can add the foreign key constraint.

If you are having trouble adding a foreign key constraint to a table in MariaDB and you can’t figure out why it can be helpful to check the MariaDB error log for more information. The error log may contain additional details about the problem that can help you troubleshoot the issue.

Also, take a look at some more MariaDB tutorials.

• MariaDB Check Empty String
• MariaDB Insert If Not Exists
• MariaDB Between + Examples
• MariaDB Not Between
• Create Views In MariaDB Group By

Conclusion

• In conclusion, MariaDB’s ALTER TABLE statement is a powerful tool that allows you to modify the structure of an existing table.
• By using the ADD FOREIGN KEY clause, you can add a foreign key constraint to a table, specifying that the values in a column (or set of columns) must be taken from the values in a column (or set of columns) in another table.

• This can be useful for enforcing data integrity and preventing invalid data from being inserted into your tables.
• There are several options that you can use when adding a foreign key constraint to a table in MariaDB, such as the ON DELETE and ON UPDATE options, which determine what action should be taken if the referenced row is deleted or updated.

You can also specify the name of the foreign key constraint and the action to be taken if the foreign key constraint is violated.

While adding foreign key constraints to tables in MariaDB is generally straightforward, you may encounter some common issues, such as the ALTER TABLE statement not working or the foreign key constraint is violated.

If you do encounter any issues, you can check the MariaDB error log for more information or try troubleshooting the problem using some of the tips provided in this MariaDB tutorial.

• How to MariaDB Alter Table Add Foreign Key
• MariaDB Alter Table Add Foreign Key Example
• How to MariaDB Alter Table Add Foreign Key Default Value
• MariaDB Alter Table Add Foreign Key Not Working