[Oracle] 제약 조건 -1 (PK, FK)

• 제약 조건
  • 테이블 단위에서 정의되고 적용된다.
  • 종속성이 존재하는 경우 테이블의 삭제를 막아준다.
  • 자료가 삽입, 갱신, 삭제될 때마다 규칙이 적용된다.
  • 일시적으로 활성화하거나 비활성화하는 것이 가능하다.
  • 제약 조건은 개체처럼 관리되므로 반드시 이름이 필요하다.
• 오라클에서 제공되는 제약 조건
• PRIMARY KEY (기본키)
• FOREIGN KEY (외래키)
• UNIQUE KEY (유니크키|유일키)
• NOT NULL (공백불가)
• CHECK (값의 범위|패턴 정의)

PK, FK

• PK(PRIMARY KEY) : 테이블내에 모든 행을 유일하도록 식별해주는 컬럼
  • 테이블 마다 한 개만 정의 가능하다.
  • 모든 컬럼은 PK 컬럼에 함수적 종속 관계를 갖는다 
    • 학번 → (이름, 학년, 학과, ...)
  • 중복 될 수 없고 NULL을 허용하지 않는다. (고유 인덱스 자동 생성)

CREATE TABLE 테이블명 ( ...
CONSTRAINT 제약_조건 PRIMARY KEY (컬럼명));

• FK(FOREIGN KEY) : 다른 테이블에 관계를 통해 접근하게 해주는 컬럼 
  • 테이블 간 관계를 의미한다.
  • 항상 부모 자식 관계이다.
  • 자식 테이블의 참조 컬럼에 지정한다.
  • 두 컬럼에 데이터 타입이 일치해야 한다.
  • PK나 UK만 참조 가능

CREATE TABLE 테이블명 ( ...
CONSTRAINT 제약_조건 FOREIGN KEY (컬럼명)
REFERENCES 참조_테이블명 (참조_컬럼명) [ON DELETE CASCADE]);

ON DELETE CASCADE : 연쇄 삭제 ( 참조하고 있는 값까지 같이 삭제하는 옵션)

//dept, emp 테이블을 생성 (PK, FK를 추가해서 새로 생성)

DROP TABLE dept;
DROP TABLE emp;
PURGE RECYCLEBIN;

CREATE TABLE dept(
dno VARCHAR(8),
dname VARCHAR2(15),
loc VARCHAR2(9),
CONSTRAINT dept_dno_pk PRIMARY KEY(dno) --PK 제약조건 설정문
);

CREATE TABLE emp(
eno VARCHAR2(8),
ename VARCHAR2(15),
sex VARCHAR2(4),
job VARCHAR2(12),
mgr VARCHAR2(4),
hdate DATE,
sal NUMBER,
comm NUMBER,
dno VARCHAR2(2),
CONSTRAINT emp_eno_pk PRIMARY KEY (eno),
CONSTRAINT emp_mgr_fk FOREIGN KEY (mgr) REFERENCES emp (eno),
CONSTRAINT emp_dno_fk FOREIGN KEY (dno) REFERENCES dept (dno)
);

@ucon
@ref

INSERT INTO dept (dno, dname, loc) VALUES ('10','개발','서울');
INSERT INTO emp (eno, ename, dno) VALUES ('2000','문시현','10');
COMMIT;

-- 무결성 제약 조건에 의해 실행디지 않는 문장들 (에러 발생)

INSERT INTO dept (dno, dname, loc) VALUES ('10','총무','부산');
                                          -- PK 중복 ('10')
INSERT into EMP (eno,ename,dno) VALUES ('2001','손하늘','20');
													--dept에 '20'(dno)이란 값이 존재하지 않음
DELETE FROM dept WHERE dno ='10'; -- emp에서 참조하고 있기때문에 삭제 불가
UPDATE emp SET dno = '20' WHERE ename = '문시현'
			-- 2번째와 같은 이유

제약 조건 검색

SELECT c.table_name, c.constraint_name, c.constraint_type,
       c.status, s.column_name
FROM user_constraints c, user_cons_columns s
WHERE c.constraint_name = s.constraint_name
AND c.table_name in (검색_대상_테이블_목록) --이 구문을 뺴고 스크립트로 저장하면 전체테이블목록
ORDER BY c.table_name;

SELECT  p.table_name 상위테이블, p.constraint_name 상위제약조건,       
        c.table_name 하위테이블, c.constraint_name 참조제약조건
FROM user_constraints p, user_constraints c
WHERE c.r_constraint_name=p.constraint_name
AND p.table_name in (검색_대상_테이블_목록) --위 주석과 동일
ORDER BY p.table_name;

- USER_CONSTRAINTS, USER_CONS_COLUMNS 로 부터 제약 조건을 검색한다.
‐ ucon.sql, ref.sql 스크립트로 저장해서 사용한다

제약조건 예제

// 다음 구조를 갖는 테이블을 생성한다. 각 테이블에는 필요한 PK와 FK를 지정한다
- 데이터 타입이나 길이는 임의로 각자 결정하고 테이블 생성 순서에 주의한다.
factory 테이블(공장)
: fno(공장번호), fname(공장이름), loc(지역)
goods 테이블(제품)
: gno(제품번호), gname(제품명), pri(표준단가), fac_no(생산공장)
prod 테이블(출고 상품)
: s_num(일련번호), gno(제품번호), pri (출고단가), pdate(생산일자)

DROP TABLE factory;
DROP TABLE goods;
DROP TABLE prod;

PURGE RECYCLEBIN;

CREATE TABLE factory(
fno VARCHAR2(8),
fname VARCHAR2(40),
loc VARCHAR2(8),
CONSTRAINTS factory_fno_pk PRIMARY KEY(fno)
);

CREATE TABLE goods(
gno VARCHAR(8),
gname VARCHAR2(15),
pri NUMBER,
fac_no VARCHAR2(8),
CONSTRAINTS goods_gno_pk PRIMARY KEY(gno),
CONSTRAINTS goods_facno_fk FOREIGN KEY(fac_no) REFERENCES factory (fno)
);

CREATE TABLE prod(
s_num VARCHAR2(8),
gno VARCHAR(8),
pri NUMBER,
pdate DATE DEFAULT sysdate,
CONSTRAINTS prod_snum_pk PRIMARY KEY(s_num),
CONSTRAINTS prod_gno_fk FOREIGN KEY(gno) REFERENCES goods(gno)
);

@ucon
@ref