Foreign Key in MariaDB

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.

Introduction to FOREIGN KEY in MariaDB

FOREIGN KEY stands for a column or the set of columns table that creates a foreign key connection to the primary key or unique index in another table. It is important for the basis of referential integrity which ensures data relationships remain consistent across tables.

In MariaDB, a Foreign Key is declared within the structure of a table using the FOREIGN KEY constraint. This restriction defines which columns in the table correspond to primary or unique key columns of another table. Foreign keys enforce referential integrity.

Add Foreign Key to a Table

To add a foreign key to a table in MariaDB we need to ensure that the referenced columns in the related table have a primary key or a unique key constraint.

Syntax:

CREATE TABLE table_name(columns_list,
.....
 FOREIGN KEY (column_name) REFERENCES parent_table_name(column_name)

Explanation:

• CREATE TABLE table_name: This part create a new table and give the name of the table.
• columns_list: There will be the list of columns and their data types that you want to include in the table.
• CONSTRAINT [constraint_name]: Specify the name of the foreign key constraint after the constraint keyword.
• FOREIGN KEY [fk_name] (col_name): 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.
• REFRENCES parent_table_name (col_name): Specifies the referenced table and column that the foreign key in the current table refers to.

Setting Up Environment

Let’s create tables and add foreign key to them:

Create Departments table

Query:

CREATE TABLE Departments
(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
)

Create Workers table

Query:

CREATE TABLE worker 
(
    worker_id INT  PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    dept_id INT,
    salary DECIMAL(10, 2),
   CONSTRAINT fk_dept_id FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

Output:

Foreign Key Added

Explanation: The above query create a table called worker with a columns and a foriegn key constraint fk_dept_id on the dept_id referencing the dept_id column in Departments table and ensruing referential integrity between the two tables in MariaDB.

Add Foreign Key Constraint with ALTER TABLE

To add a foreign key constraint to an existing table, we can use alter table statement:

Query:

ALTER TABLE table_name ADD CONSTRAINT constraint_name
FOREIGN KEY (col_name) REFERENCES parent_table_name(col_name);

Example:

Let’s create a table and after that add foreign key using alter table statement:
Query:

CREATE TABLE employees 
(
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    dept_id INT,
     Hire_date DATE
);

Add foreign key by executing below command:

Query:

ALTER TABLE employees ADD CONSTRAINT fk_dept_id FOREIGN KEY (dept_id) REFERENCES Departments(dept_id);

Output:

Dropping a Foreign Key from a Table

Suppose we need to remove the foreign key constraints from the table in a MariaDB. We will use a ALTER TABLE statement with the DROP FOREIGN KEY clause to drop the foreign key constraints.

Query:

ALTER TABLE worker DROP FOREIGN KEY fk_dept_id;

Output:

Dropped Foreign Key

Explanation: In the above Query, We have successfully remove the foreign key constraints which is fk_dept_id from the worker table.

Add Foreign Key on Multiple Columns

To add the Foreign Key constraints on multiple tables we will use a ALTER TABLE command.

Query:

ALTER TABLE workers FOREIGN KEY (employee_id, first_name)  REFERENCES employees(employee_id, first_name);

Explanation: This statement defines how to add a FOREIGN KEY constraint on multiple columns (employee_id and first_name). Please note that, the Employees table must have PRIMARY KEY constraint on these columns such as employee_id and first_name.

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:

product_types Table

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:

Products table

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.

SQL Error

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:

Product Table

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:

Product Table

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.

Conclusion

In MariaDB, foreign keys is important constrainnts for ensuring data consistency and table relations. Foreign keys also help in implementing referential integrity which ensures that the database is consistent and reliable. In this article we have seen how to define, insert and delete foreign keys enables database administrators in designing as well as managing databases schemas.