23강 제약 조건의 이해와 설정

T-Evan 2010. 11. 7. 10:41

제약 조건이란 테이블 단위에서 데이터의 무결성을 보장해주는 원칙이다. 제약 조건은 테이블에 데이터가 입력, 수정, 삭제되거나 테이블이 삭제, 변경되는 경우 잘못된 트랜잭션이 수행되지 않도록 방지해주는 역할을 담당한다.

* 제약조건
   - 테이블 단위에서 정의되고 적용된다.
   - 종속성이 존재하는 경우 테이블의 삭제를 막아준다.
   - 자료가 삽입, 갱신, 삭제될 때마다 규칙이 적용된다.
   - 일시적으로 활성화하거나 비활성화하는 것이 가능하다.
   - USER_CONSTRAINTS, USER_CONS_COLUMNS 딕셔너리에서 검색한다.
   - 제약 조건은 개체처럼 관리되므로 반드시 이름이 필요하다. 제약 조건에 이름을 정의하지 않으면 오라클 서버가 자동으로 SYS_Cn형태의 이름을 붙인다.

* 컬럼 레벨 정의
   - 열별로 제약 조건을 정한다.
   - 무결성 제약조건을 모두 정의할 수 있다.
   - NOT NULL은 컬럼 레벨에서만 정의할 수 있다.

* 테이블 레벨
   - 컬럼 정의와는 별도로 정의한다.
   - 하나 이상의 열을 묶어서 정의할 경우 유일한 방법이다.
   - NOT NULL은 정의 불가능하다.

* 오라클에서 제공되는 제약조건
   - PRIMARY KEY
   - FOREIGN KEY
   - UNIQUE KEY
   - NOT NULL
   - CHECK

★ 제약 조건 설정
* Primary Key 설정
SQL> CREATE TABLE 테이블 (
      > 컬럼명 데이터_타입 CONSTRAINT 제약_조건_이름 PRIMARY KEY,
      > ......

SQL> CREATE TABLE 테이블명 ( ← 테이블 레벨 정의
      > .....
      > CONSTRAINT 제약_조건_이름 PRIMARY KEY (컬럼));

   - 각 행을 고유하게 식별 가능하도록 하는 PK(Primary Key)를 정의한다.
   - 테이블당 하나만 정의 가능하다.
   - 고유 인덱스(Unique Index)가 자동으로 생성된다.
   - 지정된 컬럼에는 중복된 값이나 NULL값이 입력될 수 없다.
   - PK로 지정 가능한 컬럼이 여러 개 있을 때는 이중에 검색에 많이 사용되고 간단하고 짧은 컬럼을 지정한다.
   - 주 식별자, 주키, 주식별자 등으로 불린다.

* Foreign Key 설정
SQL> CREATE TABLE 테이블 (
      > 컬럼명 데이터_타입 CONSTRAINT 제약_조건_이름 PRIMARY KEY
      > REFERENCES 참조할_테이블 (참조할_컬럼)
      > [ON DELETE CASCADE],
      > .....

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

   - FK(Foreign Key)를 정의한다.
   - FK가 정의된 테이블이 하위(자식) 테이블이다.
   - 참조되는 테이블을 상위(부모) 테이블이라고 한다.
   - 상위 테이블은 미리 생성되어 있어야 한다.
   - 상위 테이블의 참조되는 컬럼에 존재하는 값만을 입력 할 수 있다.
   - 상위 테이블에 참조되는 행의 데이터는 삭제나 변경이 불가능 하다.
   - 상위 테이블은 FK로 인해 삭제가 불가능하다.
   - ON DELETE CASCADE : 참조되는 상위 테이블의 행에 대한 DELETE를 허용한다.
      . 하위 테이블의 행도 같이 지워진다.
   - 자료형이 반드시 일치해야 한다.
   - 같은 테이블을 다른 컬럼을 참조 할 수 있다.
   - 참조되는 컬럼은 PK이거나 UK(Unique Key)만 가능하다.
   - 외부키, 참조키, 외부 식별자 등으로 불린다.

* Unique Key 설정
SQL> CREATE TABLE 테이블 (
      > 컬럼 데이터_타입 CONSTRAINT 제약_조건_이름 UNIQUE,
      > ......

SQL> CREATE TABLE 테이블 (
      > .....
      > CONSTRAINT 제약_조건_이름 UNIQUE (컬럼));

   - UK(Unique Key)를 정의한다.
   - 중복된 값을 허용하지 않지만 여러 개의 NULL값은 허용한다.
      . NULL값은 비교가 불가능함으로 여러 개가 있어도 중복된 값이 아니다.
   - 고유 인덱스가 생성된다.
   - 고유키 등으로 불린다.

* NOT NULL 설정
SQL> CREATE TABLE 테이블 (
      > 컬럼 데이터_타입 CONSTRAINT 제약_조건_이름 NOT NULL,
      > ......

   - 테이블 레벨 정의가 불가능하다.
   - 지정된 컬럼은 NULL 값을 허용하지 않는다.
   - 반드시 널값이 필요한 컬럼을 제외하고 가능한 모든 컬럼에 NULL값을 허용하지 않는 것이 좋다.

* Check 설정
SQL> CREATE TABLE 테이블 (
      > 컬럼 데이터_타입 CONSTRAINT 제약_조건_이름 CHECK (조건),
      > ......

SQL> CREATE TABLE 테이블 (
      > .....
      > CONSTRAINT 제약_조건_이름 CHECK (조건));

   - 행에 입력될 데이터의 조건을 정의한다.
   - 조건은 WHERE절에 기술하는 조건 형식과 동일하다.

SQL> SELECT c.table_name, c.constraint_name, c.constraint_type, 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;

   - 테이블에 정의된 제약 조건을 검색한다.
   - constraint_name : CONSTRAINT 이름
   - constraint_type : CONSTRAINT 타입
      . P(PK), R(FK), U(UK), C(NOT NULL, CHECK)

SQL> 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;

   - 지정한 테이블을 참조하는 테이블의 목록을 확인한다.
   - 하위 테이블이 지정되어 있으면 삭제가 불가능하다.

예제 1. student, professor, course, score 테이블을 삭제한다.

예제 2. 다음과 같은 구조의 테이블을 생성하고 테이블과 제약 조건을 검색한다.

* 테이블 : board
   - 컬럼 구성
      no : 게시물번호 : PK
      name : 작성자 : NOT NULL
      sub : 제목 : NOT NULL
* 테이블 : b_content
   - 컬럼 구성
         no : 게시물번호 : PK, FK(board.no)
         content : 내용 : NOT NULL

예제 3. 다음과 같은 구조의 테이블을 생성하고 테이블과 제약 조건을 검색한다.

* 테이블명 : insa
   - 컬럼 구성
      jumin : 주민번호 : PK
      name : 이름 : NOT NULL
      sex : 성별 : (F,M)

예제 4. 제약 조건의 이름을 정의하지 않는 경우를 확인한다.

* 테이블 : test
- 컬럼 : no(PK)

실습

실습 쿼리

다운로드

factory.sql

① 다음 구조를 갖는 테이블을 생성한다.

goods 테이블(제품)
: gno(제품번호), gname(제품명), pri(표준단가), fac_no(생산공장)
factory 테이블(공장)
: fno(공장번호), fname(공장이름), loc(지역)
prod 테이블(출고 상품)
: s_num(일련번호), gno(제품번호), pri (출고단가), date(생산일자)

문제

실습쿼리

다운로드

st_pr.sql

1. 다음 테이블에 대한 표를 보고 테이블을 생성을 위한 스크립트를 생성한다.

저작자표시 (새창열림)