MySQL 참조 관계

1. 참조 관계에 대해서

 

참조 관계

부모 테이블 , 자식 테이블 개념으로 , 

부모가 없는 자식 데이터는 있을 수 없다. ( 참조할 데이터가 없기 때문 )

하지만 부모 테이블과 자식 테이블을 모두 생성하고 참조 관계를 맺었는데

부모 데이터를 삭제한다면 ( 조건 : cascade 속성 사용한다면 ) 자식 테이블은 고아 객체가 된다.

 

2. 관계

1. 1:1관계(일대일)

하나의 레코드가 다른 테이블의 레코드 한개와 연결된 경우이다.

2. 1:N관계(일대다) ( 주로사용 )

하나의 레코드가 서로 다른 여러 개의 레코드와 연결된 경우이다.

만약 User 테이블에 ID(int) , name(varchar) 칼럼이 있다고 하고

phonebook 테이블에 ID(int) , phone_number(varchar) , user_id(int) 칼럼이 있다고 하면

이 구조에서는 한명의 유저가 여러개의 전화번호를 가질 수 있다.

하지만 여러명의 유저가 한개의 전화번호를 가질 수 없다.

이런 1:N (일대다) 관계는 관계형 데이터베이스에서 가장 많이 사용한다고 볼 수 있다.

3. N:M(다대다)

여러개의 레코드가 다른 테이블의 여러 개의 레코드와 관계가 있는 경우다.

N:M(다대다) 관계를 위해 스키마를 디자인할 때에는

너무 복잡하기 때문에 중간 테이블로 N:1 , 1:M 관계로 분리할 수도 있다.

1:N(일대다) 관계와 비슷하지만 양방향에서 다수의 레코드를 가질 수 있다.

 

 

3. 참조 관계의 특징

1. 부모 테이블을 생성하기 전에 자식 테이블을 생성할 때 생성되지 않는다.

참조할 테이블이 없기 때문에 ERROR 1824 에러가 나온다.

 

2. 참조 관계가 맺어진 후에 부모 테이블을 삭제하는 경우 삭제 불가능

ERROR 3730 에러가 나온다.

하지만 cascade 속성을 사용한다면 우회가능하다.

 

 

 

1. 예시

drop table if exists board1;
create table board1(
	id bigint,
    board_writer varchar(20) not null, -- 작성자
    board_contents varchar(500), -- 내용
    constraint pk_board1 primary key(id)
    );

board1 테이블을 생성하는데 왜 먼저 drop으로 테이블을 제거하는가 ?

- 그전의 값이 입력되었을 수도 있기 때문에 데이터를 날린다.

if exists 란 ?

- if exists를 사용하면 만약 테이블이 없을 경우 drop을 하면 에러가 나서 여러 줄을 한꺼번에 실행할 때

그곳에서 멈추지만 if exists를 사용하면 에러>경고로 변하기 때문에 경고는 멈추지 않기 때문에 사용하면 편하다.

-- 댓글 테이블 : 댓글은 존재하는 게시글에서만 작성 가능하며 , 
-- 게시글의 글번호(ID)를 참조하는 관계로 정의

drop table if exists comment1;
create table comment1 (
	id bigint, -- 댓글 번호
    comment_writer varchar(20) not null, -- 댓글 작성자
    comment_contents varchar(200), -- 댓글 내용
    board1_id bigint, -- 어떤 게시글에 작성된 댓글인지 글번호 정보가 필요함
    -- 댓글테이블(comment1)의 pk지정
    constraint pk_comment1 primary key(id),
    -- 참조관계 지정을 위해 comment1 테이블의 board1_id 컬럼을
    -- board1 테이블의 id 컬럼을 참조하는 관계로 정의
    -- 참조하는 데이터는 타입이 같이야 한다 ( 여기서는 board(id) = bigint, comment1(id)= bigint 이므로 가능 )
    constraint fk_comment1 foreign key(board1_id) references board1(id)
    );

insert into board1(id, board_writer, board_contents)
values(1, 'writer1', 'contents1');
insert into board1(id, board_writer, board_contents)
values(2, 'writer2', 'contents2');
insert into board1(id, board_writer, board_contents)
values(3, 'writer3', 'contents3');
insert into board1(id, board_writer, board_contents)
values(4, 'writer4', 'contents4');
select * from board1;

board1 테이블에 데이터를 삽입하였다 ( 글 )

 

 

-- 댓글 데이터
-- 1번 게시글에 대한 댓글
insert into comment1(id, comment_writer, comment_contents, board1_id)
	values(1, 'c writer1', 'c contents1' , 1);
-- 1번 게시글에 대한 2번째 댓글
insert into comment1(id, comment_writer, comment_contents, board1_id)
	values(2, 'c writer1', 'c contents1' , 1);
-- 2번 게시글에 대한 댓글
insert into comment1(id, comment_writer, comment_contents, board1_id)
	values(3, 'c writer2', 'c contents2' , 2);
-- 3번 게시글에 대한 댓글
insert into comment1(id, comment_writer, comment_contents, board1_id)
	values(4, 'c writer3', 'c contents3' , 3);
insert into comment1(id, comment_writer, comment_contents, board1_id)
	values(5, 'c writer4', 'c contents4' , 5);    -- board1의 5번째 글이 없기 때문에 에러
select * from comment1;

(comment1의 pk , comment_writer , comment_contents , board1_id ( board의 pk값 참조) )

마지막 문구에 board1의 pk값이 5가 없기 때문에 실행이 불가능하다.

 

-- 부모 데이터 삭제
-- 1,2,3번 게시글에는 댓글이 있고 ,  4번 데이터에는 댓글이 없음
-- 4번 데이터 삭제
delete from board1 where id= 4; -- where 조건은 항상 PK컬럼
delete from board1 where id= 3; -- comment1 데이터가 자식으로 있기때문에 삭제안됨, 에러
-- 2번 게시글에 있는 댓글 삭제
delete from comment1 where id= 3;
delete from board1 where id= 2; -- 댓글이 없어졌기 때문에 글삭제 가능

기본적으로는 부모 데이터를 삭제하려면 참조하는 자식 클래스를 삭제해야 삭제 할 수 있다.