참조키 - 2024-09-12

수호천사임다·2024년 9월 12일

오라클

목록 보기

30/53

개요

아버지는 유일성을 보장 받았기 때문에 참조키 가능

참조 키는 테이블 간의 데이터의 일관성을 보장하기 위한 제약조건이다.
한 테이블의 PRIMARY KEY나 UNIQUE 컬럼을 다른 테이블에 추가하여 두 테이블 간 연결을 설정 한다. 이 때 두 번째 테이블에 추가되는 컬럼이 외래 키(FOREIGN KEY)가 된다.
부모 테이블이 먼저 생성된 후 자식 테이블(FOREIGN KEY를 포함하는 테이블)이 생성되어야 한다.
FOREIGN KEY는 부모 테이블의 PRIMARY KEY, UNIQUE만 참조할 수 있고, 컬럼의 값과 일치하거나 NULL을 허용하는 경우 NULL 값이어야 한다.
부모테이블의 컬럼명과 자식테이블의 컬럼명은 일치하지 않아도 되지만 자료형은 일치해야 한다.(자료형과 폼만 같으면 된다.)
부모 테이블의 참조 키 컬럼에 존재하지 않는 값을 자식 테이블에 입력하면 오류가 발생한다.
외래 키에 ON DELETE SET NULL 또는 ON DELETE CASCADE 옵션을 지정하지 않은 경우, 자식 테이블에 참조하는 부모 테이블 값이 존재하면 부모 테이블의 참조 행을 삭제하거나 참조 값을 다른 값으로 수정 할 수 없다.
자식 테이블이 존재하는 경우 부모 테이블은 제거가 불가능 하다.
아버지가 지워지면 자식도 삭제된다.
컬럼을 수정할거면 일단 부자의 관계를 끊고나서 일일이 수정을 한 다음 다시 관계를 맺는다.
DROP TABLE에서 CASCADE CONSTRAINTS 옵션을 부여 하면 자식 테이블이 존재 해도 부모 테이블이 제거가 가능하다.

CREATE TABLE test1(
    code VARCHAR2(30) PRIMARY KEY,
    subject VARCHAR2(50) NOT NULL
);

CREATE TABLE ex1 (
    id VARCHAR2(30) PRIMARY KEY,
    name VARCHAR2(30) NOT NULL,
    code VARCHAR2(30),
    CONSTRAINT fx_ex1_code FOREIGN KEY(code) REFERENCES test1(code)
);

INSERT INTO ex1(id, name, code) VALUES (1, 'a', 'x100');
-- ORA-02291: 무결성 제약조건(SKY.FX_EX1_CODE)이 위배되었습니다- 부모 키가 없습니다
-- 부모테이블에 존재하지 않는 code는 추가 불가 

INSERT INTO ex1(id, name, code) VALUES (1, 'a', NULL);
-- 참조키가 null을 허용 했으므로 가능 

-- 부모부터 data를 넣어야 자식도 data를 넣을 수 있다.
-- test1(부모) 테이블에 데이터 추가

INSERT INTO test1(code, subject) VALUES('x100', 'aaa');
INSERT INTO test1(code, subject) VALUES('x101', 'bbb');
INSERT INTO test1(code, subject) VALUES('x102', 'ccc');

INSERT INTO ex1(id, name, code) VALUES ('2', 'b', 'x100');
INSERT INTO ex1(id, name, code) VALUES ('3', 'c', 'x102');
INSERT INTO ex1(id, name, code) VALUES ('4', 'd', 'x100');


-- 참조하는 컬럼과 참조당하는 컬럼은 타입은 일치해야 하지만 컬럼명은 다를 수 있다.
CREATE TABLE test2 (
    num NUMBER PRIMARY KEY,
    subject VARCHAR2(50) NOT NULL
);

CREATE TABLE exam2(
    id VARCHAR2(30) PRIMARY KEY,
    name VARCHAR2(30) NOT NULL,
    snum NUMBER NOT NULL,
    CONSTRAINT fk_ex2_snum FOREIGN KEY(snum) REFERENCES test2(num)
);

SELECT * FROM user_constraints WHERE table_name = 'TEST2';
SELECT * FROM user_constraints WHERE table_name = 'EXAM2';

SELECT * FROM user_cons_columns WHERE table_name = 'TEST2';
SELECT * FROM user_cons_columns WHERE table_name = 'EXAM2';

DROP TABLE exam2 PURGE;
DROP TABLE test2 PURGE;

식별관계 - 기본키이면서 참조키

관계 -> 1 : 1
기본키가 2개이면 식별관계에서도 1 : n 이 될 수도 있다.

CREATE TABLE member2(
    id VARCHAR2(50),
    birth DATE,
    tel VARCHAR2(30),
    CONSTRAINT pk_member2_id PRIMARY KEY(id),
    CONSTRAINT fk_member2_id FOREIGN KEY(id)
    REFERENCES member1(id)
);

비식별관계 - 단순한 참조키

CREATE TABLE guest(
    num NUMBER PRIMARY KEY,
    id VARCHAR2(50) NOT NULL,
    content VARCHAR2(4000) NOT NULL,
    reg_date DATE DEFAULT SYSDATE,
    FOREIGN KEY (id) REFERENCES member1(id) 
);

CREATE TABLE note(
    num NUMBER PRIMARY KEY,
    content VARCHAR2(4000) NOT NULL,
    sendld VARCHAR2(50) NOT NULL,
    receiveld VARCHAR2(50) NOT NULL,
    FOREIGN KEY(sendld) REFERENCES member1(id),
    FOREIGN KEY(receiveld) REFERENCES member1(id)
); -- member1을 2번 참조 이름은 같을 필요가 없다. 

-- member1 테이블의 모든 자식 테이블의 목록 출력
SELECT fk.owner, fk.constraint_name , fk.table_name 
        FROM all_constraints fk, all_constraints pk 
        WHERE fk.r_constraint_name = pk.constraint_name 
                   AND fk.constraint_type = 'R' 
                   AND pk.table_name = UPPER('member1')
        ORDER BY fk.table_name;
        
-- guestLIKE테이블의 모든 부모 테이블 및 부모 컬럼 출력 

SELECT fk.constraint_name, fk.table_name child_table, fc.column_name child_column,
      pk.table_name parent_table, pc.column_name parent_column
      FROM all_constraints fk, all_constraints pk, all_cons_columns fc, all_cons_columns pc
      WHERE fk.r_constraint_name = pk.constraint_name
         AND fk.constraint_name = fc.constraint_name
         AND pk.constraint_name = pc.constraint_name
         AND fk.constraint_type = 'R'
         AND pk.constraint_type = 'P'
         AND fk.table_name = UPPER('gusetLike');

두개의 컬럼으로 기본키 설정

기본키가 2개이면 식별관계에서도 1 : n 이 될 수도 있다.

CREATE TABLE guestLike(
    num NUMBER,
    id VARCHAR2(50),
    PRIMARY KEY(num, id),
    FOREIGN KEY (num) REFERENCES guest(num),
    FOREIGN KEY (id) REFERENCES member1(id) 
);

-- 중복으로 사용할 수 있냐 없냐 .

존재하는 테이블에 참조키 추가

테이블 생성 후 FORELGN KEY를 추가할 필요가 있을 때는 ALTER 명령어를 이용하여 추가한다.
형식

ALTER TABLE table_name
	ADD [ CONSTRAINT constraint_name ] FOREIGN KEY (column [, column ]...)
		REFERENCES [ schema. ] object [ (column [, column ]...) ]
		[ON DELETE { CASCADE | SET NULL } ];

CREATE TABLE test1(
    code VARCHAR2(30) NOT NULL,
    num NUMBER NOT NULL,
    subject VARCHAR2(100) NOT NULL,
    PRIMARY KEY (code, num)
);

CREATE TABLE test2 (
    pnum NUMBER PRIMARY KEY,
    name VARCHAR2(500) NOT NULL,
    code VARCHAR2(30) NOT NULL,
    num NUMBER NOT NULL
);

ALTER TABLE test2 ADD FOREIGN KEY (code, num) REFERENCES test1(code, num);

FOREIGN KEY 제약 조건 삭제

FOREIGN KEY 제약 조건을 제거 할 필요가 있을 때는 ALTER 명령어를 이용하여 삭제한다.
삭제하기 전 우선 제약 조건을 확인한다.
형식

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

SELECT * FROM user_constraints WHERE table_name = 'TEST2';
SELECT * FROM user_cons_columns WHERE table_name = 'TEST2'; 
-- 제약 조건을 확인하여 CONSTRAINT_TYPE에서 참조키를 찾는다.
ALTER TABLE test2 DROP CONSTRAINT SYS_C008571;