0811_Oracle : DEFAULT와 CHECK 제약조건

DEFAULT 설정  : 값을 입력하지 않으면 뒤에 값을 등록한다.

 

예시를 통해 알아보자.

 

CREATE TABLE test1(
    num NUMBER PRIMARY KEY,
    name VARCHAR2(30) NOT NULL,
    subject VARCHAR2(500) NOT NULL,
    content VARCHAR2(4000) NOT NULL,
    reg_date DATE DEFAULT SYSDATE,
    hitCount NUMBER DEFAULT 0 NOT NULL
);

reg_date와 hitCount는 값을 입력하지 않으면 각각 SYSDATE 와 0을 입력한다.

 

데이터를 입력해서 확인해보자.

INSERT INTO test1(num, name, subject, content) VALUES(1, 'a', 'a', 'a');
INSERT INTO test1(num, name, subject, content) VALUES(2, 'b', 'b', 'b');
SELECT * FROM test1;

INSERT INTO test1(num, name, subject, content, reg_date) 
	VALUES(3, 'c', 'c', 'c', TO_DATE('2000-10-10', 'YYYY-MM-DD'));
SELECT * FROM test1;

INSERT INTO test1(num, name, subject, content, reg_date, hitCount) 
	VALUES(4, 'd', 'd', 'd', SYSDATE, 10);
SELECT * FROM test1;

결과를 보면 입력시 reg_date 와 hitCount를 넣지 않았더니DEFALUT로 설정한 SYSDATE 와 0이 들어간 것을 확인 할 수 있다.

reg_date와 hitCount를 직접 입력했을 때는 입력한 값이 들어감을 확인 할 수 있다.

 

※ DEFALUT가 있어도 행을 추가할 때 값이 들어가지 않을 수도 있기 때문에 자바로 코딩할 때 DEFALUT가 없다고 생각하고 값을 넣어주자!

DEFALUT의 값을 확인 방법

명령어를 이용해서 확인할 수 있다.

SELECT * FROM cols WHERE table_name = 'TEST1'; 

SELECT * FROM user_tab_columns WHERE table_name = 'TEST1'; 

SELECT * FROM cols WHERE table_name = 'TEST1'; -- DEFAULT 확인 가능
SELECT * FROM user_tab_columns WHERE table_name = 'TEST1'; -- DEFAULT 확인 가능

DATA_DEFAULT 컬럼에 보면 SYSDATE 와 0이 들어가있는 것을 확인 할 수 있다.

 

이미 존재하는 테이블 칼럼에 DEFAULT를 추가하려면 ALTER TABEL 명령을 이용한다.

ALTER TABLE test1 MODIFY 컬럼명 DEFAULT 값;

DEFAULT 제거하는 방법

ALTER TABLE 테이블명 MODIFY 컬럼 DEFAULT NULL;

ALTER TABLE 테이블명 MODIFY 컬럼 DEFAULT NULL; 을 통해서 DEFAULT를 제거한다. DELETE를 사용하지 않는다.

---

CHECK 제약조건에 대해 알아보자.

 

테이블 생성을 통한 예시로 알아보자.

CREATE TABLE test2(
    num NUMBER PRIMARY KEY,
    name VARCHAR2(30) NOT NULL,
    com NUMBER(3) NOT NULL,
    excel NUMBER(3) CHECK( excel BETWEEN 0 AND 100),
    CONSTRAINT ch_test2_com CHECK ( COM BETWEEN 0 AND 100)
);


SELECT * FROM cols WHERE table_name = 'TEST2'; 
SELECT * FROM user_tab_columns WHERE table_name = 'TEST2';

excel을 입력할 때 0~100사이여야 입력이 가능하다. com의 경우 테이블레벨에서 CHECK제약을 주었다. 이렇게 CHECK 제약조건을 줄 수 있다. 하지만 이렇게 CHECK 제약을 주는 것보다 후에는 자바에서 제약을 주는 것이 속도면에서 효율적이다.

 

테이블에 값을 입력해보자.

INSERT INTO test2(num, name, com, excel) VALUES (1, 'a', 100, 100);

INSERT INTO test2(num, name, com, excel) VALUES (2, 'b', 120, 100);
    -- 에러 ORA-02290 : CHECK 제약 위반

com과 excel 모두 0~100까지의 값만을 넣을 수 있기 때문에 ORA-02290 : CHECK제약조건위반으로 값이 입력되지 않는다.

 

문제풀이 : 여태껏 배운 것을 바탕으로 테이블을 만들어보자 > 

테이블명 : test3num NUMBER PRIMARY KEYname VARCHAR2(50) NOT NULLgender VARCHAR2(10) NOT NULLsdate DATE NOT NULLedate DATE NOT NULL

 

-- gender 컬럼은 '남', '여'만 등록될 수 있도록 설정한다.-- 다음의 CHECK 제약을 추가한다 : sdate<= edate

 

CREATE TABLE test3 (
    num NUMBER PRIMARY KEY,
    name VARCHAR2(50) NOT NULL,
    gender VARCHAR2(10) NOT NULL CHECK(gender IN('남', '여')),
    -- '남', '여'만 등록될 수 있도록 설정
    -- gender VARCHAR2(10) CHECK(gender IN('남', '여')) DEFAULT '여' 이거는 안된다.
    -- DEFAULT와 CHECK를 동시에 설정할 경우는 DEFAULT를 먼저
    -- gender VARCHAR2(10) DEFAULT '여' CHECK(gender IN('남', '여')) 로 해야함.
    sdate DATE NOT NULL,
    edate DATE NOT NULL
);

ALTER TABLE test3 ADD CHECK ( sdate<= edate );
-- 다른 컬럼을 이용하여 CHECK 제약을 만드는 경우에는 테이블 레벨 또는
-- ALTER TABLE로 만든다.

gender VARCHAR2(10) NOT NULL CHECK(gender = '남' OR gender = '여') 를 gender IN('남', '여') 로 줄 수 있다.

다른 컬럼을 이용하여 CHECK 제약을 만드는 경우에는 테이블 레벨 또는 ALTER TABLE을 통해 제약조건을 만든다.

 

데이터를 추가해보자.

INSERT INTO test3(num, name, gender, sdate, edate) VALUES (1, 'a', '남', SYSDATE, SYSDATE);

INSERT INTO test3(num, name, gender, sdate, edate) 
    VALUES (2, 'b', '남', SYSDATE, TO_DATE('01/01/01', 'RR/MM/DD') );
    -- 에러 CHECK 제약 위반

 edate는 sdate와 같거나 더 뒤의 날짜여야 하므로 CHECK 제약에 위반 (ORA-02290) 되므로 두번째 쿼리는 오류를 발생한다.