정규화 - 2 (JOIN문)

 

FK의 값을 보고 외부 테이블의 값을 끌어오는 SQL문법

JOIN(조인)문

 

 

 

그렇다면 분리되어 있는데(정규화가 되어 있는데) 어떻게 데이터를 불러올 수 있을까?

 

대학교

 학번     이름      과       동아리     전공

1                                   101

2                                  101

3                                  102

4                                  103

5                                  102

6                                  103

 

예를 들위 위의 표에서 101 동아리를 보고 싶다고

SQL 문을 다음과 같이 쓴다면

SELECLT 동아리 FROM 대학교;

   >>101 숫자만 나온다

그래서

SELECT (JOIN 으로 가져온 외부 데이블의 값) AS 동아리명 FROM 대학교;

 

---

만약 이런 요청이 들어 온다면 어떻게 해야할까?

 

아래에서는 지금은 제품 판매자 Id가 나오는데

 

 

판매자의 name값이 나오게 해달라고 하면 어떻게 할까?

 

CREATE TABLE MEMBER(
	MID VARCHAR(20) PRIMARY KEY,
	PASSWORD VARCHAR(20) NOT NULL,
	NAME VARCHAR(30) NOT NULL,
	ROLE VARCHAR(10) DEFAULT 'USER'
);



CREATE TABLE PRODUCT(
	NUM INT PRIMARY KEY,
	NAME VARCHAR(50) NOT NULL,
	DETAILS VARCHAR(100),
	PRICE INT,
	CNT INT,
	SELLER VARCHAR(20) -- NOT NULL충돌되기 때문에 지움 -- FK,외래키,외부키 == 상대 테이블의 PK를 받아옴
);

 

위에서 MEMBER 에서 NAME이 VARCHAR(30) 이니까 아래 PRODUCT에서  SELLER 를 NAME으로 바꾸면 될까??

 

안된다

 

1. FK 는 상대 테이블의 PK이어야한다.

 

2. NAME으로 하면, 똑같은 이름을 가진 사용자를 구분할 수 없기 때문에

PK가 아닌 컬럼으로 설정하면, UNIQUE하지 않아서 구분할 수 없기 때문이다

 

그렇다면 NAME VARCHAR(30) NOT NULL;을 추가 하면 될까?

그럼 데이터 중복이 된다.

 

테이블을 바꾸는 것이 아니라!

정답은 JOIN문을 써주는 것이다.!!

 

지금부터 A to Z 하나씩 만들어 보자

 

CREATE TABLE A(
	NUM INT PRIMARY KEY,
	NAME VARCHAR(50) NOT NULL,
	SCORE INT
); -- 대학교

 

A테이블을 하나 만든다

 

CREATE TABLE B(
	NUM INT PRIMARY KEY,
	NAME VARCHAR(50) NOT NULL,
	AAA INT NOT NULL -- FK == A의 PK 값 NUM을 받아와야만함
); -- 동아리

 

B 테이블을 하나 만든다

 

이 둘은 연결되어 있는 제약조건이 전혀 없다.

 

제약 조건을 추가함으로 제약조건을 걸어준다.

 

FK 제약 조건을 추가하는 두가지 방법이 있다

1)원본 데이터가 사라질 때, 연결 데이터도 함께 사라지는 방법

       예를 들어 동아리 농부구에 가입했는데 농구부가 사라졌다. 그러면 학생이 아닌가?

 

2) 원본 데이터가 사라질 때, 연결 데이터도 유지되는 방법

       예를 들어 댓글을 썼는데 회원 탈퇴를 한다 그러면 댓글, 피드들 모두 사라진다. 내가 했던 모든 기록이 사라진다!

 

 

1) 원본 데이터가 사라질 때, 연결 데이터도 함께 사라지는 방법

 

 

ALTER TABLE B -- 테이블 제약조건 변경하고 싶
ADD CONSTRAINT FK_AAA --제약조건을 추가할꺼야, FK제약 조건을 할꺼 (이때 해 FK 값을 언더바 뒤어 적는다)
FOREIGN KEY (AAA)--외부키는 AAA
REFERENCES A(NUM) -- A테이블 NUM에 의존하고 있어
ON DELETE CASCADE;-- 얘는 지우면 따라서 지워질 꺼야

 

 

INSERT INTO A VALUES(1,'사과',100);
INSERT INTO A VALUES(2,'바나나',200);

 

 

--INSERT INTO B VALUES (100, '티보0',?????); ??? 자리가 FK 자리==> 상대PK 받아온다 === 즉 1,2만 들어 갈 수있다
INSERT INTO B VALUES (100, '티보0',1);
INSERT INTO B VALUES (101, '티보1',1);
INSERT INTO B VALUES (102, '티보2',1);
INSERT INTO B VALUES (103, '티보3',2);
INSERT INTO B VALUES (104, '티보4',2);

 

 

만약에 

 

INSERT INTO B VALUES (105, '티보5',3);

 

넣고 실행한다면 다음과 같은 에러가 난다.

 

parent key를 찾을 수 없다고 한다.  부모가 과연 누구 일까?

제약 조건을 걸어 놓으면 의존 되는 곳과 의존 당하는 곳이 부모관계가 된다.

 

그래서 A table 은 부모 테이블 B table은 자식 테이블이 된다.

 

SELECT * FROM A;

 

넣고 A 테이블을 확인해 보자

 

 

INSERT INTO B VALUES (100, '티보0',1);
INSERT INTO B VALUES (101, '티보1',1);
INSERT INTO B VALUES (102, '티보2',1);
INSERT INTO B VALUES (103, '티보3',2);
INSERT INTO B VALUES (104, '티보4',2);
INSERT INTO B VALUES (105, '티보5',1);

SELECT * FROM B;

 

테이블 B에 데이터를 넣고 확인해보자

 

 

 

자 여기서 만약에 바나나를 지워보고 싶다!

 

 

DELETE FROM A WHERE NUM=2; -- A테이블에서 바나나를 지워보자

 

 

그리고 다시 SELECT * FROM A;을 해서 확인해보면 

 

 

SELECT * FROM B;

 

 

CASCADE 당해서 티보 3,4 가 사라졌다!!

자 이제 table B에서 사과를 띄어 보자!!

'사과'를 출력하려면? JOIN문을 사용해야한다!!

 

SELECT 
	BBB.NUM, --BBB 출신이라는 것을 정확히 적어줘야한다.
	BBB.NAME, AS B_NAME,-- 테이블별칭.컬럼명;
	BBB.AAA,
	AAA.NAME AS A_NAME -- A테이블 별칭.name --중복될 수 있기때문에 별칭을 추가해줘야
FROM		-- 별칭을 지어줘야한다.
	B BBB   --테이블명 테이블별칭
JOIN
	A AAA   --- A테이블과 조인하겠습니다.
ON			-- 뭐랑 뭐랑 연결해야하는지 추가로 기술
	BBB.AAA = AAA.NUM;

 

 

실습이 끝났으니 한번 지워보자

 

DROP TBALE A;

 

 

이때 위와 같은 에러가 난다

 

부모 테이블은 자식 테이블을 모두 삭제하기 전까지 삭제 할 수 없다.

 

 

 

 

2) 원본 데이터가 사라질 때, 연결 데이터도 유지되는 방법

 

그냥 동아리 null ???하면 안될까요??

 

가능 

 

ALTER TABLE B 
ADD CONSTRAINT FK_AAA 
FOREIGN KEY (AAA)
REFERENCES A(NUM)
ON DELETE SET NULL; -- CASCADE 대신

 

 이때, 주의 사항!!!

 table B에 not null을 붙이지 않으면 된다.

 

 

만약에 예를 들어 회원 탈퇴를 했는데 댓글 작성자가 '알수 없음'으로 변경되었다고 하자 

 

이때는

 

ALTER TABLE B 
ADD CONSTRAINT FK_AAA 
FOREIGN KEY (AAA)
REFERENCES A(NUM)
ON DELETE SET '알수없음';

 

 

SET NULL  가능 >>  해당 컬럼이 NOT NULL이면 안됨

SET '값'  가능 >> 해당 컬럼이  NOT NULL 일 때