2021-12-15 17:14 작성

My SQL basic 메모

Table of contents

MySQL ON DELETE CASCADE 소개

MySQL은 foreign key에 대해 ON DELETE CASCADE 참조 action라고 불리는 효과적인 방법을 제공하는데 parent table의 데이터를 삭제할 때 child tables의 데이터도 함께 삭제되도록 한다.



MySQL ON DELETE CASCADE 예시

buildings와 rooms라는 두 가지 tables를 가지고 있다고 가정하자. 데이터베이스 모델에서 각각의 building은 하나 혹은 그 이상의 rooms를 가지고 있다. 그러나 각각의 room은 오직 하나의 building에만 속한다. 어떤 room도 building 없이는 존재하지 않는다.

buildings와 rooms tables 간의 관계는 다음과 같이 일대다(1:N)다.


buildings table에서 한 개의 row를 삭제할 때, buildings table의 특정 row를 참조하는 rooms table의 모든 rows도 함께 삭제하길 원할 것이다. 예를 들어, 다음과 같이 building 번호가 2인 row를 삭제할 때:

DELETE FROM buildings

WHERE building_no = 2;

rooms table의 building 번호가 2인 rows를 삭제하길 원할 것이다.

다음은 ON DELETE CASCADE referencial action(참조 조치)이 어떻게 작동하는지 단계적으로 증명한다.

Step 1. buildings table을 생성한다:

CREATE TABLE buildings (
    building_no INT PRIMARY KEY AUTO_INCREMENT,
    building_name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL
);

Step 2. rooms table을 생성한다:

CREATE TABLE rooms (
    room_no INT PRIMARY KEY AUTO_INCREMENT,
    room_name VARCHAR(255) NOT NULL,
    building_no INT NOT NULL,
    FOREIGN KEY (building_no)
        REFERENCES buildings (building_no)
        ON DELETE CASCADE
);

ON DELETE CACADE 절은 foreign key constraint 정의의 끝에 오는 것에 주의한다.

Step 3. buildings table에 rows를 삽입한다:

INSERT INTO buildings(building_name,address)
VALUES('ACME Headquaters','3950 North 1st Street CA 95134'),
      ('ACME Sales','5000 North 1st Street CA 95134');

Step 4. buildings table의 데이터를 쿼리한다:

SELECT * FROM buildings;

output:

building_no building_name address
1 ACME Headquaters 3950 North 1st Street CA 95134
2 ACME Sales 5000 North 1st Street CA 95134

Step 5. rooms table에 rows를 삽입한다:

INSERT INTO rooms(room_name,building_no)
VALUES('Amazon',1),
      ('War Room',1),
      ('Office of CEO',1),
      ('Marketing',2),
      ('Showroom',2);

Step 6. rooms table의 데이터를 쿼리한다:

SELECT * FROM rooms;

output:

room_no room_name building_no
1 Amazon 1
2 War Room 1
3 Office of CEO 1
4 Marketing 2
5 Showroom 2

3개의 rooms가 building no 1에 속하고 2개의 rooms가 building no 2에 속한다.

Step 7. buildings table에서 building no.2를 삭제한다.

DELETE FROM buildings 
WHERE building_no = 2;

Step 8. rooms table을 쿼리해 자동으로 삭제되었는지 확인한다.

SELECT * FROM rooms;

output:

room_no room_name building_no
1 Amazon 1
2 War Room 1
3 Office of CEO 1

자동으로 rooms의 building_no 2를 가진 rooms가 모두 삭제된 것을 확인 할 수 있다.

ON DELETE CASCADE는 Inno DB와 같이 foregin keys를 지원하는데 storage engines에서만 작동한다는 것에 주의한다.

MyISAM과 같은 몇몇 table types는 foreign keys를 지원하지 않으므로 ON DELETE CASCADE를 사용하길 계획하고 있다면 적절한 storage engines를 선택 해야 한다.



MySQL ON DELETE CASCADE action에 영향 받은 tables를 찾는 tips

때때로 어느 table의 데이터를 삭제할 때 어떤 table이 ON DELETE CASCADE referencial action의 영향을 받았는지 아는 것은 유용하다. 다음과 같이 information_schema 데이터베이스의 referential_constraints에서 데이터를 쿼리할 수 있다:

USE information_schema;

SELECT
    table_name
FROM
    referential_constraints
WHERE
    constraint_schema = 'database_name'
        AND referenced_table_name = 'parent_table'
        AND delete_rule = 'CASCADE';

예를 들어 classicmodels 데이터베이스의 CASCADE 삭제 규칙을 가지면서 buildings와 연계된 tables를 찾기 위해 다음의 쿼리를 사용한다:

USE information_schema;

SELECT 
    table_name
FROM
    referential_constraints
WHERE
    constraint_schema = 'classicmodels'
        AND referenced_table_name = 'buildings'
        AND delete_rule = 'CASCADE'
table_name
rooms