[Oracle] 무결성 제약 조건

 

 

데이터 무결성 제약 조건이란 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서 테이블을

생성할 때 각 컬럼에 대해서 정의하는 여러 가지 규칙을 말한다

 

 

무결성 제약 조건	역할
NOT NULL	NULL을 허용하지 않는다.
UNIQUE (고유키)	중복된 값을 허용하지 않는다. 항상 유일한 값을 갖도록 한다.  ex ) E-mail
PRIMARY KEY (기본키)	NULL을 허용하지 않고 중복된 값을 허용하지 않는다.  NOT NULL 조건과 UNIQUE 조건을 결합한 형태     ex) 주민번호, ID
FOREIGN KEY (외래키)	참조되는 테이블의 칼럼의 값이 존재하면 허용한다.
CHECK	저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만을 허용한다.

 

NOT NULL 제약조건

특정 컬럼은 반드시 값이 입력되도록 필수 입력 컬럼으로 지정하는 것이다.

INSERT 할때 누락이 되면 안 되는 부분, NULL값이 들어가면 오류 발생

UNIQUE 제약조건

특정 컬럼에 대해 자료가 중복되지 않게 하는 것이다. 해당 테이블에 있어서는 존재하는 값이 유일해야한다. INSERT / UPDATE 할 떄 제약이 걸려 있는 컬럼에 동일한 데이터가 존재하면 오류 발생

NULL 값에 대해서는 UNIQUE 제약이 적용되지 않는다. NULL 값은 데이터로 인식하지 않기 때문에 해당 컬럼에 NULL 데이터행이 여러 개 존재 가능하다.

PRIMARY KEY 제약조건

UNIQUE 제약조건 + NOT NULL 제약조건이라고 생각하면 된다. 컬럼명과 자료형을 기술한 다음 PRIMARY KEY를 기술하면 된다. PRIMARY KEY는 하나의 테이블에 있는 데이터들을 식별하기 위한 기준으로 인식되는 제약 조건이다. 한 개의 테이블에 하나만 생성이 가능하며, PRIMARY KEY는 NOT NULL + UNIQUE의 속성을 가진다. 즉, NULL 값이 있으면 안 되고 해당 컬럼의 데이터는 중복되지 않고 유일해야 한다. UNIQUE에서와 같이 여러 컬럼들을 조합해서 지정할 수 있다.

FOREIGN KEY 제약조건

해당 컬럼에 참조하는 테이블로부터 존재하는 값들만 사용한다는 의미의 제약조건이다.

만일 참조하는 테이블에 해당하는 값이 없을 시에는 INSERT, UPDATE시에 오류가 나게 된다. 외래 키는 여러 개의 컬럼에 중복적으로 적용 가능하다. 외래 키로 두 테이블이 부모 테이블과 자식 테이블로 관계를 맺고 있을 시에 자식 테이블이 참조하는 데이터는 부모 테이블에서 삭제가 불가능하다

CHECK

조건에 부합하는 데이터만 입력이 가능하도록 하는 제약 조건이다

조건에는 기본 연산자나 비교 연산자 IN, NOT IN 등등이 사용 가능하다.

 

아래 예시를 확인해보자

INSERT INTO tb_test06(COL1, COL2)
VALUES('AAA', '111');

INSERT INTO tb_test06(COL1)
VALUES('BBB');

INSERT INTO tb_test06(COL2)
VALUES('222');

DROP TABLE tb_test06
CASCADE CONSTRAINTS;    -- 무결성 제약조건까지 모두 삭제

-- Primary key = Unique + NOT NULL
CREATE TABLE TB_TEST06(
    PK_COL VARCHAR2(10) CONSTRAINT PK_TEST_01 PRIMARY KEY,
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20)
);

INSERT INTO TB_TEST06(PK_COL, COL1, COL2)
VALUES('AAA', 'aaa', '111');

INSERT INTO TB_TEST06(PK_COL)
VALUES('BBB');

INSERT INTO TB_TEST06(PK_COL)
VALUES('bbb');

INSERT INTO TB_TEST06(PK_COL, COL1, COL2)
VALUES('', 'aaa', '111');

DROP TABLE tb_test06
CASCADE CONSTRAINTS;

CREATE TABLE TB_TEST06(
    PK_COL VARCHAR2(10) CONSTRAINT PK_TEST_01 PRIMARY KEY,
    COL1 VARCHAR2(20) CONSTRAINT PK_TEST_02 PRIMARY KEY,
    COL2 VARCHAR2(20)
);

CREATE TABLE TB_TEST06(
    PK_COL VARCHAR2(10),
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20),
    CONSTRAINT PK_TEST_01 PRIMARY KEY(PK_COL, COL1)
);

CREATE TABLE TB_TEST06(
    PK_COL VARCHAR2(10),
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20)
);

ALTER TABLE TB_TEST06
ADD CONSTRAINT PK_TEST_01
PRIMARY KEY(PK_COL);

INSERT INTO tb_test06(PK_COL, COL1, COL2)
VALUES('AAA', 'aaa', '111');

ALTER TABLE TB_TEST06
DROP CONSTRAINT PK_TEST_01;

DROP TABLE tb_test06
CASCADE CONSTRAINTS;

-- Unique : 고유키. 중복된 값은 입력불가. NULL허용
CREATE TABLE TB_TEST06(
    UK_COL VARCHAR2(10) CONSTRAINT UK_TEST_01 UNIQUE,
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20)
);

INSERT INTO tb_test06(UK_COL, COL1, COL2)
VALUES('AAA', 'aaa', '111');

INSERT INTO tb_test06(UK_COL, COL1, COL2)
VALUES('', 'aaa', '111');

INSERT INTO tb_test06(COL1, COL2)
VALUES('aaa', '111');

-- FOREIGN KEY : 외래키
--               기본 테이블(departments)에서 PK, UK로 설정되어 있어야 한다.
--               NULL 값을 허용

DROP TABLE TB_DEPT
CASCADE CONSTRAINTS;

-- PARANT TABLE : departments
CREATE TABLE TB_DEPT(
    DEPARTMENT_ID VARCHAR2(10),
    DEPARTMENT_NAME VARCHAR2(20),
    LOCATION_ID NUMBER,
    CONSTRAINT PK_DEPT_TEST PRIMARY KEY(DEPARTMENT_ID)
);

INSERT INTO tb_dept(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES('10', '기획부', 120);

INSERT INTO tb_dept(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES('20', '관리부', 150);

INSERT INTO tb_dept(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES('30', '개발부', 180);

-- CHILD TABLE : employees
CREATE TABLE TB_EMP(
    EMPNO VARCHAR2(10),
    ENAME VARCHAR2(20),
    DEPARTMENT_ID VARCHAR2(10),
    CONSTRAINT FK_EMP_TEST FOREIGN KEY(DEPARTMENT_ID)
        REFERENCES TB_DEPT(DEPARTMENT_ID)
);

INSERT INTO tb_emp(EMPNO, ENAME, DEPARTMENT_ID)
VALUES('100', '홍길동', '20');

INSERT INTO tb_emp(EMPNO, ENAME, DEPARTMENT_ID)
VALUES('101', '성춘향', '10');

INSERT INTO tb_emp(EMPNO, ENAME, DEPARTMENT_ID)
VALUES('102', '일지매', '30');

INSERT INTO tb_emp(EMPNO, ENAME, DEPARTMENT_ID)
VALUES('103', '홍두께', '');

SELECT e.empno, e.ename, d.department_id, d.department_name
FROM tb_emp E, tb_dept D
WHERE e.department_id = d.department_id;

-- CHECK : 지정된 값만 을 넣을 수 있다. NULL을 허용, 중복 허용
CREATE TABLE TB_CHECK(
    COL1 VARCHAR2(10),
    COL2 VARCHAR2(20),
    CONSTRAINT CHK_01 CHECK( COL1 IN('사과', '배', '바나나') ),
    CONSTRAINT CHK_02 CHECK( COL2 > 0 AND COL2 <= 100 )
);

INSERT INTO tb_check(COL1, COL2)
VALUES('사과', 50);

INSERT INTO tb_check(COL1, COL2)
VALUES('귤', 50);

INSERT INTO tb_check(COL1, COL2)
VALUES('', 50);

INSERT INTO tb_check(COL1, COL2)
VALUES('', 101);

SELECT constraint_name, constraint_type, table_name
FROM user_constraints