[Oracle] 제약조건(Constraints) 의 개념과 종류(2) Primary key&Foreign key&Check

기본키 (Primary key)

기본키는 데이터 무결성을 관계형 데이터 베이스에서 실질적으로 구현한 제약조건이다.예를 들어 사원 테이블(employees)의 사번과 사원명이라는 컬럼이 있다고 하자. 여기서 사원명은 중복될 가능성이 있다. 따라서 사원 테이블에서 특정한 사원을 식별하려면 중복되지 않는 값을 가지는 사번이 필수적이다.이러한 사번이 바로 기본키(NOT NULL +UNIQUE)이다.

(1)  기본키 설정

기본키는 유일한 식별자 개념으로 당연히 테이블 당 1개만 생성할 수 있다.기본키로 사용할 수 있는 컬럼 개수는 최대 32개이다.(32개로 설정할 일이 있을까...) 기본키는 CREATE, ALTER  구문에서 정의가능하며 생성 방법은 다음과 같다.

CREATE 문 기본키 설정

-- CREATE 문 기본키 생성문 Syntax
CREATE TABLE 테이블명
( 
  컬럼1 데이터타입 null/not null
  컬럼2 데이터타입 null/not null
  ...
  
  CONSTRAINT 제약조건명 PRIMARY KEY(column1,column2,...)
  );
  
-- 생성 예시 
 CREATE TABLE supplier
(
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

--복수 컬럼 지정 기본키 생성
CREATE TABLE supplier
(
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

NULL 값 입력 시 오류 발생

이미 존재하는 값 입력 시 unique 제약 조건 충돌 에러 발생

ALTER 문 기본키 설정

ALTER 문으로 테이블에 새로운 필드를 추가하거나 수정할 때도 기본키 제약 조건을 설정할 수 있다.

-- ALTER 문 기본키 제약조건 변경
ALTER TABLE 테이블명
ADD CONSTRAINT 제약조건명 PRIMARY KEY (column1, column2, ... column_n);

-- 예시
ALTER TABLE supplier
ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name);

 

Drop & Disable & Enable 기본키 설정

각각의 구문을 통해 테이블 기본키의 삭제, 해제, 활성화를 할 수 있다.

-- 기본키 제약조건 삭제
ALTER TABLE 테이블명
DROP CONSTRAINT 제약조건명;

--기본키 해제
ALTER TABLE 테이블명
DISABLE CONSTRAINT 제약조건명;

-- 기본키 활성화
ALTER TABLE 테이블명
ENABLE CONSTRAINT 제약조건명;

외래키 (Foreign key)

외래키는 테이블 간의 참조 데이터 무결성을 위한 제약조건이다.외래 키는 한 테이블의 값이 다른 테이블에도 표시되어야 함을 의미한다. 사원 테이블에 부서번호(department_id) 컬럼이 있고 부서정보는 부서(department) 테이블에 있다. 관리자가 데이터를 입력할 때 부서 테이블에 없는 부서번호를 사원 테이블에 입력한다면 이는 논리적으로 합당하지 않고, 사원에 대한 부서정보도 제대로 찾을 수 없다. 이 때 외래키를 설정하면 부서정보에 없는 부서번호를 입력하려고 시도할 시 오라클이 오류를 발생시켜  잘못된 입력을 막는다. 즉 참조 무결성을 보장하는 것이다. 

(1)  외래키 설정

참조된 테이블을 상위 테이블이라고 하고, 외래 키가 있는 테이블을 하위 테이블이라고 보통 지칭한다.하위 테이블의 외래키는  상위 테이블의 기본키를 참조하는 구조를 지닌다.외래키에 사용할 수 있는 최대 컬럼 개수 또한 32개이다. 외래키도 CREATE 또는 ALTER 구문을 통해 설정 가능하다.

CREATE 문 기본키 설정

-- CREATE 문 외래키 생성문 Syntax
CREATE TABLE 테이블명
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT 외래키명
    FOREIGN KEY (column1, column2, ... column_n)
    REFERENCES 상위 테이블 (column1, column2, ... column_n)
);
  
-- 생성 예시 
 CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id)
);


--복수 컬럼 지정 외래키 생성
CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  CONSTRAINT fk_supplier_comp
    FOREIGN KEY (supplier_id, supplier_name)
    REFERENCES supplier(supplier_id, supplier_name)
);

ALTER 문 외래키 설정

ALTER 문으로 테이블에 새로운 필드를 추가하거나 수정할 떄도 외래키 제약 조건을 설정할 수 있다.

-- ALTER 문 외래키 제약조건 변경
ALTER TABLE 테이블명
ADD CONSTRAINT 제약조건명
   FOREIGN KEY (column1, column2, ... column_n)
   REFERENCES 상위 테이블 (column1, column2, ... column_n);

-- 단일 컬럼 참조
ALTER TABLE products
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id)
  REFERENCES supplier(supplier_id);
  
-- 복수 컬럼 참조
  ALTER TABLE products
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id, supplier_name)
  REFERENCES supplier(supplier_id, supplier_name);

Check

Check는 컬럼에 입력되는 데이터를 체크해 특정 조건에 맞는 데이터만 입력 받게끔 하는 제약조건이다.

(1)  Check 설정

Check 제약 조건은 뷰(VIEW), 서브쿼리,다른 테이블 참조열 등에는 적용할 수 없다. 해당 테이블의 열만 참조할 수 있으며 CRETE&ALTER 문을 통해 역시 설정할 수 있다.

CREATE 문 Check 설정

CREATE TABLE 테이블 명
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...
  CONSTRAINT 제약조건명 CHECK (컬렁명 조건) [DISABLE]
);

-- 예시
CREATE TABLE suppliers
(
  supplier_id numeric(4),
  supplier_name varchar2(50),
  CONSTRAINT check_supplier_id
  CHECK (supplier_id BETWEEN 100 and 9999)
);

Check 제약조건 충돌 에러

ALTER & DROP & ENABLE & DISABLE 문 Check 설정

-- Check 삭제
ALTER TABLE suppliers
DROP CONSTRAINT check_supplier_id;

-- Check 활성화
ALTER TABLE suppliers
ENABLE CONSTRAINT check_supplier_id;

-- Check 비활성화
ALTER TABLE suppliers
DISABLE CONSTRAINT check_supplier_id;

참고자료

Oracle / PLSQL: Foreign Keys