MariaDB Foreign Key Constraint Examples

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

Create some sample tables

First, create a table named product_types:

CREATE TABLE product_types(
type_id INT auto_increment PRIMARY KEY,,
name VARCHAR(100) NOT NULL
);

Insert some rows into it

INSERT INTO product_types(name) VALUES ('Health and Beauty'),
    ('Electronics'),
    ('Sports'),
    ('Stationary'),
    ('Baby and Kids');

Output:

Create another table named Products:

CREATE TABLE Products
(
product_id int auto_increment, product_name VARCHAR(100) NOT NULL, type_id INT,
PRIMARY KEY(product_id),  CONSTRAINT fk_type FOREIGN KEY(type_id) REFERENCES products_types(type_id)
);

The Products table has a foreign key ( type_id) that references the type_id column of the products_types table.

Now insert some rows into the Products table:

INSERT INTO Products (product_name, type_id) VALUES 
('Earphones',1), ('Apple iPod',1), ('Football',2), ('Badminton',2), ('Pen',3), ('Pencil',3), ('Diapers',4),  ('Toys',4)
);

Output:

The Restrict Reference Option

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

Query:

DELETE FROM product_types where type_id = 1;

Output:

The error occurred because of the restrict reference option.

To delete a row from the products_types table, we need to remove all the referencing rows from the Products table first.

The Set Null Reference Option

Step 1: Drop the fk_type foreign key constraint from the Products table.

ALTER TABLE Products DROP CONSTRAINT fk_type;

Step 2: Now Add a foreign key constraint to the Products table with the on delete set null and on update set null options.

ALTER TABLE Products ADD CONSTRAINT fk_type  foreign key(type_id) REFERENCES product_types(type_id) on delete set null on update set null;

Step 3: Delete product type id 1 from the product_types table:

DELETE FROM product_types where type_id = 1;

Step 4: Query the data from the Products table:

SELECT * FROM Products;

Output:

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

Step 5: Update the product type from 2 to 10 in the product_types table:

UPDATE product_types set type_id = 10 WHERE type_id =2;

Step 6: Query the data from the Products table:

SELECT * FROM Products;

Output:

Explanation: The values in the type_id column of rows with type_id 2 from the Products table is set to null because of on update set null option.

MariaDB is an open–source database system which is similar to MySQL. It provide various features such as high availability and vertical scalability to allow database to scale up over various nodes or single node as features like Galera Cluster in MariaDB. The Foreign keys are the most important features that help define and establish relationships between tables. In this article, we will what is Foreign Key, its syntax various methods to define a Foreign Key, and examples.