참조키(외래키, FORIGN KEY)

-- 참조키(외래키, FORIGN KEY)

  -- 두 테이블간으로 연결을 설정하고 강제 적용( 개체간의 무결성 유지)

  -- 부모테이블이 먼저 만들어지고 자식테이블을 작성

  -- FORIGN KEY는 부모의 PRIMARY KEY, UNIQUE만 설정 할 수 있다.!!!!!!!!!!!!!!!!

  -- 부모테이블의 컬러명과 자식 테이블의 컬럼명이 달라도 관계를 설정 할 수 있다.

  -- 참조제약조건이 설정된 경우 부모테이블에 없는 참조키값은 자식테이블에 추가하거나 수정 할 수 없다.

  -- 참조제약조건이 설정된 자식테이블에 존재하는 값을 부모테이블에서 제거할 수 없다.

  -- ON DELETE CASCADE 옵션을 부여하면 부모쪽이 지워지면 자식도 자동으로 삭제된다.

  -- ON DELETE SET NULL 옵션을 부여하면 부모쪽이 지원지면 자식은 NULL이 된다.

  -- 관계가 설정된 경우 자식테이블을 먼저 삭제하고 부모테이블을 삭제해야 한다.

  -- DROP에서 CASCADE CONSTRAINTS 옵션을 부여하여 부모테이블을 삭제하면 부모는 삭제가되고 관계설정도 제거가 된다.

  

  -- 부모 테이블 작성

  CREATE TABLE ex1(

    id VARCHAR2(10)

    ,name VARCHAR2(20)

    ,CONSTRAINT pk_ex1_id PRIMARY KEY (id)

  );

  

  -- 자식 테이블 작성( 1:n의 관계 설정 )

  -- CONSTRAINT 제약이름 FOREIGN KEY (자식컬럼명) REFERENCES 부모테이블(부모컬럼명)

  CREATE TABLE ex2(

    num NUMBER

    ,id VARCHAR2(10)

    ,tel VARCHAR2(20)

    ,CONSTRAINT pk_ex2_num PRIMARY KEY (num)

    ,CONSTRAINT fk_ex2_id FOREIGN KEY (id) REFERENCES ex1(id)

  );

  

  -- 자식테이블에 먼저 데이터 추가

  INSERT INTO ex2(num, id, tel) VALUES (1,'11','000');  -- 부모에 없으므로 추가불가

  

  -- 부모먼저 데이터 추가

  INSERT INTO ex1(id,name) VALUES ('11','a');

  --자식에 데이터 추가

  INSERT INTO ex2(num,id,tel) VALUES (1,'11','000');

  INSERT INTO ex2(num,id,tel) VALUES (2,'11','111');

 COMMIT;

 SELECT * FROM ex1;

 SELECT * FROM ex2;

    

  -- 자식테이블에 추가

  INSERT INTO ex2(num, id, tel) VALUES (1,'22','000');  -- 부모에 없으므로 추가불가

  

  -- 부모먼저

  INSERT INTO ex1(id,name) VALUES ('11','a');

  INSERT INTO ex1(id,name) VALUES ('22','a');

  --자식추가

  INSERT INTO ex2(num,id,tel) VALUES (1,'11','000');

  INSERT INTO ex2(num,id,tel) VALUES (2,'11','111');

 COMMIT;

 SELECT*FROM ex1;

 SELECT*FROM ex2;

    

  --부모의 11삭제

  DELETE FROM ex1 WHERE id='11'; -- 에러:자식이 있으므로 (자식을 다 지우고 지워야함)

  DELETE FROM ex1 WHERE id='22'; -- 가능:자식이 없으므로

  COMMIT;

  SELECT * FROM ex1;

  SELECT * FROM ex2;

  

  -- 부모 테이블 삭제

  DROP TABLE_ex1 PURGE; -- 에러:자식이 있으므로 (자식을 먼저  지워야함)

  

  DROP TABLE ex1 CASCADE CONSTRAINT PURGE; --강제삭제

  

  SELECT * FROM user_constrains;

  

  DROP TABLE ex2 purge;

  

    -- 부모 테이블 작성

  CREATE TABLE ex1(

    id VARCHAR2(10)

    ,name VARCHAR2(20)

    ,CONSTRAINT pk_ex1_id PRIMARY KEY (id)

  );

  

  -- 자식 테이블 작성( 1:n의 관계 설정 )

  CREATE TABLE ex2(

    num NUMBER

    ,id VARCHAR2(10)

    ,tel VARCHAR2(20)

    ,CONSTRAINT pk_ex2_num PRIMARY KEY (num)

    ,CONSTRAINT fk_ex2_id FOREIGN KEY (id) REFERENCES ex1(id)

    ON DELETE CASCADE --위와 달리 이줄을 추가해주었다. 이부분 때문에 자식과 연결된 부모를 한번에 지울 수 있다.

  );

  

  -- 부모먼저

  INSERT INTO ex1(id,name) VALUES ('11','a');

  --자식추가

  INSERT INTO ex2(num,id,tel) VALUES (1,'11','000');

  INSERT INTO ex2(num,id,tel) VALUES (2,'11','111');

 COMMIT;

 SELECT * FROM ex1;

 SELECT * FROM ex2;

  

  DELETE FROM ex1 WHERE id='11';  

  --=> ON DELETE CASCADE 때문에 부모와 자식을 한번에 지우지만 그만큼 위험하다.

   COMMIT;

 SELECT * FROM ex1;

 SELECT * FROM ex2;

 

  INSERT INTO ex1(id,name) VALUES ('11','a');

  INSERT INTO ex1(id,name) VALUES ('22','a');

  INSERT INTO ex2(num,id,tel) VALUES (1,'11','000');

  INSERT INTO ex2(num,id,tel) VALUES (2,'11','111');

  COMMIT;

  SELECT * FROM ex1;

  SELECT * FROM ex2;

  

  UPDATE ex1 SET id='33' WHERE id ='11'; --에러 자식이 있으므로 수정 불가

  UPDATE ex1 SET id='33' WHERE id ='22'; --가능 : 자식이 없으므로

  COMMIT;

  UPDATE ex2 SET id='22' WHERE num ='2'; -- 에러 : 부모가 없으므로

  UPDATE ex2 SET id='33' WHERE num ='2'; -- 가능 : 부모가 있으므로

  COMMIT;

  

  SELECT * FROM ex1;

  SELECT * FROM ex2;

  ------------------------------------------------------------------------------------

  --예제

  CREATE TABLE demo1(

 num NUMBER(4) 

 ,hak VARCHAR2(5) 

 ,name VARCHAR2(10)

 ,birth DATE

 ,gender VARCHAR2(4)

 ,CONSTRAINT pk_demo1_num primary key(num,hak)

);

CREATE TABLE demo2(

 no NUMBER(4) 

 ,hak VARCHAR2(5) 

 ,birth DATE

 ,email VARCHAR2(20)

 ,CONSTRAINT pk_demo2_no primary key(no)

 ,CONSTRAINT un_demo2_email UNIQUE(email)

);

CREATE TABLE demo3(

 id VARCHAR2(20)

 ,no NUMBER(4) 

 ,hak VARCHAR2(5)

 ,tel VARCHAR2(12)

 ,email VARCHAR2(20)

 ,CONSTRAINT pk_demo3_id PRIMARY KEY(id)

 ,CONSTRAINT fk_demo3_no FOREIGN KEY(no,hak) REFERENCES demo1(num,hak)

   ---테이블레벨로 만들지 않아도 되지만 기본키가 2개 이상일 때는 이렇게 만들어준다

 ,CONSTRAINT fk_demo3_email FOREIGN KEY(email) REFERENCES demo2(email)

);

COMMIT;

 SELECT * FROM demo1;

 SELECT * FROM demo2;

 SELECT * FROM demo3;

   

   SELECT * FROM tab;

  DROP TABLE SCORE CASCADE CONSTRAINT PURGE;

------------------------------------------------------------------------------------------------