[ MySQL | TIL ] 데이터베이스 설계 - foreign key 여러개

Haksoo JI·2023년 1월 16일

TIL mysql

[ TIL ]

목록 보기

16/30

데이터베이스 설계 - foreign key 여러개

⚠️문제 발생

워크벤치를 사용해서 데이터베이스를 설계하던 중에 상당히 골치 아픈 에러를 만났다. 테이블을 생성한 후에 외래키(FK, foreign key)를 설정할 때 나타나는 ERROR: 1452 였다.

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

구글링을 통해서 알게된 에러의 원인은 다음과 같다.

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

참조테이블에 없는 값을 추가해서 발생한 오류
참조 무결성에 따라서 부모키에 해당하는 값만 넣을 수 있음.

참조하는 테이블에 데이터를 먼저 추가한 후, 참조받는 테이블에 데이터를 추가하니 오류해결.

(외래키로 연결한 값을 동일하게 줘야함)

참조
https://reeme.tistory.com/39

🧪시행착오

처음에 에러를 만났을 때, CRUD 연습을 한다고 테이블에 이것저것 데이터를 집어넣은 상태였어서 내가 뭔가 놓치고 맞물리지 않게 넣은 데이터가 있을 것 같다는 생각에 입력된 데이터들을 모두 삭제시키고 다시 시도해봤다.

물론 맞게 넣었다고 생각을 하고 시도해봤던 것이었는데 아무리 찾아도 모르겠어서 그냥 다 지워버리고 빈 테이블만 남겨놓고 외래키 설정을 시도했다.

그래도 여전히 똑같은 에러가 뜬다.

이 문제로 몇 시간동안 고민하다가 그냥 결국 prompt로 입력해보기로 했다. 쿼리문 치는 것이 귀찮기도 하고 워크벤치로 하면 알아서 쿼리문까지 출력해주는데 굳이 쳐야 할 필요성을 못 느껴서 끝까지 시도하지 않던 방법이었다.

프롬프트로 시도하면서 데이터 설계를 생각하면서 테이블 생성의 순서를 정해서 차근 차근 진행했다.

설계한 ERD는 다음과 같다.
ERD

테이블 reviews에서 users와 stores의 id값을 외래키로 참조하고 있으며, orders에서는 나머지 세 테이블의 id값을 모두 외래키로 참조하고 있다.

따라서 users, stores, reviews, orders 순서로 테이블을 생성하면서 생성과 동시에 외래키를 설정했다. 이전에 시도했을 때는 이미 생성된 테이블에서 워크벤치를 통해 새로 외래키를 설정하려고 시도했던것과 다르게 해보았다.

✅해결

다음은 프롬프트(나는 워크벤치의 sql탭에 입력했다)에 입력한 코드이다.

use laundry_delivery_service;

-- foreign key 설정시 워크벤치 GUI를 사용했을 때, 오류가 뜨는 부분이 많음.
-- 테이블간 연결관계를 생각해서 users -> stores -> reviews -> orders 순서로 테이블 생성
-- 테이블 생성시users foreign key 설정하니까 성공

-- CREATE users
-- 비밀번호 바꾸기
ALTER USER 'root'@'localhost' IDENTIFIED BY 'haksooji95';
FLUSH PRIVILEGES;

use laundry_delivery_service;

-- foreign key 설정시 워크벤치 GUI를 사용했을 때, 오류가 뜨는 부분이 많음.
-- 테이블간 연결관계를 생각해서 users -> stores -> reviews -> orders 순서로 테이블 생성
-- 테이블 생성시users foreign key 설정하니까 성공

-- CREATE users
create table users (
	id int not null auto_increment,
    nickname varchar(255) not null,
    password varchar(255) not null,
    email varchar(255) not null,
    phone varchar(255) not null,
    point int unsigned default 1000000,
    createdAt datetime not null default now(),
    updatedAt datetime not null default now() on update now(),
    primary key(id),
    unique index nickname_UNIQUE (nickname ASC),
    unique index email_UNIQUE (email ASC),
    unique index phone_UNIQUE (phone ASC))
    comment = '사용자(고객=customer) 정보'
    default character set = utf8mb4
    engine = InnoDB;

-- CREATE stores
create table stores (
	id int not null auto_increment,
    store_name varchar(255) not null,
    password varchar(255) not null,
    email varchar(255) not null,
    phone varchar(255) not null,
    location varchar(255) not null,
    point int default 0,
    createdAt datetime not null default now(),
    updatedAt datetime not null default now() on update now(),
    primary key(id),
    unique index store_name_UNIQUE (store_name ASC),
    unique index store_email_UNIQUE (email ASC),
    unique index store_phone_UNIQUE (phone ASC))
    comment = '사용자(업체=store) 정보'
    default character set = utf8mb4
    engine = InnoDB;
    
    -- CREATE reviews
    create table reviews (
	id int not null auto_increment,
    rating int not null unsigned,
    comment varchar(255) not null,
    image_path varchar(255) not null,
    reviewer int not null,
    target_store int not null,
    createdAt datetime not null default now(),
    updatedAt datetime not null default now() on update now(),
    primary key(id),
	index reviewer_idx (reviewer ASC),
    constraint reviewer foreign key (reviewer) references users (id) on delete cascade on update cascade,
    index target_store_idx (target_store ASC),
    constraint target_store foreign key (target_store) references stores (id) on delete cascade on update cascade
    )
    comment = '리뷰 정보'
    default character set = utf8mb4
    engine = InnoDB;
    
    -- CREATE orders
    create table orders (
	id int not null auto_increment,
    status int not null,
    location varchar(255) not null,
    image_path varchar(255) not null,
    orderer int not null,
    receiver int not null,
    review int not null,
    createdAt datetime not null default now(),
    updatedAt datetime not null default now() on update now(),
    primary key(id),
	index orderer_idx (orderer ASC),
    constraint orderer foreign key (orderer) references users (id) on delete cascade on update cascade,
    index receiver_idx (receiver ASC),
    constraint receiver foreign key (receiver) references stores (id) on delete cascade on update cascade,
	index review_idx (review ASC),
    constraint review foreign key (review) references reviews (id) on delete cascade on update cascade
    )
    comment = '세탁물 신청 정보'
    default character set = utf8mb4
    engine = InnoDB;