MariaDB Foreign Key Constraints

Summary: in this tutorial, you will learn how to use the MariaDB foreign key constraints to enforce the referential integrity between tables.

What is a foreign key

A foreign key is a column or set of columns in a table that references a column or a set of columns in another table, which enforces the referential integrity between the two tables.

A table that has the foreign key is called a child table while the table to which the foreign key references is known as a parent table.

Typically, a foreign key column in a child table references to the primary key column of the parent table.

See the following regions and countries table from the nation sample database:

The region_id in the countries table is the foreign key that references to the region_id column in the regions table.

For each row in the countries table, you can find a corresponding row in the regions table.

The foreign key constraint prevents you from inserting a row into the countries table without a corresponding row in the regions table. In other words, a country will not exist without a region.

A table can have multiple foreign keys that refer to the primary keys of different parent tables.

Defining a foreign key constraint

To define a foreign key for a table, you use the foreign key constraint. Typically, you define foreign key constraints for a table at the time of the table creation.

The following illustrates the syntax of defining a foreign key constraint:

create table table_name(
    column_list,
    ...,
    Code language: SQL (Structured Query Language) (sql)

[constraint constraint_name]

foreign key [fk_name](column_list) references parent_table(column_list)

[on delete reference_option]

[on update reference_option]

);

In this syntax:

First, specify the name of the foreign key constraint after the constraint keyword. MariaDB will implicitly assign a generated name if you skip the constraint clause.
Second, specify the name of the foreign key followed by a list of comma-separated column names placed within parentheses. The foreign key name is optional.
Third, specify the parent table with a list of columns to which the foreign key columns reference after the references keyword.
Finally, determine how foreign key constraint maintains the referential integrity between the two tables using the on delete and on update clauses.

The reference_option provides actions that MariaDB will take when values in the parent table are deleted (on delete) and/or updated (on update). The reference_option accepts one of the following five values with the corresponding effects:

cacade: when a row in the parent table is deleted or updated, the corresponding row in the child table is automatically deleted or updated.
set null: when a row in the parent table is deleted or updated, the corresponding row in the child table is set to null.
restrict: if a row in the parent table has referencing rows in the child table, it cannot be updated or deleted.
no action: has the same effect as restrict.
set default: only worked with obsolete PBXT storage engine. The foreign key columns will set to their default values if the columns in the parent table are updated or deleted. If no default values available, MariaDB will raise an error.

By default, MariaDB uses the restrict option if you don’t specify either on delete or on update clauses.

Adding a foreign key constraint to a table

To add a foreign key constraint to an existing table, you use the alter table statement:

alter table table_name
 Code language: SQL (Structured Query Language) (sql)

[constraint fk_constraint_name]

foreign key [fk_name](column_list) references parent_table(column_list)

[on delete reference_option]

[on update reference_option]

Removing a foreign constraint from a table

To drop a foreign constraint, you use the alter table drop constraint statement:

alter table table_name
drop constraint fk_constraint_name;
Code language: SQL (Structured Query Language) (sql)

MariaDB foreign key constraint examples

Let’s take some examples of using a foreign key constraint with various reference options.

Setting up sample tables

First, create a new table called gadget_types:

create table gadget_types(
    type_id int auto_increment,
    name varchar(100) not null,
    primary key(type_id)
);
Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the gadget_types table:

insert into gadget_types(name)
values
    ('Entertainment'),
    ('Computing'),
    ('Communication'),
    ('Lifestyle'),
    ('Cameras');
Code language: SQL (Structured Query Language) (sql)

Third, create another table called gadgets:

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)
);
Code language: SQL (Structured Query Language) (sql)

The gadgets table has a foreign key ( type_id) that references the type_id column of the gadget_types table:

constraint fk_type
foreign key(type_id) 
    references gadget_types(type_id)
Code language: SQL (Structured Query Language) (sql)

Finally, insert some rows into the gadgets table:

insert into 
    gadgets(gadget_name,type_id)
values
    ('Amazon Kindle',1),
    ('Apple iPod',1),
    ('Audio Highway Listen Up',1),
    ('Apple iPad',2),
    ('MicroSD',2),
    ('Apple iPhone',3),
    ('BlackBerry 6210',3),
    ('Pager',3),
    ('Air Taser Model 34000',4),
    ('Credit Card',4),
    ('Zippo',4),
    ('Casio G-Shock DW-5000C',4),
    ('Nikon F',5),
    ('Canon EOS 5D Mark II',5);
Code language: SQL (Structured Query Language) (sql)

The restrict reference option

The following statement attempts to delete a row from the gadget_types table:

delete from gadget_types 
where type_id = 1;
Code language: SQL (Structured Query Language) (sql)

MariaDB issued the following error:

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`)) 
Code language: SQL (Structured Query Language) (sql)

The error occurred because of the restrict reference option.

To delete a row from the gadget_types table, you need to remove all the referencing rows from the gadgets table first.

The set null reference option

First, drop the fk_type foreign key constraint from the gadgets table:

alter table gadgets
drop constraint fk_type;
Code language: SQL (Structured Query Language) (sql)

Second, add a foreign key constraint to the gadgets table with the on delete set null and on update set null options:

alter table gadgets 
add constraint fk_type 
foreign key(type_id) 
    references gadget_types(type_id)
    on delete set null
    on update set null;
Code language: SQL (Structured Query Language) (sql)

Third, delete gadget type id 1 from the gadget_types table:

delete from gadget_types
where type_id = 1;
Code language: SQL (Structured Query Language) (sql)

Fourth, query data from the gadgets table:

select * from gadgets;
Code language: SQL (Structured Query Language) (sql)

MariaDB Foreign Key - On Delete Set Null

As shown clearly from the output, the values in the type_id column of rows with type_id 1 from the gadgets table were set to null because of the on delete set null option.

Fifth, update the gadget type from 2 to 20 in the gadget_types table:

update gadget_types
set type_id = 20
where type_id = 2;
Code language: SQL (Structured Query Language) (sql)

Sixth, query data from the gadgets table:

select * from gadgets;
Code language: SQL (Structured Query Language) (sql)

MariaDB Foreign Key - On Update Set Null

The values in the type_id column of rows with type_id 2 from the gadgets table were set to null because of the on update set null option.

Finally, remove the orphan rows from the gadgets table:

delete from gadgets
where type_id is null;
Code language: SQL (Structured Query Language) (sql)

The cascade reference option

First, drop the fk_type foreign key constraint from the gadgets table:

alter table gadgets
drop constraint fk_type;
Code language: SQL (Structured Query Language) (sql)

Second, add a foreign key constraint to the gadgets table with the on delete set cascade and on update cascade options:

alter table gadgets 
add constraint fk_type 
foreign key(type_id) 
    references gadget_types(type_id)
    on delete cascade
    on update cascade;
Code language: SQL (Structured Query Language) (sql)

Third, delete the gadget_type id 3 from the gadget_types table:

delete from gadget_types
where type_id = 3;
Code language: SQL (Structured Query Language) (sql)

MariaDB automatically deleted rows from the gadgets table whose type_id is 3 because of the on delete cascade action.

select * from gadgets;
Code language: SQL (Structured Query Language) (sql)

Fourth, update the gadget_type id 4 to 40:

update gadget_types
set type_id = 40
where type_id = 4
Code language: SQL (Structured Query Language) (sql)

MariaDB automatically updated rows from the gadgets table whose type_id is 4 to 40 because of the on update cascade action:

select * from gadgets;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned about the MariaDB foreign key constraints and how to use them to enforce the referential integrity between tables.

Was this tutorial helpful ?