0811_Oracle : 참조키(외래키, FOREIGN KEY)

참조키 (외래키, FOREIGN KEY)

• 테이블 간의 데이터의 일관성을 보장하기 위한 제약조건이다.
• 한 테이블의 PRIMARY KEY나 UNIQUE 컬럼을 다른 테이블에 추가하여 두 테이블 간 연결을 설정한다.
• 부모 테이블이 먼저 생성된 후 자식 테이블(FOREIGN KEY를 포함하는 테이블)이 생성되어야 한다.

 

예제를 통해 알아보자.

-- std : 부모 테이블
CREATE TABLE std (
    hak VARCHAR2(30) PRIMARY KEY,
    name VARCHAR2(30) NOT NULL,
    email VARCHAR2(50) NOT NULL,
    tel VARCHAR2(30) NOT NULL
);

-- std_score : 자식 테이블
    -- 부모의 PRIMARY KEY 또는 UNIQUE 만 참조 가능
CREATE TABLE std_score(
    num NUMBER PRIMARY KEY,
    hak VARCHAR2(30) NOT NULL,
    gubun NUMBER(1) NOT NULL,
    java NUMBER(3) NOT NULL,
    oracle NUMBER(3) NOT NULL,
    CONSTRAINT fk_stdscore_hak FOREIGN KEY ( hak )
        REFERENCES std(hak)
);

SELECT * FROM USER_CONSTRAINTS WHERE table_name = 'STD';
SELECT * FROM USER_CONSTRAINTS WHERE table_name = 'STD_SCORE';
    -- P : 기본키, R : 참조키
SELECT * FROM USER_CONS_COLUMNS WHERE table_name = 'STD';
SELECT * FROM USER_CONS_COLUMNS WHERE table_name = 'STD_SCORE';

CREATE TABLE 문에서 FOREIGN KEY를 추가할 때는 FOREIGN KEY (컬럼) REFERENCES 참조하는테이블(참조하는컬럼) 을 통해 추가한다.

기본키는 CONSTRAINT TYPE 이 P로 되어있고 참조키는 R로 되어있다.

데이터를 추가해보자.

INSERT INTO std_score(num, hak, gubun, java, oracle) VALUES (1, '11', 1, 100, 90);
    -- 에러 ORA-02291 : 부모 테이블에 존재하지 않는 hak은 추가 불가
    
INSERT INTO std(hak, name, email, tel) VALUES ('11', 'aa', 'aa', '11');
INSERT INTO std(hak, name, email, tel) VALUES ('22', 'bb', 'bb', '22');
INSERT INTO std(hak, name, email, tel) VALUES ('33', 'cc', 'cc', '33');

INSERT INTO std_score(num, hak, gubun, java, oracle) VALUES (1, '11', 1, 100, 90);
INSERT INTO std_score(num, hak, gubun, java, oracle) VALUES (2, '11', 2, 90, 90);

INSERT INTO std_score(num, hak, gubun, java, oracle) VALUES (3, '33', 1, 90, 90);
COMMIT;

SELECT * FROM std;
SELECT * FROM std_score;

부모테이블에 아직 아무데이터도 있지 않은 상태이다. 이 상태에서 자식테이블에 값을 추가하면 에러(ORA-02291)가 발생한다. 

참조하는 키가 먼저 부모테이블에 존재해야 자식테이블에 값도 추가할 수 있다.

먼저 부모 테이블에 데이터를 입력하고 자식테이블에도 입력을 하면 잘 들어가는 것을 확인할 수 있다.

 

입력된 데이터의 값을 수정하거나 삭제해보자.

DELETE FROM std WHERE hak = '11';
    -- 에러 ORA-02292 : 참조하는 레코드가 있음.

UPDATE std SET hak = '13' WHERE hak = '11';
    -- 에러 ORA-02292 : 참조하는 레코드가 있음

UPDATE std SET hak = '23' WHERE hak = '22';
    -- 가능 : 참조하는 레코드가 없음
SELECT * FROM std;

DELETE FROM std WHERE hak = '23';
    -- 가능

부모테이블에 있는 PRIMARY KEY인 HAK을 바꿔보려 했으나 이 값을 참조키로 자식테이블에서 데이터를 추가했기 때문에 오류가 발생한다.(ORA-02292) 하지만 HAK 22의 경우 이 값으로 하여금 자식 테이블이 가지고 있는 값이 아무 것도 없기 때문에 23으로 변경할 수 있었다. 또 삭제도 가능했다.

 

 

자식이 부모테이블에있는 컬럼을 참조키로 가지고 있는 경우, 부모 테이블은 여태까지 했던 방식으로는 불가하다.

DROP TABLE std PURGE;
    -- 에러 ORA-02449 : 참조하는 테이블이 있으면 삭제 불가
    -- 모든 자식 테이블을 먼저 삭제하고 삭제해야 함

에러 (ORA - 02449)가 발생한다. 

강제로 삭제하는 방법이 존재한다. 참조키를 삭제해 부와 자의 관계를 없앤다.

-- 강제로 삭제. 참조키가 삭제 됨(부와 자의 관계가 없어짐)
DROP TABLE std CASCADE CONSTRAINTS PURGE;
SELECT * FROM tab;

SELECT * FROM USER_CONSTRAINTS WHERE table_name = 'STD_SCORE';
    -- R(참조키)이 제거됨

DROP TABLE std_score PURGE;

명령어

DROP TABLE std CASCADE CONSTRAINTS PURGE;

통해서 부모테이블을 강제로 삭제했다. 그 후 테이블 제약조건을 확인해보니 원래 HAK은 CONSTRAINT_TYPE 이 FOREIGN KEY 였기 때문에 F 였으나 이제는 NOT NULL의 CHECK조건을 가지고 있으므로 C로 바뀌어있는 것을 확인할 수 있다. 참조키를 가지고 있지 않기 때문에 평소 했던 방법으로 테이블을 삭제할 수 있다.

DROP TABLE std_score PURGE;

 

참조하는 컬럼과 참조당하는 컬럼은 타입과 크기가 같아야한다. 하지만 컬럼명은 다를 수 있다.

 

예시를 통해 알아보자.

-- std : 부모 테이블
CREATE TABLE std (
    hak VARCHAR2(30) PRIMARY KEY,
    name VARCHAR2(30) NOT NULL,
    email VARCHAR2(50) NOT NULL,
    tel VARCHAR2(30) NOT NULL
);

-- std_score : 자식 테이블
CREATE TABLE std_score(
    num NUMBER PRIMARY KEY,
    shak VARCHAR2(30),
    gubun NUMBER(1) NOT NULL,
    java NUMBER(3) NOT NULL,
    oracle NUMBER(3) NOT NULL,
    FOREIGN KEY ( shak )
        REFERENCES std(hak)
);

SELECT * FROM USER_CONSTRAINTS WHERE table_name = 'STD';
SELECT * FROM USER_CONSTRAINTS WHERE table_name = 'STD_SCORE';
SELECT * FROM USER_CONS_COLUMNS WHERE table_name = 'STD';
SELECT * FROM USER_CONS_COLUMNS WHERE table_name = 'STD_SCORE';

테이블에 데이터를 넣어보자.

INSERT INTO std_score(num, shak, gubun, java, oracle) VALUES (1, NULL , 1, 100, 90);
    -- 가능. 부모에 존재하지 않아도 참조키가 NULL이 가능하므로 NULL은 추가 가능
INSERT INTO std_score(num, shak, gubun, java, oracle) VALUES (2, '11', 1, 80, 90);
    -- 에러 ORA-02291 : 부모의 hak에 11이 없음.

자식테이블에 외래키에 NOT NULL 조건을 붙이지 않았으므로 NULL이 가능하다.

하지만 자식테이블의 참조키에 NULL 이외의 값으로는 데이터를 추가할 수 없다. 부모테이블에 값이 있어야 추가가 가능하다.