컴퓨토피아

FOREIGN KEY(참조키)

참조 되어지는 테이블에는 기본키혹은 유니크가 설정이 되어있어야하며
참조 하려는 테이블에는 참조키가 설정이 되어있어야 한다.

부모 테이블의 기본키 컬럼이 자식 테이블에 컬럼을 따로 만들지 않아도 자동으로 넘어감..

<테이블명 : 부서>                              <테이블명 : 입사지원>
------------------                        -------------------------
부서번호 |  부서명                             번호   |   이름     |  부서번호                           
------------------                        -------------------------
10              전산실                               1          이순신         10         
20              총무부                               2          홍길동         30
30              영업부                               3          김유신         30

CREATE TABLE 부서
(부서번호 INT CONSTRAINT PK_부서_부서번호 PRIMARY KEY,  //기본키
  부서명 CHAR(20))

INSERT INTO 부서 VALUES(10,'전산실')
INSERT INTO 부서 VALUES(20,'총무부')
INSERT INTO 부서 VALUES(30,'영업부')

CREATE TABLE 입사지원
(번호 INT,
 이름 CHAR(10),
 부서번호 INT CONSTRAINT FK_입사지원_부서번호
              FOREIGN KEY REFERENCES 부서(부서번호)) //참조키

INSERT INTO 입사지원 VALUES(1,'이순신', 10)
INSERT INTO 입사지원 VALUES(2,'홍길동',30)
INSERT INTO 입사지원 VALUES(3,'김유신', 30)
INSERT INTO 입사지원 VALUES(4,'허준', 40)

//마지막 쿼리 에러.. 참조하는 부서번호에 40번이 없기때문에...

//참조하는 것이 있으면, 수정 및 삭제할수 없다.

UPDATE 부서
SET 부서번호=100
WHERE 부서번호=10

-->에러!!

//참조하는 것이 없기 때문에 수정및 삭제가 가능하다.

UPDATE 부서
SET 부서번호=200
WHERE 부서번호 =20

//참조하고 있는 것을 강제로 수정 및 삭제하려면 (CASECADE 사용)

① 입사지원의 참조키 제약을 지우고 수정 및 삭제를 한다.

ALTER TABLE 입사지원
DROP CONSTRAINT FK_입사지원_부서번호

② 처음에 FOREIGN 제약에 조건을 넣어서 테이블을 생성, 혹은 다음과 같이 수정해보자

ALTER TABLE 입사지원
ADD CONSTRAINT FK_입사지원_부서번호
            FOREIGN KEY(부서번호) REFERENCES 부서(부서번호)
             ON UPDATE CASCADE
             ON DELETE NO ACTION

※알아두기

    CASCADE : 연속동작, 참조하고 있다하더라도 참조당하는 것이 바뀌면 참조하는 것도 바뀜
    NO ACTION : 쓰지 않아도 디폴트값으로 가지고 있다. 동작하지 않는다는 것.

1. Primary key

(1) 제약조건 생성

CREATE TABLE sawon
(sano NUMBER(4)          constraint sawon_sano_nn     NOT NULL

,constraint sawon_sano_pk primary key(sano)
);

ALTER TABLE buseo
add constraint buseo_buno_pk primary key(buno);

2. Foreugn key

(1) 제약조건 생성

CREATE TABLE sawon
(,buno NUMBER(2)
,constraint sawon_buno_fk foreign key(buno) references buseo(buno)
 --on delete cascade 1:1에서만 쓴다. <on update cascade는 존재하지 않는다.>
);

ALTER TABLE sawon
add constraint sawon_buno_fk foreign key(buno) references buseo(buno);

(2) 제약조건 삭제

ALTER TABLE [테이블명]
drop constraint [제약조건명];

만약, 다른 테이블을 참조중이라 삭제가 안된다면

SELECT *
FROM user_constraints
WHERE constraint_type='R' AND r_constraint_name='BUSEO_BONO_PK';

우선 검색을 한 후 포린키 제약을 삭제 후 삭제 하면 지워진다.

아니면 casecade를 붙여 주면 자동으로 지워진다.

ALTER TABLE buseo
drop constraint buseo_buno_pk cascade;

3. Unique key

(1) 제약조건 생성

jubun 값이 유니크 속성을 주었지만 NOT NULL 조건을 안주었기 때문에
여러개의 NULL 값이 들어 갈 수 있다.
MS-SQL, MySQL은 2번째부터 NULL 값이 안들어 간다.

CREATE TABLE sawon
(jubun VARCHAR2(13)     constraint sawon_jubun_nn    NOT NULL
,constraint sawon_jubun_uk unique(jubun)   

);

4. Check 제약

(1) 제약조건 생성

CREATE TABLE sawon
(jik VARCHAR2(10)          DEFAULT '사원'
,constraint sawon_jik_ch CHECK(jik IN('사장','부장','과장','대리','사원'))
);

5. Not null 제약

(1) 제약조건 생성

MS-SQL에서는 룰제약 대신 Not null 제약이다.

그래서 일반적으로 테이블을 만들때 Not null 제약을 줄때 제약명을 적어줘야

유지보수하는데 도움이 된다.

CREATE TABLE sawon
(sano NUMBER(4)          constraint sawon_sano_nn     NOT NULL

);

ALTER TABLE sawon
modify constraint saname sawon_saname_nn NOT NULL; --보통은 add지만 NOT NULL 제약은 modify다.

ALTER TABLE sawon
add email VARCHAR(20) DEFAULT ' '
constraint sawon_email_nn NOT NULL;

6. 전체 예문

CREATE TABLE sawon
(sano NUMBER(4)    constraint sawon_sano_nn NOT NULL
,saname VARCHAR2(10) constraint sawon_saname_nn NOT NULL
,jubun VARCHAR2(13) constraint sawon_jubun_nn NOT NULL
,jik VARCHAR2(10) DEFAULT '사원'
,sal NUMBER(4)
,comm NUMBER(4)
,hiredate DATE DEFAULT SYSDATE
,mgr NUMBER(4)
,buno NUMBER(2)
,constraint sawon_sano_pk primary key(sano)
,constraint sawon_jubun_uk unique(jubun)
,constraint sawon_jubun_ch CHECK(TO_NUMBER(jubun)*0=0 AND LENGTH(jubun) =13)
,constraint sawon_jik_ch CHECK(jik IN('사장','부장','과장','대리','사원'))
,constraint sawon_salcomm_ck CHECK ((sal BETWEEN 1000 AND 9999) AND (comm >=0) AND sal > comm)
,constraint sawon_buno_fk foreign key(buno) references buseo(buno)
 --on delete cascade 1:1에서만 쓴다. <on update cascade는 존재하지 않는다.>
,constraint sawon_mgr_fk foreign key(mgr) references sawon(sano)
);

MS-SQL와는 달리 default는 제약조건이 아니고 컬럼이다.

ALTER TABLE sawon
modify jik DEFAULT '사원';

default 값 변경은 위와 같이 한다.

 7. 테이블의 제약조건 확인

SELECT *
FROM user_constraints
WHERE TABLE_name='SAWON';

8. 제약 조건의 삭제

ALTER TABLE [테이블명]
drop constraint [제약조건명]

9. DEFERRED : 제약조건을 바로 적용하지 않고 commit할때 적용한다.

위의 확인결과 중에서 DEFERRABLE 컬럼이 NOT  DEFERRABLE 라면 인서트시 제약 조건을 확인한다.

하지만 부서의 매니져 같이 순서대로 넣지 않으면 제약조건에 위배 될때

매니져 제약조건을 잠시 검사 안하게 하고 전부 삽입 후 체크를 하면 순서 상관 없이 넣을 수 있다.

단, 그때도 위배 되는 것은 rollback시킨다.

그 지정 방법은 아래와 같다.

ALTER TABLE sawon
add constraint sawon_mgr_fk
foreign key(mgr) references SAWON(sano)
deferrable initially DEFERRED;