Using SQL Foreign Key: Ensuring Data Integrity

🚀 What Will You Learn in This Guide?

This guide teaches you how to use Foreign Key, the essential tool for maintaining data integrity in large SQL projects.
Preventing the creation of invalid (orphan) records by establishing a relationship between two tables and
**You will learn step by step to ensure referential integrity on MySQL.

🧠 Technical Summary

Subject: Ensuring referential integrity with Foreign Key in SQL
Purpose: Enforce data consistency between tables, prevent incorrect records from being added
Steps:

1. Connecting to MySQL
2. Creating sample database and tables
3. Foreign Key definition
4. Invalid data insertion attempt
5. Add valid data
6. Running query related to JOIN
7. Parental deregistration tests

---

⚙️ 1. Sample Database and Tables Setup

Connect to MySQL Server

sudo mysql -u genixnode_kullanici -p

➡️ Login to the MySQL command line with your user account.

Create Database

CREATE DATABASE sirket_vt;
USE sirket_vt;

➡️ Creates and activates a new company_vt database.

Positions (Parent) Table

CREATE TABLE pozisyonlar (
    pozisyon_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    pozisyon_adi VARCHAR(50)
) ENGINE = InnoDB;

➡️ Creates the reference (parent) table that will hold all valid positions.

Add sample data:

INSERT INTO pozisyonlar (pozisyon_adi) VALUES ('ŞUBE MÜDÜRÜ'), ('MEMUR'), ('SEVİYE 1 AMİR');

Employees (Child) Table

CREATE TABLE calisanlar (
    calisan_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    pozisyon_id BIGINT NOT NULL,
    ad VARCHAR(50),
    soyad VARCHAR(50),
    telefon VARCHAR(50),
    INDEX (pozisyon_id),
    FOREIGN KEY (pozisyon_id) REFERENCES pozisyonlar (pozisyon_id)
) ENGINE = InnoDB;

➡️ Each employee record is linked to a valid position in the positions table.

---

🚫 2. Invalid Data Entry (Foreign Key Error)

Currently valid position_id values are 1, 2, 3. The following queries try to insert data with non-existent IDs:

INSERT INTO calisanlar (pozisyon_id, ad, soyad, telefon) VALUES (4, 'AYŞE', 'YILMAZ', '11111');

❌ Result:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

➡️ MySQL rejects the record because an invalid position_id is found. In this way, data consistency is maintained.

---

✅ 3. Valid Data Entry

INSERT INTO calisanlar (pozisyon_id, ad, soyad, telefon) VALUES
(1, 'CANSU', 'AKIN', '11111'),
(2, 'MUSTAFA', 'DEMİR', '55555'),
(3, 'SELİN', 'UÇAR', '22222');

➡️ A record is added with the current position_id values.

---

🔗 4. Viewing Related Data with JOIN

SELECT
c.calisan_id,
c.ad,
c.soyad,
p.pozisyon_adi,
c.telefon
FROM calisanlar c
LEFT JOIN pozisyonlar p
ON c.pozisyon_id = p.pozisyon_id;

📋 Result:

employee_id	name	surname	position_name	phone
1	CANSU	AKIN	BRANCH MANAGER	11111
2	MUSTAFA	IRON	OFFICER	55555
3	SELİN	FLY	LEVEL 1 SUPERVISOR	22222

➡️ Each employee's position is listed with proper attribution.

---

🛑 5. Parental Unregistration Test

DELETE FROM pozisyonlar WHERE pozisyon_id = 1;

❌ Result:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

➡️ Since there is an associated employee record, deletion is prevented.

Try adding and deleting a new position:

INSERT INTO pozisyonlar (pozisyon_adi) VALUES ('CEO');
DELETE FROM pozisyonlar WHERE pozisyon_id = 4;

✅ It is successful because this position is not tied to any records.

---

💬 Frequently Asked Questions (FAQ)

1. What is the purpose of Foreign Key?

It ensures referential integrity between related tables and prevents incorrect data entry.

2. Why is InnoDB needed?

Foreign key restrictions in MySQL are supported only in the InnoDB engine.

3. Does it affect performance?

It imposes a small overhead on write operations, but provides speed and security in queries.

4. Can Foreign Key checking be disabled?

Yes, it can be temporarily turned off during large data transfer:

SET FOREIGN_KEY_CHECKS = 0;

5. What does CASCADE do?

It ensures that child records are automatically affected when the parent is deleted or updated.

6. Is the column with a Foreign Key defined automatically indexed?

Yes, MySQL does this automatically; This improves JOIN performance.

---

🧭 Result

In this guide, you learned how to ensure data consistency and referential integrity by using Foreign Key in SQL databases. This method allows you to establish secure data relationships at the database level without writing extra validation at the application level.

You can immediately set up and test your SQL database on the GenixNode infrastructure for data security and high performance in your projects.