[Oracle] 오라클 - 참조 키(외래 키, FOREIGN KEY) / 참조 키 관계

[Oracle]
오라클 - 참조 키(외래 키, FOREIGN KEY) / 참조 키 관계

---

✔️ 참조 키

: 쉽게 상속? 부모 자식 관계를 연결짓는 연결다리 .... 

: 1:1 / 1:0 관계 EX) 게시판 등 => 1: N , 1:0 관계

: 테이블 간의 데이터의 일관성을 지키기 위해 사용하는 제약 조건이다. 

: 외래 키는 부모 테이블의 기본키/유니크 만 참조할 수 있음 ❤️

: 부모테이블의 컬럼과 자식테이블 컬럼의 이름 일치 안해도 되지만 자료형은 일치해야함!

❤️ 참조당하는 컬럼가 참조하는 컬럼은 타입과 크기가 일치해야한다. (이름 상관없음)

❌ 부모 테이블 컬럼에 없는 값을 자식 테이블 컬럼에 넣을 수 없다.

 

 

 

1) 테이블 생성과 동시에 FOREIGN KEY 제약 조건 설정

-- 기본형식

CREATE TABLE 테이블명 (
     컬럼  데이터타입  [ CONSTRAINT 제약조건명 ] 
                REFERENCES 참조할테이블명(컬럼)
                [ON DELETE { CASCADE | SET NULL } ]
                                 :
);

 

 

2) 존재하는 테이블에 FOREIGN KEY 제약 조건 설정

-- 기본형식

ALTER TABLE 테이블명
    ADD [ CONSTRAINT 제약조건명 ] FOREIGN KEY( 컬럼 [,컬럼] )
         REFERENCES  참조할테이블명(컬럼 [,컬럼]);

 

 

3) FOREIGN KEY 제약 조건 삭제

-- 기본형식

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;

 

 

 

🔒문제1

test1 : 부모 테이블
	CREATE TABLE test1 (
		code VARCHAR2(30) PRIMARY KEY,
		subject VARCHAR2(100) NOT NULL;
	);
    
--exam1 : test1 테이블의 참조 테이블 (자식 테이블)
    	--부모 테이블의 기본키, unique 만 참조 가능
    CREATE TABLE exam1 (
        num NUMBER PRIMARY KEY,
        name VARCHAR2(50) NOT NULL,
        code VARCHAR2(30),
        content VARCHAR2(4000) NOT NULL,
        FOREIGN KEY (code) REFERENCES test1(code)
    );

INSERT INTO exam1(num, name, code, content) VALUES (1, 'a', 'x100', 'a');
            -- 에러 . : 부모 테이블에 없는 코드는 추가 불가 .
            
INSERT INTO exam1(num, name, code, content) VALUES (1, 'a', null, 'a');
            -- 참조키(code) 가 not null 아니라서 데이터 들어감.

💡 부모 테이블에 없는 코드는 추가 불가
참조키가 NOT NULL 이 아니라서 데이터 들어갈 수 있다.

 

 

 

🔒문제2

INSERT INTO test1(code, subject) VALUES ('a1', 'aa');
          INSERT INTO test1(code, subject) VALUES ('a2', 'bb');
          INSERT INTO test1(code, subject) VALUES ('a3', 'cc');
          INSERT INTO test1(code, subject) VALUES ('a4', 'dd');
    
        --exam1 에 데이터 추가
        INSERT INTO exam1(num, name, code, content) VALUES (2, 'b', 'a2', 'b');
        INSERT INTO exam1(num, name, code, content) VALUES (3, 'a', 'a1', 'xx');
        INSERT INTO exam1(num, name, code, content) VALUES (4, 'c', 'a2', 'hh');

DELETE FROM test1 WHERE code = 'a1';    
   -- 에러 : exam1 자식 테이블에 a1 인 자료가 있기 때문에 안지워짐

💡 참조한 테이블, 즉 자식 테이블에 자료가 있으면 부모 테이블의 값 지울 수 없다. 

삭제 뿐만 아니라 변경도 마찬가지. 자식 테이블에 자료 있으면 변경 불가

 

⭐ 죽어도 강제로 삭제하고 싶을 때,

DROP TABLE test1 CASCADE CONSTRAINTS PURGE;
	-- 부자 관계 끊어버림

 

 

 

✔️ 참조 키로 보는 관계

-- 테이블 만들기 
CREATE TABLE member1 (
        id VARCHAR2(30) PRIMARY KEY,
        pwd VARCHAR2(100) NOT NULL,
        name VARCHAR2(30) NOT NULL
   );

 

 

-- 1대 1 관계 (식별관계 : 기본키이자 참조키인 경우! 혼자 다하네)

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

💡member1 테이블의 기본키를 참조 키이자 기본키로 설정
= member2 테이블 1:1 관계 성립

 

 

-- 1대 다 관계 (단순한 참조. 비식별관계)

ex) 한 회원이 여러 번 게시글을 등록하는 경우

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

💡 member1 테이블의 기본키를 참조 키로 설정
(기본키는 설정 안함) 

 

 

-- 1 대 다관계 (비식별관계)

ex) 동일 컬럼을 두 번 참조 : 쪽지같은 경우

CREATE TABLE note(
        num NUMBER PRIMARY KEY,
        sendId VARCHAR2(30) NOT NULL,
        receiveId VARCHAR2(30) NOT NULL,
        content VARCHAR2(4000) NOT NULL,
        FOREIGN KEY(sendId) REFERENCES member1(id),
        FOREIGN KEY(receiveId) REFERENCES member1(id)
    );

💡 member1 의 기본키를 두번이나 참조 키로 가져왔다.

 

 

-- 자기 자신 참조

CREATE TABLE category (
        num NUMBER PRIMARY KEY,
        subject VARCHAR2(100) NOT NULL,
        parentNum NUMBER,
        FOREIGN KEY(parentNum) REFERENCES category(num)
    );

 

 

 

✔️ ON DELETE CASCADE

: 부모 테이블의 레코드가 삭제되면 자식 테이블 레코드도 삭제

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

CREATE TABLE exam1(
		num NUMBER PRIMARY KEY,
		name VARCHAR2(50) NOT NULL,
		code VARCHAR2(30),	
		content VARCHAR2(4000) NOT NULL,
		FOREIGN KEY(code) REFERENCES test1(code) ON DELETE CASCADE
	);

 

 

 

✔️ 제약 조건 활성화 및 비활성화

1) 존재하는 테이블의 제약 조건 비 활성화

ALTER TABLE 테이블명 DISABLE CONSTRAINT 제약조건명 [ CASCADE ];

💡 CASCADE : 부모 테이블의 제약조건을 비활성화하면, 자식 테이블의 제약조건도 비활성화

 

 

-- 비활성화 확인

SELECT * FROM user_constraints WHERE table_name = 'EXAM1';
			-- DISABLED 되어있다.

 

 

2) 테이블의 제약 조건 활성화

ALTER TABLE 테이블명 ENABLE CONSTRAINT 제약조건명;

💡 만약, 제약 조건을 활성화하는데 제약 조건을 위반하는 데이터가 있다!! 하면 활성화 못한다.