MariaDB Alter Table Add Foreign Key

A table can be built in MariaDB using the foreign key constraint by running a MariaDB ALTER TABLE statement. This will also be covered in this MariaDB tutorial. To assist you in better understanding the topic, we’ll discuss and conclude several scenarios. Here is a list of every topic we’ll be covering.

  • How to add a foreign key to the table by using the MariaDB ALTER TABLE statement?
  • Using the MariaDB ALTER TABLE statement, describe how to add a foreign key to the table with an example.
  • How to add a foreign key with a default value on the column of the table?
  • How to check foreign key is not working by using the MariaDB ALTER TABLE statement?

MariaDB Alter Table Add Foreign Key

We will learn and understand how to add foreign keys to the table by using the MariaDB ALTER TABLE statement, which is explained with the help of syntax and an illustrated example.

In MariaDB, the ALTER TABLE statement is used to add, modify, drop/ delete columns from the table by the query. It is also used to rename columns of the table. Let’s see the syntax of the MariaDB ALTER TABLE statement by the following query:

SYNTAX:

ALTER TABLE YOUR_TABLENAME ADD CONSTRAINT FOREIGN KEY [ COLUMN_NAME] 
REFERENCES PARENT_TABLENAME [ PRIMARY_COLUMN_NAME]; 

In the syntax explanation:

  • First, we need to use add a foreign key constraint in the child table by using the ALTER TABLE statement.
  • Second, we need to use the REFERENCES keyword which will refer to the primary key column of the PARENT TABLE in the query.

EXAMPLE:

CREATE TABLE STATES_OF_CANADA(
STATE_ID INT AUTO_INCREMENT PRIMARY KEY, 
STATE_NAME VARCHAR(50) NOT NULL);

CREATE TABLE CANADASTATES_DETAIL(
STATE_ID INT AUTO_INCREMENT PRIMARY KEY,
ID INT ,
FULL_NAME VARCHAR(50),
ZIPCODE INT);

ALTER TABLE CANADASTATES_DETAIL
ADD CONSTRAINT FOREIGN KEY (ID) REFERENCES STATES_OF_CANADA (STATE_ID);

DESC CANADASTATES_DETAIL;

In the above query example explanation:

  • In the first query, we created a table called STATES_OF_CANADA table by using the INSERT INTO statement. It carries the STATE_ID and STATE_NAME columns with datatype as INT and VARCHAR(50). And it is the parent table.
  • In the second query, we have created another table called CANADASTATES_DETAIL by using the INSERT INTO statement. There is one column as ID which is the foreign key of the child table to the parent table.
  • Then we used the ALTER TABLE statement to add a foreign key constraint on the ID column of the CANADASTATES_DETAIL table. And then we used the REFERENCES keyword to refer to the PARENT TABLE as the STATES_OF_CANADA table.
  • To check the new modification and add a foreign key to the ID column, we used the DESC statement in the CANADASTATES_DETAIL table.
MariaDB alter table add foreign key
Example of MariaDB ALTER TABLE statement to add a foreign key constraint

We hope that you have understood the subtopic “MariaDB Alter Table Add Foreign Key” by using the MariaDB ALTER TABLE statement on the table by the query. For a better understanding, we have used a sample example and explained it in depth.

Read: MariaDB Primary Key With Examples

MariaDB Alter Table Add Foreign Key Example

Here, we will learn and understand how to add a foreign key to the column of the table by using the MariaDB ALTER TABLE statement which is explained by the following query:

EXAMPLE:

ALTER TABLE SKULLCANDY
ADD CONSTRAINT FOREIGN KEY (SKULLCANDY_ID) REFERENCES SKULLCANDY_USA (SKULLCANDY_ID);

DESC SKULLCANDY;

As we see in the above query, we have used the ALTER TABLE statement to add a foreign key constraint on the SKULLCANDY_ID column in the SKULLCANDY table. And to make it refer to the SKULLCANDY_ID column, we have used the REFERENCES keyword in the SKULLCANDY_USA table.

To check the new modification and description of the SKULLCANDY table, we have used the DESC statement.

MariaDB alter table add foreign key example
Example of MariaDB Alter Table Add Foreign Key

By using the MariaDB ALTER TABLE statement on the table returned by the query, we hope you have comprehended the subtopic “MariaDB Alter Table Add Foreign Key Example.” For a clearer explanation, we used a sample example and gave a detailed definition.

Read: MariaDB Vs SQLite

MariaDB Alter Table Add Foreign Key Default Value

We will learn and understand how to add a foreign key with the default value on the column of the table by the following query:

EXAMPLE:

ALTER TABLE SKULLCANDY
ADD CONSTRAINT FOREIGN KEY (SKULLCANDY_ID) REFERENCES SKULLCANDY_USA (SKULLCANDY_ID)
DEFAULT 0; 

DESC SKULLCANDY;
  • In the aforementioned query, the ALTER TABLE statement is used to add a foreign key constraint on the SKULLCANDY_ID column which works as the primary key in the SKULLCANDY table.
  • Then to refer to the parent table as the SKULLCANDY_USA table on the SKULLCANDY_ID column, we have used the REFERENCES keyword in the query.
  • In the end, we used the DEFAULT keyword with a value of 0. But if no value is put into the SKULLCANDY_ID column then it will automatically put the value as 0 in the SKULLCANDY table.
  • To check the new modifications and new descriptions of the SKULLCANDY table, we have used the DESC statement.
MariaDB alter table add foreign key default value example
Example of MariaDB Alter Table Add Foreign Key Default Value

By using the MariaDB ALTER TABLE statement on the table returned by the query, we hope you have grasped the subtopic “MariaDB Alter Table Add Foreign Key Default Value.” We used a detailed description and an illustrated example to provide a better explanation.

Read: MariaDB Add Column With Default Value

MariaDB Alter Table Add Foreign Key Not Working

For a better understanding of this subtopic, we have explained in detail how altering the table adds a foreign key not working on the table by the query. Kindly look into the subtopic “MariaDB Alter Table Add Foreign Key” and understand this concept.

Also, take a look at some more MariaDB tutorials.

After reading this lesson, we were able to use the MariaDB Alter Table Add Foreign Key as described in this tutorial. To better understand the concept, we have also explored a few examples. The subjects we have discussed are listed below.

  • How to add a foreign key to the table by using the MariaDB ALTER TABLE statement?
  • Using the MariaDB ALTER TABLE statement, describe how to add a foreign key to the table with an example.
  • How to add a foreign key with a default value on the column of the table?
  • How to check foreign key is not working by using the MariaDB ALTER TABLE statement?