[SQL] 참조키 : 기본키, 외래키

PRIMARY KEY와 FOREIGN KEY의 관계

• 참조되는 키 (PRIMARY KEY): 테이블의 각 행을 고유하게 식별하며, 다른 테이블에서 외래 키로 참조될 수 있습니다.
• 참조하는 키 (FOREIGN KEY): 다른 테이블의 PRIMARY KEY를 참조하여 두 테이블 간의 관계를 나타내며, 참조 무결성을 보장합니다.

 

PRIMARY KEY (기본 키)

• 역할: 한 테이블에서 각 행을 고유하게 식별하는 열로, 기본적으로 중복값과 NULL 값을 허용하지 않습니다.
• 특징:
• 용도: PRIMARY KEY는 테이블의 특정 행을 고유하게 식별하기 때문에, 이 열을 다른 테이블이 참조하는 기준 열로 사용할 수 있습니다.

CREATE TABLE dept_p (
    deptno INT(2) PRIMARY KEY,
    dname VARCHAR(14),
    loc VARCHAR(13)
);

• PRIMARY KEY 설정:
• 부모 테이블 (Parent Table): 다른 테이블이 참조하는 테이블입니다. 이 테이블은 보통 PRIMARY KEY나 UNIQUE 제약조건을 통해 고유 식별자 역할을 하는 열을 가지고 있습니다.

 

FOREIGN KEY (외래 키)

• 역할: 한 테이블이 다른 테이블의 PRIMARY KEY 또는 UNIQUE 열을 참조하도록 정의하는 열입니다. 이를 통해 테이블 간의 관계를 표현할 수 있습니다.
• 특징
• 용도: FOREIGN KEY는 한 테이블이 다른 테이블의 특정 열을 참조하도록 하여, 두 테이블 간의 연결 관계를 형성합니다.

CREATE TABLE emp_f (
    empno INT(4),
    ename VARCHAR(10),
    job VARCHAR(9),
    deptno INT(2),
    CONSTRAINT FOREIGN KEY (deptno) REFERENCES dept_p (deptno)
);

• deptno 열에 FOREIGN KEY 제약조건을 설정하여 dept_p 테이블의 deptno 열을 참조하도록 만듭니다.
• emp_f 테이블의 deptno 열에는 dept_p 테이블의 deptno 열에 있는 값만 삽입될 수 있습니다.
• 이 제약조건 덕분에, emp_f 테이블의 직원들은 반드시 dept_p에 존재하는 부서에 소속될 수 있도록 제한됩니다.
• 자식 테이블 (Child Table): 부모 테이블의 PRIMARY KEY 또는 UNIQUE 열을 참조하는 테이블입니다. 자식 테이블은 외래 키(FOREIGN KEY) 제약조건을 통해 부모 테이블의 특정 열을 참조하여, 부모 테이블에 존재하는 값만 가질 수 있습니다.

 

참조 제약조건과 데이터 무결성

• 참조 무결성( (Referential Integrity) : 부모 테이블에 존재하지 않는 값은 자식 테이블에 삽입할 수 없으며, 자식 테이블에서 참조 중인 부모 데이터를 수정하거나 삭제할 수 없습니다.
• 데이터 일관성 유지: 이러한 제약조건을 통해 데이터베이스는 여러 테이블 간의 데이터 일관성과 무결성을 유지할 수 있습니다.

동작	설명
존재하지 않는 부모 값 참조 방지	부모 테이블에 없는 값은 자식 테이블에서 참조할 수 없습니다.
부모 데이터 삭제 제한	자식 테이블에서 참조 중인 부모 데이터는 삭제할 수 없습니다.
부모 데이터 수정 제한	자식 테이블에서 참조 중인 부모 데이터는 수정할 수 없습니다.

---

예시

-- 자식 테이블(emp_f)에 존재하지 않는 부모 키로 데이터 삽입 시도 
INSERT INTO emp_f VALUES (1000, '홍길동', 'clerk', 10);  -- 에러          

--결과: 부모 테이블에 존재하지 않는 deptno 값은 자식 테이블에 삽입할 수 없습니다.참조 무결성 유지.

-- 부모 테이블에 데이터 삽입 후 자식 테이블에 참조 데이터 삽입
INSERT INTO dept_p VALUES (10, '영업부', '부여');         -- 성공
INSERT INTO emp_f VALUES (1000, '홍길동', 'clerk', 10);  -- 성공


--결과: 두 개의 테이블 간에 참조 관계가 성립되어 데이터 삽입이 성공 
--자식 테이블의 deptno 값이 부모 테이블의 deptno를 참조할 수 있게 되어 데이터 무결성이 보장됩니다.

--  부모 테이블에서 기본 키 값 수정 시도 
UPDATE dept_p SET deptno = 20 WHERE deptno = 10;         -- 에러

-- 결과: 부모 테이블의 기본 키 값을 수정하면 자식 테이블에서 참조할 수 없게 되므로 데이터 일관성이 깨지게 됩니다. 
-- 이를 방지하기 위해 기본 키 수정이 제한됩니다.

 -- 부모 테이블에서 참조 중인 데이터 삭제 시도 
DELETE FROM dept_p WHERE deptno = 10;          -- 에러

--결과: 자식 테이블에서 참조 중인 데이터는 부모 테이블에서 삭제할 수 없습니다. 참조 무결성이 유지됩니다.