DEVELOPER NOMAD

[오라클 FOREIGN KEY] (외래키, 참조키) 생성,삭제 본문

ORACLE(오라클)/ORACLE(오라클) 실습

[오라클 FOREIGN KEY] (외래키, 참조키) 생성,삭제

DEVELOPER NOMAD 2019. 1. 8. 10:38

//*****************************************

-- 참조(외래, FOREIGN)키

    


- 두 테이블의 데이터 간 연결을 설정하고 강제 적용하는 데 사용되는 열이다. 한 테이블의 기본 키 값이 있는 열을 다른 테이블에 추가하면 테이블 간 연결을 설정할 수 있다. 이 때 두 번째 테이블에 추가되는 열이 외래 키가 된다.

   

 - 부모 테이블이 먼저 생성된 후 자식 테이블(foreign key를 포함하는 테이블)이 생성되어야 한다.


    - FOREIGN KEY는 부모 테이블의 PRIMARY KEY, UNIQUE만 참조할 수 있고, 컬럼의 값과 일치하거나 NULL을 허용하는 경우 NULL 값이어야 한다.


    - 부모테이블의 컬럼명과 자식테이블의 컬럼명은 일치하지 않아도 되지만 자료형은 일치해야 한다.


    - 참조 무결성 제약조건에서 부모 테이블의 참조 키 컬럼에 존재하지 않는 값을 자식 테이블에 입력하면 오류가 발생한다.


    - ON DELETE SET NULL은 자식 테이블이 참조하는 부모 테이블의 값이 삭제되면 자식 테이블의 값을 NULL 값으로 변경시킨다.


    - ON DELETE CASCADE 옵션을 지정하면 부모 테이블의 부모데이터가 지워지면 자식 테이블의 자식데이터도 지워진다.


    - 자식 테이블이 존재하는 경우 부모 테이블은 제거가 불가능 하다.


    - DROP TABLE에서 CASCADE CONSTRAINTS 옵션을 부여 하면 자식 테이블이 존재 해도 부모 테이블이 제거가 가능하다.


CREATE TABLE 테이블명 (


   컬럼명 자료형

           :

  , CONSTRAINT 제약조건이름 FOREIGN KEY(컬럼명 [,컬럼명])

              REFERENCES 참조할테이블(컬럼명 [,컬럼명])

              [ON DELETE CASCADE]

);



//*****************************************

-- 참조키 삭제


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



//*****************************************

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


ALTER TABLE 테이블명 ADD

       CONSTRAINT 제약조건명 FOREIGN KEY(컬럼명[, 컬럼명])

                 REFERENCES  참조할테이블명(컬럼명[, 컬럼명]);



//*****************************************

-- 문제

   -- hr 사용자(스키마)의 다음 7개의 테이블의 구조를 분석한다.

   테이블명, 컬럼명, 자료형, 기본키, 참조키등의 제약조건등

   -- test라는 이름으로 사용자를 추가하고 test 사용자에

       hr 사용자의 테이블과 동일한 구조(자료형, 제약조건 등)로 7개의 테이블을 작성한다.

   -- 테이블 하나당 최소 3개 이상의 데이터(레코드)를 추가한다.

       

      COUNTRIES

      DEPARTMENTS

      EMPLOYEES

      JOBS

      JOB_HISTORY

      LOCATIONS

      REGIONS



-- 부와 자 관계의 모든 테이블 출력


 SELECT fk.owner, fk.constraint_name , pk.TABLE_NAME parent_table, 

        fk.table_name child_table

  FROM all_constraints fk, all_constraints pk 

 WHERE fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME 

   AND fk.CONSTRAINT_TYPE = 'R'

 ORDER BY fk.TABLE_NAME;



-- 『테이블명』을 참조하는 모든 테이블 목록 출력(자식 테이블 목록 출력)


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('테이블명')

 ORDER BY fk.TABLE_NAME;

 

 

-- 『테이블명』이 참조하고 있는 모든 테이블 목록 출력(부모 테이블 목록 출력)


SELECT table_name FROM USER_constraints

   WHERE constraint_name IN (

SELECT r_constraint_name 

   FROM user_constraints

   WHERE table_name = UPPER('테이블명')

         AND constraint_type = 'R'

);




Comments