MariaDB Foreign Key: A Comprehensive Guide 101

The MariaDB Foreign Key constraint is used to connect two tables. A Foreign Key is made up of one or more columns in one table that refer to the PRIMARY KEY in another. The child table is the table that contains the Foreign Key. The referred or parent table is the table that is referenced.

The Foreign Key requirement keeps linkages between tables intact. It prevents the insertion of invalid data into the Foreign Key column. The only values that can be added into the Foreign Key column are those from the referenced table.

This article will show you about MariaDB Foreign Key. It will also provide understanding of MariaDB and its key features before diving deep into constraints and examples of MariaDB Foreign Key.

What is MariaDB?

MariaDB is a MySQL clone that was built by MySQL’s original developers and is a widely used Open SQL (Structured Query Language) RDBMS. The basic reason for their similarity is that MariaDB is a MySQL software branch. The MySQL source code is at the center of everything. They aren’t the same, though. MariaDB has come a long way from 2009, when multiple MySQL authors produced a distinct version from the same source.

When Oracle Corporation purchased MySQL, the MySQL developers were concerned about the product’s future. They were concerned that Oracle might terminate MySQL in order to eliminate a major competitor. They also wanted to ensure that their work was available for free. This is how MariaDB made its debut.

It is completely GPL-licensed and available to the general public. It is now one of the top ten most widely used relational databases in the world as a stand-alone product (holding the 9th position). Among the many MariaDB users are Google, Wikipedia, Tumblr, Amazon Web Services, Ubuntu, RedHat, and others.

In addition to the Windows version, MariaDB has versions for Linux and macOS. This RDBMS works with both Intel and IBM Power8 systems. It is available as a service on Amazon Web Services (AWS), Rackspace Cloud, and Microsoft Azure cloud platforms. The procedure is easy to understand, safe, and convenient to use. It’s no wonder that it’s gaining in popularity.

Key Features of MariaDB

• SQL Support: MariaDB supports the majority of SQL statements, functions, procedures, definitions, variables, and so on. Mysqladmin and mysqldump are two client apps that are accessible (these popular solutions for command-line come with MariaDB). Many useful plugins, such as the audit plugin, are also available. All of these features make database operations considerably easier with MariaDB.
• Execution of Parallel Queries: MariaDB versions 10.0 and higher include this important capability. Several queries can be executed simultaneously. Some queries from the primary database are essentially mirrored in the other databases. In practice, it greatly reduces the time it takes to complete a task. This allows OLAP capabilities for analytical queries to perform much faster and more smoothly as a result.
• Thread Pooling: When there are multiple connections to MariaDB, Thread Pooling is a concept that speeds up operations. Instead of establishing a new thread for each connection, MariaDB uses a pool of previously open threads. A new connection, which takes a thread, sends the query. All searches become faster, which leads to faster results.
• Database Views: Another performance-enhancing tool is database views. The main difference when querying a view is that virtual tables are used. In MariaDB’s functioning, only that specialized, specific table will be used. Regardless of whether the query is directed at them, MySQL impacts all tables associated with that view. The optimized query of a database view improves performance and reduces load.

What is MariaDB Foreign Key?

Foreign Keys are Referential Integrity constraints provided by MariaDB. A Foreign Key is a set of columns in a parent table that refers to another set of columns in a child table. Referential Integrity constraint between two tables is another term for it. The tables with Foreign Keys are referred to as the Child Tables, and the table to which the Foreign Key refers is referred to as the Parent Table. A Foreign Key column in a child table is essentially a reference to the parent table’s primary key. When you utilize a Foreign Key in MariaDB, it automatically applies some integrity requirements.

The following table from the nation sample database shows the regions and countries:

• The Foreign Key in the nations table is region_id, which refers to the regions database’s region_id column. 
• There is a corresponding row in the regions table for each row in the nations table.
• You can’t insert an entry into the nations table without a corresponding row in the regions table because of the Foreign Key constraint. To put it another way, a country cannot exist without a region.
• A table can have numerous Foreign Keys that correspond to separate parent tables’ primary keys.

Syntax of MariaDB Foreign Key

In the below syntax, the create table statement comes first, followed by the constraint name statement, which specifies the Foreign Key name; if you miss this constraint, MariaDB will use the default produced name. 

The next section contains a Foreign Key, a foreign name, and a set of column names separated by a comma within parentheses. The parent name is then specified using the column list after the reference keyword, as seen in the syntax above. 

Finally, using the on delete and on update clauses, you discover how a Foreign Key preserves Referential Integrity between two tables, the child and parent table.

create table table_name (colm name 1, colm name 2, ………colm name N ) [constraint name] Foreign Key [Foreign Key name] (colm name or list) reference parent_table_name (column list or set) [on delete reference_option] [on update reference_option];

How does MariaDB Foreign Key work?

Basically, a Foreign Key is used to maintain referential integrity when the values of both tables match. Let’s look at how a Foreign Key in MariaDB works with different parameters. When we use a Foreign Key in a table, we can regulate the operation by using the following parameters.

• On Delete No action: In Foreign Keys, this is the default argument. The transaction fails at the end of the query if any existing reference key is destroyed. An update clause will be used to update the key.
• On Update No action: This is a Foreign Key default parameter. The transaction fails at the end of the query if any existing key from the parent table is modified. Using the delete clause, we may remove the Foreign Key.

MariaDB Foreign Key Constraints

Each row in the child table must match a row in the parent table if a Foreign Key exists. The same parent row can be matched by many child rows. If all of the Foreign Key values in a child row match those in a parent row in the parent table, the child row matches the parent row. The row has no parents if at least one of the Foreign Key values is NULL, but it is still acceptable.

To ensure that Data Integrity is maintained, MariaDB performs the following checks:

• In the child table, attempting to insert non-matching rows (or updating matching rows in a way that makes them non-matching rows) results in a 1452 error (SQLSTATE ‘23000’).
• 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.
• When the value of a column referenced by a Foreign Key changes and at least one child row exists, MariaDB takes action based on the Foreign Key’s ON UPDATE clause.
• A 1217 error (SQLSTATE ‘23000’) occurs when you try to drop a table that is referenced by a Foreign Key.
• A TRUNCATE TABLE against a table with one or more Foreign Keys is treated as a DELETE without the WHERE clause, ensuring that the Foreign Keys are enforced for each row.

MariaDB Foreign Key constraint with CREATE TABLE

A table called Contact_Info is created in the example below. The following sentence is used to impose the Foreign Key constraint on the EmpID column:

CREATE TABLE Contact_Info (
  Phone_Number VARCHAR(100),
  EmpID INT NOT NULL,
  PersonName VARCHAR(255),
  Address VARCHAR(255),
  Foreign Key (EmpID) REFERENCES Employee(EmpID)
);

The following sentence is used to provide a Foreign Key constraint a name and to define a Foreign Key constraint on several columns:

CREATE TABLE Contact_Info (
  Phone_Number VARCHAR(100),
  EmpID INT NOT NULL,
  PersonName VARCHAR(255),
  Address VARCHAR(255),
  CONSTRAINT FK_Contact_Info Foreign Key (EmpID) 
  REFERENCES Employee(EmpID)
);

The following line shows how to use the Foreign Key constraint on several columns (EmpID and PersonName). Please note that two columns, EmpID and Name, must have PRIMARY KEY constraints in the Employee table.

CREATE TABLE Contact_Info (
  Phone_Number VARCHAR(100),
  EmpID INT NOT NULL,
  PersonName VARCHAR(255),
  Address VARCHAR(255),
  CONSTRAINT FK_Contact_Info Foreign Key (EmpID, PersonName) 
  REFERENCES Employee(EmpID, Name)
);

MariaDB Foreign Key constraint with ALTER TABLE

A table called Contact_Info is created in the example below. The following sentence is used to impose the Foreign Key constraint on the EmpID column:

ALTER TABLE Contact_Info
ADD Foreign Key (EmpID) 
REFERENCES Employee(EmpID);

The following sentence is used to provide a Foreign Key constraint a name and to define a Foreign Key constraint on several columns:

ALTER TABLE Contact_Info
ADD CONSTRAINT FK_Contact_Info 
Foreign Key (EmpID) 
REFERENCES Employee(EmpID);

The following sentence shows how to apply a Foreign Key constraint to several columns (EmpID and PersonName). Please note that two columns, EmpID and Name,must have PRIMARY KEY constraints in the Employee table.

ALTER TABLE Contact_Info
ADD CONSTRAINT FK_Contact_Info 
Foreign Key (EmpID, PersonName) 
REFERENCES Employee(EmpID, Name);

DROP MariaDB Foreign Key constraint

The following line is used to remove the FK_Contact_Info Foreign Key constraint from the Contact_Info table:

ALTER TABLE Contact_Info
DROP Foreign Key FK_Contact_Info;

OR

ALTER TABLE Contact_Info
DROP CONSTRAINT FK_Contact_Info;

Rules for using MariaDB Foreign Key Constraints

Foreign Keys in MariaDB are basically referential integrity constraints that work between parent and child tables. We can either generate Foreign Keys when we build the table or we may use an edit command to do so. When using the reference option in a Foreign Key, the reference option accepts the five values listed below.

• Cascade: When we edit or delete entries in the parent table, the corresponding rows in the child table are automatically updated or deleted.
• Set Null: When we delete rows from the parent table, the child table’s corresponding rows become null.
• Restrict: If reference rows from the parent table are modified, referencing rows from the child table are prohibited.
• No action: It works in the same way as the above-mentioned restrict function.
• Set Default: The set default value in MariaDB was compatible with the now-defunct PBXT storage engine. In MariaDB, that Foreign Key has a default value, and if that value is not accessible, MariaDB displays an error message.

Examples of MariaDB Foreign Key

Let’s look at some examples of how to use a Foreign Key restriction with different types of references.

Setting up Tables with Foreign Key

create table gadgets(
    gadget_id int auto_increment,
    gadget_name varchar(100) not null,
    type_id int,
    primary key(gadget_id),
    constraint fk_type
    Foreign Key(type_id) 
        references gadget_types(type_id)
);

The gadgets table has a Foreign Key (type_id) that refers to the gadget_types database’s type_id column.

Restrict Constraint Example 

The statement below tries to remove a row from the gadget_types table:

delete from gadget_types 
where type_id = 1;

The following error was reported by MariaDB:

SQL Error (1451): Cannot delete or update a parent row: a Foreign Key constraint fails (`nation`.`gadgets`, CONSTRAINT `fk_type` Foreign Key (`type_id`) REFERENCES `gadget_types` (`type_id`)) 

Due to the restrict reference option, the error occurred.

To delete an entry from the gadget_types table, you must first delete all of the gadgets table’s referencing rows.

Set NULL Constraint Example 

The gadgets table’s fk_type Foreign Key constraint can be removed:

alter table gadgets
drop constraint fk_type;

With the on delete set null and on update set null options, you may create a Foreign Key constraint to the gadgets table and delete gadget type id 1 from the gadget_types table:

alter table gadgets 
add constraint fk_type 
Foreign Key(type_id) 
    references gadget_types(type_id)
    on delete set null
    on update set null;
delete from gadget_types
where type_id = 1;

You can also query data from the gadgets dataset and update the gadget type from 2 to 20 in the gadget_types table:

update gadget_types
set type_id = 20
where type_id = 2;
select * from gadgets;

Output:

Cascade Constraint Example 

With the on delete set cascade and on update cascade options, you may apply a Foreign Key constraint to the gadgets table and delete the gadget type id 3 from the gadget_types table:

alter table gadgets 
add constraint fk_type 
Foreign Key(type_id) 
    references gadget_types(type_id)
    on delete cascade
    on update cascade;
delete from gadget_types
where type_id = 3;

Due to the on delete cascade action, MariaDB automatically erased rows from the gadgets table with a type id of 3. The gadget type_id 4 can be changed to 40 as well.

update gadget_types
set type_id = 40
where type_id = 4

Due to the on update cascade action, MariaDB automatically updated rows from the gadgets table whose type id is 4 to 40:

Conclusion

In this article, you have learned about MariaDB Foriegn Key. This article also provided information on MariaDB, its key features and deep dive into MariaDB Foreign Key constraints and a few examples. In case you want to export data from a source of your choice into your desired Database/destination then Hevo Data is the right choice for you! 

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations, with a few clicks. Hevo Data with its strong integration with 150+ sources (including 60+ free sources) such as MariaDB allows you to not only export data from your desired data sources & load it to the destination of your choice, but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis using BI tools.

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

FAQs