Foreign Key, 진짜 걸어 줘야 할까? - 웹 서비스 DB의 참조 무결성

Enter 🙋‍♀️

Anyone who has worked with relational databases knows that foreign keys exist and what they are used for. They help ensure the integrity of the data.
However, when you're designing tables for web services and actually developing them, there are times when you wonder if it's really the best idea to attach foreign keys. In this post, I'll discuss some of the times I've had that question, and what the best way to go about it is.

Referential integrity and foreign keys

Referential integrity

Referential integrity is a form of data integrity. The Wikipedia definition of 데이터 무결성 is as follows

plain
데이터 무결성(영어: data integrity)은 컴퓨팅 분야에서 완전한 수명 주기를 거치며 데이터의 정확성과 일관성을
유지하고 보증하는 것을 가리키며 데이터베이스나 RDBMS 시스템의 중요한 기능이다. - 위키백과

When designing relationships between tables in a database, the data to be loaded must be integrity. This means that the criteria for determining integrity is the accuracy and consistency of the data.

Of these, 참조 무결성 is related to foreign keys. Let's say we have a table like this

Example table

You have a table called Students.

[students].

Name	Number	Department
TaeYoung Kim	21327	Department of Mathematics
Ji Yeon Lee	32719	Department of Philosophy
Junho Nam	33828	Mechanical Engineering Department

And let's say you have a department table.

[department].

Name	College	Department Chair
Department of Mathematics	College of Natural Sciences	Chulho Lee
Department of Philosophy	College of Arts and Letters	Ae-Ri Moon
Department of Mechanical Engineering	College of Engineering	Hoonjeong Kang

The 학과 column in the student's table can reference the [[7]]] column in the department table. This referential relationship makes it easy to know, for example, who is the chair of the department in which a student is enrolled.

If you have these references, you shouldn't just delete the information in the department table. You would no longer have any information about the department columns used by the student table. This would violate referential integrity (= break referential integrity).

Therefore, we use a Foreign Key constraint. The most important thing about this constraint is Foreign Key로 참조하는 값이 해당 테이블에서 유일한 값이어야 한다. This means that each value of the name column in the department table must be unique. No record should exist that looks like this

[Department].

Name	College	Department Chair
Department of Mathematics	College of Natural Sciences	Chulho Lee
Department of Mathematics	College of Natural Sciences	In-Tae Jung

The math student's department chair cannot be found because there are two records with the name 수학과.

This means that the name column should either be 기본키 컬럼 or 유일키 컬럼.

Constraints can also be used to determine how the student table should change when there is a change in the department table (the reference table). This can be applied when it is deleted (ondelete) or when the primary/unique key changes (onupdate).

plain
수학과 정보를 학과 테이블에서 삭제를 시도한다면(ondelete),
1) 김태영 학생 레코드도 삭제한다. -> CASCADE
2) 김태영 학생 정보 중 학과 값은 null로 변경한다. -> SET NULL
3) 아무 일도 일어나지 않게 한다(수학과 레코드 삭제도 금지한다.) -> RESTRICT

Flask-SQLAlchemy Example

python
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://username:password@localhost:5432/postgres'

db = SQLAlchemy(app)

# 학과 테이블
class Major(db.Model):
	id = db.Column(
    	db.Integer,
        primary_key=True,
        autoincrement=True,
        nullable=False
    )
    name = db.Column(db.String)
    std_no = db.Column(db.Integer)
    major = db.Column(db.String)


# 학생 테이블
class Student(db.Model):
    id = db.Column(
    	db.Integer,
        primary_key=True,
        autoincrement=True,
        nullable=False
    )
    name = db.Column(db.String)
    std_no = db.Column(db.Integer)

    # 테이블 예시에서는 이름 자체를 썼지만, 실제 서비스에서는 id를 이용하여 하는 경우가 더 많음
    # Foreign key 제약 조건을 설정
    major_id = db.Column(
		db.Integer,
        db.ForeignKey(Major.id, ondelete="SET NULL", onupdate="CASCADE")
    )

When is referential integrity hard to maintain?

However, when developing real-world services, it is sometimes difficult to maintain referential integrity. Referential integrity is difficult to maintain because of the inconvenience of using foreign key constraints.

The inconvenience is due to the following reasons

When neither ON DELETE nor ON UPDATE conditions, you need to consider the order of deletion/change to clear records.
- For example, if you want to delete a record from the department table, you need to find the students in the student table and delete them first.
The same goes for data inserts. Inserting data also requires order.
- For example, if you want to insert information about Jitae Choi, a computer science major, you must first check if the computer science major information exists in the department table. If it doesn't exist, you need to create this record first before you can create Jitae Choi.

The more complex the table structure, the more things you need to consider, such as cross-referencing between tables or referencing multiple tables from a single table. This is why there are cases where you may not want to enforce foreign key constraints, even if it means sacrificing integrity.

What to consider instead of sacrificing referential integrity

When we give up referential integrity, we mean that if the information in the department table changes, the records in the student table are unaffected.

This means that there may be data that logically shouldn't exist,

[student].

Name	Number	Department
TaeYoung Kim	21327	Department of Mathematics
Ji Yeon Lee	32719	Department of Philosophy
Junho Nam	33828	Mechanical Engineering Department

[Departments]

Name	College	Department Chair
Department of Philosophy	College of Arts and Letters	Ae-Ri Moon
Department of Mechanical Engineering	College of Engineering	Hoonjeong Kang

_(The math department record has been cleared, but Tae-Young Kim's student information is still there.

This means that any changes to the department table information will not be applied to the student table.

[Student]

Name	Number	Department
TaeYoung Kim	21327	Department of Mathematics
Ji Yeon Lee	32719	Department of Philosophy
Junho Nam	33828	Mechanical Engineering Department

[Departments]

Name	College	Department Chair
Department of Applied Mathematics	College of Natural Sciences	Chulho Lee
Department of Philosophy	College of Arts and Letters	Ae-Ri Moon
Department of Mechanical Engineering	College of Engineering	Hoonjung Kang

(The name value of the record for the Department of Mathematics has been changed to Applied Mathematics, but the student information for TaeYoung Kim remains the same.

In this case, you should use a join condition to get the information or change the information together.

Flask-SQLAlchemy example

python
data = db.session.query(
	Student,
    Major,
).join(  # outerjoin을 사용하면, 논리적으로 삭제된 데이터를 볼 수 있기 때문에 무조건 join으로 한다.
	Major,
    Major.id == Student.major_id,
).all()

Closing🤗

At the beginning of my career, I thought it was natural to set foreign keys while thinking about integrity. However, as the service functions became more complex and the table structure became more complicated, setting foreign key constraints became inconvenient. In this case, I gave up on integrity.

However, as much as we sacrifice integrity, we must also consider it at the product service level, and what is more efficient for product development should be judged by considering the complexity of the data structure and the complexity of the function.

References

Data integrity - Wikipedia