MariaDB Foreign Key + Examples

In this MariaDB tutorial, we will study the use of the MariaDB Foreign Key and we will also cover some examples. There are lists of the topic that comes under discussion:

• MariaDB foreign key
• MariaDB foreign key example
• MariaDB foreign key constraint is incorrectly formed
• MariaDB foreign key create table
• MariaDB foreign key null
• MariaDB foreign key alter
• MariaDB foreign key multiple columns
• MariaDB foreign key no action
• MariaDB a foreign key constraint fails
• MariaDB alter foreign key on delete cascade
• MariaDB foreign key checks off
• MariaDB foreign key delete
• MariaDB foreign key set default
• MariaDB drop foreign key
• MariaDB drop foreign key if exists
• MariaDB add foreign key if not exists
• MariaDB find foreign key
• MariaDB foreign key on delete set null
• MariaDB foreign key on update cascade
• MariaDB rename foreign key
• MariaDB partition table foreign key
• MariaDB drop foreign key
• MariaDB add foreign key if not exists
• MariaDB drop foreign key if exists
• MariaDB foreign key no action
• MariaDB rename foreign key
• MariaDB find foreign key

MariaDB Foreign Key

In this section, we will learn how to use the MariaDB Foreign Key with the help of syntax and examples.

In MariaDB, the Foreign key is a column or set of columns in the table that refers to another table column or set of columns that enforces the referential integrity between two tables. The table which has a foreign key is called the child table and the table to which the foreign key is referred is called the parent table.

The foreign key in the child table is a reference to the primary key in the parent table in a typical language.

The syntax of the FOREIGN KEY constraint is given below:

SYNTAX:

CREATE TABLE TABLE_NAME(
COLUMN_NAME CONSTRAINT,
[constraint constraint_name]
foreign key [fk_name](column_list) references parent_table(column_list)
[on delete ref_option]
[on update ref_option]
....,
);

The syntax explanation:

• First, use the constraint keyword to provide the name of the foreign key constraint. If we omit the constraint clause, MariaDB will assign a created name.
• Second, use paraenthsis to place a list of comma separated column names after the foreign key name. It is not required to use the Foreign key.
• Third, after using the reference keyword as ref, define the parent table with the mentioned columns to which the foreign key refers.
• Finally, using the DELETE and UPDATE statements, establish how the foreign key constraint maintains referential integrity between two tables.

The sample example of the FOREIGN KEY constraint is given below:

EXAMPLE:

First, let’s create a new table SKULLCANDY_USA by using the following query:

CREATE TABLE SKULLCANDY_USA(
SKULLCANDY_ID INT AUTO_INCREMENT PRIMARY KEY,
SKULLCANDY_NAME varchar(100) NOT NULL,
SKULLCANDY_USPRICE FLOAT);

EXPLANATION:

In the above statement, we have used the CREATE TABLE statement. Here we have created table_name as SKULLCANDY_USA with different attributes such as skullcandy_id, skullcandy_name, and skullcandy_usprice with different data types. Also, we have assigned skullcandy_id as a primary key. The output message is given below:

/* Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 1 query: 4.532 sec. */

Let’s create another table to implement foreign key constraints as shown below:

CREATE TABLE JBL_USA(
JBL_ID INT AUTO_INCREMENT PRIMARY KEY,
JBL_NAME VARCHAR(100),
JBL_PRICE FLOAT,
SKULLCANDY_ID INT,
CONSTRAINT SK_CANDY_TYPE
FOREIGN KEY (SKULLCANDY_ID)
REFERENCES SKULLCANDY_USA (SKULLCANDY_ID));

In this query, we have used the CREATE TABLE statement. Here, we have created a table with multiple columns

Also, check: MariaDB Primary Key

MariaDB Foreign Key Example

In this section, we will learn how to use the FOREIGN KEY constraint with the help of examples.

Let’s first create a SKULLCANDY_USA with the help of the following query:

TABLE_1 : SKULLCANDY_USA

CREATE TABLE SKULLCANDY_USA(
SKULLCANDY_ID INT AUTO_INCREMENT PRIMARY KEY,
SKULLCANDY_NAME VARCHAR(100) NOT NULL,
SKULLCANDY_USPRICE FLOAT);

INSERT INTO SKULLCANDY_USA(SKULLCANDY_NAME,SKULLCANDY_USPRICE)
VALUES('Crusher® Evo Sensory Bass Headphones
with Personal Sound',199.99),
('Push AM Wireless Buds',79.99),
('NCT ANC Noise Cancellation Wireless Buds',99.99),
('Budweiser DIME True Wirless Buds',34.99),
('DIME 2 True Wireless EarBuds',29.99),
('TRUE Wireless Earbuds',99.99);

SELECT * FROM SKULLCANDY_USA;

In this query, we have created a new table as SKULLCANDY_USA by using the CREATE TABLE and INSERT INTO statements. We retrieved all records from the SKULLCANDY_USA table by using the SELECT statement.

TABLE_2: FOREIGN KEY CONSTRAINT for SKULLCANDY:

CREATE TABLE SKULLCANDY(
ID INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(100),
PRICE FLOAT,
SKULLCANDY_ID INT,
CONSTRAINT SK_CANDY_TYPE
FOREIGN KEY (SKULLCANDY_ID)
REFERENCES SKULLCANDY_USA (SKULLCANDY_ID));

INSERT INTO SKULLCANDY(NAME,PRICE,SKULLCANDY_ID)
VALUES('Crusher® Evo Sensory Bass Headphones',69.99,1),
('TRUE Wireless Earbuds',49.95,6),
('DIME 2 True Wireless EarBuds',199.95,5),
('NCT ANC Noise Cancellation Wireless Buds',79.95,3);

SELECT * FROM SKULLCANDY;

In this query, we have created a table as SKULLCANDY by using the CREATE TABLE statement. We have inserted some records in the SKULLCANDY table by using the INSERT INTO statement. And to retrieve all records from the SKULLCANDY table, we have used the SELECT statement.

Read: MariaDB on Duplicate Key Update

MariaDB Foreign Key Constraint is Incorrectly Informed

In this section, we will learn if the FOREIGN KEY constraint is incorrectly informed in the MariaDB database.

First, let have a look at the USA_LAPTOPCOMPANY table by using the following query:

SELECT * FROM USA_LAPTOPCOMPANY;

As a result, the FOREIGN KEY constraint is frequently wrongly informed as a result of a syntax or logical error. Typically, the user provides the wrong parameter information in the query, resulting in an error.

CREATE TABLE USA_LAPTOPPRODUCT(
PRODUCT_ID INT AUTO_INCREMENT PRIMARY KEY,
PRODUCT_NAME VARCHAR(60),
LAPTOP_NAME VARCHAR(60),
CONSTRAINT SK_LAPTOP_TYPE
FOREIGN KEY (LAPTOP_NAME)
REFERENCES USA_LAPTOPCOMPANY (LAPTOP_NAME));

Read: MariaDB Temporary Table + Examples

MariaDB Foreign Key Create Table

In this section, we will learn how to create a table with foreign key constraints with the help of syntax and illustrated examples.

The MariaDB foreign key works as the primary key in the secondary table. It is normally used for connection between two tables. And the table has the foreign key linked is called the child table and the primary key of the first table is called the parent table.

Suppose, there are two tables as USA_LAPTOPCOMPANY and USA_LAPTOPPRODUCT table by using the following query:

CREATE TABLE USA_LAPTOPCOMPANY(
LAPTOP_ID INT AUTO_INCREMENT PRIMARY KEY,
LAPTOP_NAME VARCHAR(60));

CREATE TABLE USA_LAPTOPPRODUCT(
PRODUCT_ID INT AUTO_INCREMENT PRIMARY KEY,
PRODUCT_NAME VARCHAR(60),
LAPTOP_NAME VARCHAR(60)
CONSTRAINT SK_LAPTOP_TYPE
FOREIGN KEY (LAPTOP_NAME)
REFERENCES USA_LAPTOPCOMPANY (LAPTOP_NAME)
);

SELECT * FROM USA_LAPTOPPRODUCT;

As we see in the above query, we have created the laptop_name column as a foreign key constraint in the USA_LAPTOPPRODUCT table which means it brings laptop_name like HP, APPLE, VAIO company with its product like HP ELITE, APPLE MACBOOK AIR, etc.

The purpose of the foreign key is that it is easier to understand in terms of long data.

NOTE

The product_id column has been generated several times by using the EXECUTE SQL button in the USA_LAPTOPPRODUCT table, so don’t get confuse by generated query because of less query was inserted in the statement.

Read: MariaDB Left Join

MariaDB Foreign Key Null

In this section, we will learn how to use the Foreign Key column as Null with the help of examples.

First, let’s have a look at the JBL_USA and SKULLCANDY_USA table by the following query:

SELECT * FROM JBL_USA;

SELECT * FROM SKULLCANDY_USA;

In this query, we have retrieved all records from the JBL_USA and SKULLCANDY_USA table by using the SELECT statement.

There is a foreign key linked to the JBL_USA table because all records of the JBL_USA table are different from the SKULLCANDY_USA. Even if one row was connected to the primary key of the SKULLCANDY_USA table with the foreign key of the JBL_USA table. Then, the resultset might be different from this one.

Read: MariaDB Truncate Table

MariaDB Foreign Key Alter

In this section, we will learn how to use the ALTER statement to add the MariaDB foreign key constraint in the table with the help of syntax and examples.

The ALTER statement in MariaDB is used to add a foreign key to an existing table. It can also be used to RENAME, DELETE, and MODIFY columns in the table. The ALTER statement for the foreign key constraint has the following syntax:

SYNTAX:

ALTER TABLE TABLE_NAME 
[constraint fk_constraint_name]
foreign key [fk_name](column_list) references parent_table(column_list)
[on delete ref_option]
[on update ref_option]

The sample example of the ALTER statement for the foreign key constraint is given below:

EXAMPLE:

ALTER TABLE USA_EARPHONEPRODUCT ADD PRODUCT_NAME VARCHAR(60) PRIMARY KEY;

ALTER table USA_EARPHONEPRODUCT 
ADD CONSTRAINT FK_EARNAME_TYPE
FOREIGN KEY (PRODUCT_NAME)
REFERENCES USA_EARPHONECOMPANY(PRODUCT_NAME)
ON DELETE CASCADE
ON UPDATE CASCADE;

SELECT * FROM USA_EARPHONEPRODUCT;

As the product_name column, we’ve included a foreign key with the varchar(60) datatype. To use it as a foreign key, we must make the product_name column in the USA_EARPHONEPRODUCT table primary key.

Remember that the FK keyword stands for foreign key, thus it should be included when creating the FK_EARNAME_TYPE constraint. This will supply the constraint name. The following is the result of the query:

Read: MariaDB Rename Column

MariaDB Foreign Key Name

In this section, we will learn how to use the FOREIGN KEY constraint for the NAME column in the table with the help of examples.

First, let’s have a look at the USA_LAPTOPCOMPANY and USA_LAPTOPPRODUCT by the following query:

1. First Table ( USA_LAPTOPCOMPANY):

CREATE TABLE USA_LAPTOPCOMPANY(
LAPTOP_ID INT AUTO_INCREMENT PRIMARY KEY,
LAPTOP_NAME VARCHAR(60));

INSERT INTO USA_LAPTOPCOMPANY (LAPTOP_NAME)
VALUES('HP'),
('DELL'),
('APPLE'),
('VAIO');

SELECT * FROM USA_LAPTOPCOMPANY;

The CREATE TABLE statement was used to create a table for USA_LAPTOPCOMPANY, and then the INSERT statement was used to insert records into it. Also, the SELECT command is used to retrieve all records from the USA_LAPTOPCOMPANY table.

2. Second Table ( USA_LAPTOPPRODUCT):

CREATE TABLE USA_LAPTOPPRODUCT(
PRODUCT_ID INT AUTO_INCREMENT PRIMARY KEY,
PRODUCT_NAME VARCHAR(60),
LAPTOP_NAME VARCHAR(60)
CONSTRAINT SK_LAPTOP_TYPE
FOREIGN KEY (LAPTOP_NAME)
REFERENCES USA_LAPTOPCOMPANY (LAPTOP_NAME)
);

INSERT INTO USA_LAPTOPPRODUCT(PRODUCT_NAME,LAPTOP_NAME)
VALUES('HP ELITEBOOK','HP'),
('HP ENVY','HP'),
('APPLE MACBOOK AIR',APPLE');

SELECT * FROM USA_LAPTOPPRODUCT;

We have a foreign key of the name column from the USA_LAPTOPCOMPANY table in this part, which acts as a foreign key in the USA_LAPTOPPRODUCT table to bring the details of the laptop product name into the table.

Read: MariaDB Order By Clause

MariaDB foreign Key Multiple Columns

In this section, we will learn how to use the FOREIGN KEY constraint for the multiple columns in MariaDB with the syntax and examples.

The Primary key column of the first table which is listed to the foreign key of the second table is called the child table and the primary key of the first table is called the parent table. The syntax of the foreign key is given below:

SYNTAX:

[CONSTRAINT const_name]  
    FOREIGN KEY [foreign_key_name] (col_name_1...)  
    REFERENCES parent_tbl_name (col_name_1,...)  
    ON DELETE ref_Option  
    ON UPDATE ref_Option  

First, run the following query to create the USA_EARPHONECOMPANY and USA_SENNISHER tables:

1. FIRST TABLE (USA_EARPHONECOMPANY):

CREATE TABLE USA_EARPHONECOMPANY(
PRODUCT_ID INT AUTO_INCREMENT PRIMARY KEY,
PRODUCT_NAME VARCHAR(100),
PRODUCT_MODEL VARCHAR(100));

INSERT INTO USA_EARPHONECOMPANY (PRODUCT_NAME,PRODUCT_MODEL)
VALUES('ABYSS','ABHI-1266 Phi TC'),
('ACOUSTISM','1060-HS'),
('ACTIVE HEADSETS','DRE-1001'),
('AFTERSHOKZ','TITANIUM'),
('AIRCOM','A4X'),
('JBL','JBL 500T');

SELECT * FROM USA_EARPHONECOMPANY; 

In this query, we used the CREATE TABLE statement to create a new table named USA_EARPHONECOMPANY and the INSERT INTO statement to add some records to the table. The SELECT statement was then used to retrieve all records from the USA_EARPHONECOMPANY table.

2. SECOND TABLE: (USA_SPEAKERCOMPANY)

CREATE TABLE USA_EARPHONEPRODUCT(
EARPHONEPRODUCT_ID INT AUTO_INCREMENT PRIMARY KEY,
EARPONEPRODUCT_NAME VARCHAR(100),
EARPHONEPRODUCT_PRICE FLOAT,
PRODUCT_ID INT,
CONSTRAINT SK_EARPHONEPRO_TYPE
FOREIGN KEY (PRODUCT_ID)
REFERENCES USA_EARPHONECOMPANY(PRODUCT_ID)
);

INSERT INTO USA_EARPHONEPRODUCT(PRODUCT_ID,EARPONEPRODUCT_NAME,EARPHONEPRODUCT_PRICE)
VALUES(6,'JBL 500T',89.95),
(6,'JBL Tune 500',49.95),
(6,'JBL Charger 4', 199.95);

We got rows of foreign keys from the USA_EARPHONECOMPANY table in the USA_EARPHONEPRODUCT table in this part.

Read: MariaDB Select Statement

MariaDB Foreign Key on Update Cascade

In this section, we will learn how to use MariaDB foreign key with the reference option as CASCADE on the ON UPDATE keyword and it is explained by the syntax and examples.

The CASCADE is used to change and propagate on the child table. In simple terms, if the parent table some rows are deleted then child table rows will be also deleted. But if a parent row’s ID is changed then the child table ID is also changed.

The sample example of the FOREIGN KEY ON UPDATE CASCADE is given below:

EXAMPLE:

CREATE TABLE book_author (
  ID INT AUTO_INCREMENT PRIMARY KEY,
  Author_Name VARCHAR(100) NOT NULL
);

CREATE TABLE BOOKS (
  ID INT AUTO_INCREMENT PRIMARY KEY,
  Book_NAME VARCHAR(200) NOT NULL,
  author_id INT,
  CONSTRAINT fk_books_author
    FOREIGN KEY (author_id) REFERENCES book_author (id)
    ON UPDATE CASCADE
);

In this above query, we have cascade keywords on both tables i.e; book_author and books tables. The book_author table is the parent table and the books table is the child table if any changes happened or are required to the parent table then the same things will happen on the child table also.

Read: MariaDB Select Into

MariaDB Foreign Key Checks Off

In this section, we will learn how to set a session variable of foreign_key_checks as off in MariaDB.

In MariaDB, if the checks are set to default (which is 1) then InnoDB are checked but if they are set to 0 then they are not checked. The main purpose of checks to 0 is that they are not recommended in normal use but we know that data is consistent. If we want to reload data in the different order that is specified in parent/child relationships.

When this variable is set to 1, it does not check for errors introduced while it was set to 0. The syntax of the foreign key checks off is given below:

SET session foreign_key_checks= OFF;

The syntax explanation:

• When it is set to OFF, the foreign key constraint is disabled.
• There is no use or recommended it for normal use.
• Effect in ALTER TABLE performance: In MariaDB Enterprise Server 10.3 or later, when it is set to OFF, it will be added to INNODB table which will be very efficient. So, no foreign key are checked which can be unsafe for the query.

Read: MariaDB Comment

MariaDB Foreign Key Set Default

In this section, we will know how to set default foreign key constraint in the MariaDB and it is explained with the help of syntax and examples.

The main use of reference_option as SET DEFAULT keyword is that it is also similar to SET NULL keyword but if the default values for foreign key columns were set. An error is generated if default settings do not exist.

The syntax of MariaDB foreign key set default is given below:

SYNTAX:

ALTER TABLE TABLE_NAME 
[CONSTRAINT [NAME]] FOREIGN KEY
    [index_name] (index_col_name_1, ...)
    REFERENCES tbl_name (index_col_name_1,...)
    [ON DELETE ref_option]
    [ON UPDATE ref_option]

The sample example of the MariaDB Foreign key set default is given below:

EXAMPLE:

ALTER table USA_EARPHONEPRODUCT 
ADD CONSTRAINT FK_EARNAME_TYPE
FOREIGN KEY (PRODUCT_NAME)
REFERENCES USA_EARPHONECOMPANY(PRODUCT_NAME)
ON DELETE SET DEFAULT
ON UPDATE SET DEFAULT;

In this query, we’ve changed the constraint name to the FK_EARNAME_TYPE keyword, which was derived from the old table USA_EARPHONECOMPANY. And we’ve set the ON DELETE and ON UPDATE keywords to SET DEFAULT, which means that if the result is null, the query will fail.

Read: MariaDB Row_Number

MariaDB Foreign Key on Delete Set Null

In this section, we will learn how to add reference_option as SET NULL on the FOREIGN KEY ON DELETE statement with the help of syntax and examples.

In MariaDB, a foreign key can be referenced column and can be persistent columns. So MariaDB takes action based on the ON DELETE clause of the foreign key when a row in the parent table is deleted and at least one child row exists.

The use of the reference option as SET NULL is that when the change is allowed, the child row’s foreign key columns are set to NULL.

The sample example of the MariaDB Foreign Key on Delete Set Null is given below:

EXAMPLE:

CREATE TABLE Computerbook_author (
  ID INT AUTO_INCREMENT PRIMARY KEY,
  Author_Name VARCHAR(100) NOT NULL
);

CREATE TABLE COMPUTER_BOOKS (
  ID INT AUTO_INCREMENT PRIMARY KEY,
  Book_NAME VARCHAR(200) NOT NULL,
  ID INT,
  CONSTRAINT fk_books_author
    FOREIGN KEY (author_id) REFERENCES author (id)
    ON DELETE SET NULL
);

As we see in the above query, we have created two tables i.e; COMPUTER_AUTHOR and COMPUTER_BOOKS. In the COMPUTER_BOOKS table, we have given the author_id column as a reference of ID column from the COMPUTER_AUTHOR table by using the reference option as ON DELETE set null.

Read: MariaDB Date Function

MariaDB Partition table Foreign Key

This section will let us know about the partition table with foreign keys In MariaDB.

In MariaDB, the partition table can be split up into small subsets. The Partitioning limitation on MariaDB Foreign Key is given below:

• A maximum of 8192 partitions can be stored in each table (from MariaDB 10.0.4). The limit was 1024 in MariaDB 5.5 and 10.0.3.
• Currently, even when numerous partitions are involved, queries are never executed in parallel.
• Foreign keys cannot exist in a partitioned table or be referenced by them.
• Every unique key that a partitioned table may have must include all columns utilised in the partitioning statement.

Read: MariaDB AUTO_INCREMENT

MariaDB Drop Foreign Key

In this sub-topic, we will learn how to drop foreign key columns from the table by using the ALTER TABLE statement in MariaDB.

Normally, the ALTER TABLE statement is used to upgrade or modify or delete data from the statement. It is also used for renaming columns in the table. The syntax to drop a foreign key by using the ALTER TABLE statement is given below:

SYNTAX:

ALTER TABLE TABLE_NAME 
DROP FOREIGN KEY [IF EXISTS] fk_name

The sample example of the drop foreign key column by using the ALTER TABLE statement is given below:

EXAMPLE:

ALTER TABLE USA_EARPHONEPRODUCT DROP FOREIGN KEY IF EXISTS FK_EARNAME_TYPE;

In this query, we will drop the fk_name as FK_EARNAME_TYPE from the USA_EARPHONEPRODUCT table by using the ALTER TABLE statement. And use it with the IF EXISTS clause in the query, it will produce a warning dialogue box but it will delete it from the USA_EARPHONEPRODUCT table.

Raed: MariaDB Delete Row

MariaDB Add Foreign Key If Not Exists

In this section, we will learn how to add a foreign key column in the table with the MariaDB IF EXISTS clause and it is explained by the syntax and examples.

The syntax to add the MariaDB Foreign key with the IF EXISTS clause by using the ALTER TABLE statement is given below:

SYNTAX:

ALTER TABLE 
ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [IF NOT EXISTS] [index_name] (index_col_name,...)
        reference_definition;

The sample example of the IF NOT EXISTS clause in the ALTER TABLE statement for the foreign key column is given below:

EXAMPLE:

ALTER table USA_EARPHONEPRODUCT 
ADD CONSTRAINT FK_EARNAME_TYPE
FOREIGN KEY IF NOT EXISTS (PRODUCT_NAME)
REFERENCES USA_EARPHONECOMPANY(PRODUCT_NAME)
ON DELETE CASCADE
ON UPDATE CASCADE;

We used the IF NOT EXISTS clause in the above query to try to add the foreign key constraint FK_EARPNAME_TYPE in the USA_EARPHONEPRODUCT table. It will generate an error if the PRODUCT_NAME column previously existed in the USA_EARPHONEPRODUCT table.

Read: MariaDB index with Examples

MariaDB Drop Foreign Key If Exists

In this section, we will learn how to drop a foreign key constraint by using the IF EXISTS clause and which is explained by the syntax and examples.

The purpose of the IF the EXISTS clause is that it is only used when that table exists in the database. If the table or column already existed in the database then the purpose of getting the resultset from the query will not happen.

The syntax of the MariaDB drop foreign key column with IF EXISTS clause is given below:

SYNTAX:

ALTER TABLE TABLE_NAME
DROP FOREIGN KEY [IF EXISTS] fk_symbol;

The sample example of the MariaDB drop foreign key column with the IF EXISTS clause is given below:

EXAMPLE:

ALTER TABLE USA_EARPHONEPRODUCT DROP FOREIGN KEY IF EXISTS FK_EARNAME_TYPE;

We dropped the foreign key constraint name FK_EARNAME_TYPE from the USA_EARPHONEPRODUCT table using the IF EXISTS clause, as seen in the above query.

If the foreign key FK_EARNAME_TYPE has previously been dropped from the USA_EARPHONEPRODUCT table, an error will be thrown; otherwise, the query will run on its own.

Read: MariaDB Insert Into

MariaDB Foreign Key No Action

In this section, we will learn how to keep reference_option as NO ACTION keyword in the MariaDB FOREIGN KEY column and it is explained by syntax and examples.

The No Action keyword in the Reference_option has the same effect as the RESTRICT keyword in the refernce_option. The modification of the parent table is not possible. With a 1451 error (SQLSTATE ‘2300’), the statement is aborted. Both ON DELETE and ATE have this default behaviour.

The syntax of the MariaDB Foreign Key No Action is given below:

SYNTAX:

ALTER TABLE TABLE_NAME 
ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [IF NOT EXISTS] [index_name] (index_col_name,...)
        reference_definition

The sample example to use the reference_option as NO ACTION keyword in the foreign key column is given below:

EXAMPLE:

ALTER table USA_EARPHONEPRODUCT 
ADD CONSTRAINT FK_EARNAME_TYPE
FOREIGN KEY IF NOT EXISTS (PRODUCT_NAME)
REFERENCES USA_EARPHONECOMPANY(PRODUCT_NAME)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

We utilized the reference option as NO ACTION in this query, which operates in the same way as the RESTRICT keyword. In the USA_EARPHONEPRODUCT table, we changed the foreign key constraint name FK_EARNAME_TYPE of the product name column using the ALTER TABLE statement.

Read: How to Change Column in MariaDB

MariaDB Rename Foreign Key

In this section, we will learn how to rename a foreign key column with the help of the MariaDB ALTER TABLE statement and it is explained by syntax and examples.

Using the MariaDB ALTER TABLE statement, there is no explicit way to rename or replace the foreign key column. The DROP keyword can be used to drop the foreign key constraint_name, and then the ADD keyword can be used to add the foreign key constraint_name in the ALTER TABLE statement.

Using the DROP and ADD keywords, the foreign key constraint name should be different while dropping and adding.

First, let’s see the description of tables BOOK_AUTHOR and BOOKS by the following query:

CREATE TABLE book_author (
  ID INT AUTO_INCREMENT PRIMARY KEY,
  Author_Name VARCHAR(100) NOT NULL
);

CREATE TABLE BOOKS (
  ID INT AUTO_INCREMENT PRIMARY KEY,
  Book_NAME VARCHAR(200) NOT NULL,
  author_id INT,
  CONSTRAINT fk_books_author
    FOREIGN KEY (author_id) REFERENCES book_author (id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
);

As we see in the above query, we have created two tables BOOK_AUTHOR and BOOKS by using the CREATE TABLE statement and we have added the ID from the previous table BOOK_AUTHOR in the BOOKS table for reference as a foreign key.

The syntax for MariaDB rename foreign key is given below:

SYNTAX:

ALTER TABLE TABLE_NAME
DROP FOREIGN KEY `CONSTRAINT_NAME`,
ADD CONSTRAINT `NEW_CONSTRAINT_NAME`FOREIGN KEY (`NEW_COLUMN`) REFERENCES `TABLE_NAME` (`COLUMN_NAME`);

The sample example of the MariaDB Rename Foreign Key is given below:

EXAMPLE:

ALTER TABLE books
DROP FOREIGN KEY `fk_books_author`,
ADD CONSTRAINT `fk_books_author_1` FOREIGN KEY (`author_id`) REFERENCES `book_author` (`id`);

The DROP keyword was used in this query to drop the foreign key constraint_name as FK_BOOKS_AUTHOR. And then the ADD keyword was used to add the new constraint_name as FK_BOOKS_AUTHOR_1 which was taken by reference from the BOOKS_AUTHOR table in the BOOKS table. The ALTER TABLE statement was used to do this.

Read: MariaDB Update Statement

MariaDB Find Foreign Key

In this sub-topic, we will learn how to find the foreign key constraint_name and it is explained by examples.

Constraints can be implemented at the table level with MariaDB’s CREATE TABLE and ALTER TABLE statements. A table constraint limits the amount of data you may enter into the table. MariaDB throws an error if you try to insert invalid data into a column.

The TABLE CONSTRAINTS field in the Information Schema contains information about tables with constraints.

The following columns are described below:

• CONSTRAINT_CATALOG: It is always defined as DEF.
• CONSTRAINT_SCHEMA: The name of the database that contains the constraint.
• CONSTRAINT_NAME: constraint name
• TABLE_SCHEMA: The name of the database from which table will be used.
• TABLE_NAME: The name of the table
• CONSTRIANT_TYPE: Any constraint, such as UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint, can be used.

The sample example to find the foreign key is given below:

EXAMPLE:

SELECT * FROM information_schema.TABLE_CONSTRAINTS 
WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY' 
AND information_schema.TABLE_CONSTRAINTS.TABLE_NAME = 'BOOKS';

We utilized the SELECT statement to locate the constraint_name of the BOOKS table, which is used as the foreign key column, as seen in the preceding query. As a result, it returned FK_BOOKS_AUTHOR_1 as the constraint_name and FOREIGN KEY as the constraint_type from the BOOKS table in the AIRBNB_DB database.

In this MariaDB tutorial, we will study the use of the MariaDB Left Join clause and we will also cover some examples. There are lists of the topic that comes under discussion:

• MariaDB foreign key
• MariaDB foreign key example
• MariaDB foreign key constraint is incorrectly formed
• MariaDB foreign key create table
• MariaDB foreign key null
• MariaDB foreign key alter
• MariaDB foreign key name
• MariaDB foreign key multiple columns
• MariaDB foreign key on update cascade
• MariaDB foreign key checks off
• MariaDB partition table foreign key
• MariaDB drop foreign key
• MariaDB add foreign key if not exists
• MariaDB foreign key no action
• MariaDB rename foreign key
• MariaDB find foreign key