[SQL] DDL : 제약조건- 작성 방법, NOT NULL, UNIQUE, CHECK

제약 조건(CONSTRAINTS)

o  테이블 작성 시 각 컬럼에 기록될 데이터에 대해 제약 조건을 설정할 수 있는데 이는 데이터 무결성 보장을 주 목적으로 한다.

o  제약 조건은 컬럼 별로 설정하며 컬럼 마다 다른 제약 조건을 지정할 수 있다.

o  입력 데이터에 문제가 없는지에 대한 검사와 데이터 수정/삭제 가능 여부 검사 등을 위해 사용한다.

제약 조건 (타입기호)	설명
NOT NULL (C)	데이터에 NULL을 허용하지 않음 (Default : NULL 허용)
UNIQUE (U)	컬럼에 중복된 값을 허용하지 않음  -> 컬럼의 모든 ROW에 중복 값이 없게 된다.
PRIMARY KEY (P)	- NULL과 중복 값을 허용하지 않음(컬럼의 고유 식별자로 사용하기 위해) - 데이터를 구분할 수 있는 컬럼에 설정하는 것으로 이 컬럼을 가지고 특정 ROW(DATA)를 찾을 수 있다. - 한개 테이블에 두개 이상의 PK를 설정할 수 없다. -> PK는 테이블당 한개 설정
FOREIGN KEY (R)	- 참조되는 테이블의 컬럼의 값이 존재하면 허용 - 설정한 컬럼의 값을 참조하는 테이블 컬럼에 있는 값만 저장하게 하는 조건이다. - 참조하는 테이블 컬럼의 값은 중복 값이 없어야 한다.
CHECK (C)	저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만 허용

o  아래 명령어를 이용하여 제약 조건을 확인할 수 있다.

SELECT * FROM USER_CONSTRAINTS;

SELECT * FROM USER_CONS_COLUMNS;

 

제약조건을 설정하는 2가지 방법

1.     컬럼 레벨 설정 : 컬럼을 선언한 곳에서 제약조건을 설정

                           : 컬럼명 자료형(크기) 제약조건

2.     테이블 레벨 설정 : 컬럼 설정문을 작성 후 마지막에 제약조건을 설정

                              : 컬럼문 작성 마지막에서 -> 제약조건(컬럼명)

CREATE TABLE CONS_NN(     USER_NO NUMBER NOT NULL, -- 방법 1 컬럼 레벨 설정     USER_NAME VARCHAR2(20),     EROLL_DATE DATE     UNIQUE(USER_NAME)     -- 방법 2 테이블 레벨 설정 );

  

NOT NULL

o  해당 컬럼에 반드시 값이 기록되어야 하는 경우 사용한다.

o  특정 컬럼에 값을 저장/수정할 때는 NULL값을 허용하지 않도록 컬럼 레벨에서 제한한다.

o  NOT NULL제약조건은 컬럼레벨에서만 설정이 가능하다.

CREATE TABLE CONS_NN(     USER_NO NUMBER NOT NULL,     USER_NAME VARCHAR2(20) NOT NULL,     EROLL_DATE DATE );   INSERT INTO CONS_NN VALUES(NULL,NULL,NULL); -- NULL 입력 불가 INSERT INTO CONS_NN VALUES(1,NULL,NULL);  -- 2번째 컬럼이 NULL이기에 입력 불가 INSERT INTO CONS_NN VALUES(1,'CHAN',NULL);   SELECT * FROM CONS_NN;

  

UNIQUE

o  컬럼 입력 값에 대해 중복을 제한하는 제약조건으로 컬럼 레벨과 테이블 레벨에 설정 가능하다.

o  중복 값이 있는 경우 UNIQUE 제약 조건에 의해 행이 삽입되지 않는다. (NULL 값 중복은 가능)

o  NULL값 중복 입력 처리에 대한 부분은 DBMS벤더 회사마다 다르다.

CREATE TABLE TBL_USER_UQ(     USER_NO NUMBER,     USER_ID VARCHAR2(20) UNIQUE,     USER_PWD VARCHAR2(20),     USER_NAME VARCHAR2(20),     EMAIL VARCHAR2(20) UNIQUE );   SELECT * FROM TBL_USER_UQ; INSERT INTO TBL_USER_UQ VALUES(1,'KKCHAN','1234','CHAN','K@CH'); INSERT INTO TBL_USER_UQ VALUES(1,'KKCHAN','1234','CHAN','K@CH'); -- USER_ID,EMAIL중복 값 에러 INSERT INTO TBL_USER_UQ VALUES(1,'KKCHAN1','1234','CHAN','K@CH'); -- EMAIL 중복 값 에러   INSERT INTO TBL_USER_UQ VALUES(2,NULL,'1234','CHAN','K2@CH1'); INSERT INTO TBL_USER_UQ VALUES(2,NULL,'1234','CHAN','K3@CH1'); -- NULL은 중복 입력 가능   -- 컬럼에 대한 제약 조건은 내부적으로 관리하는 번호가 있어 어느 컬럼의 제약조건이 걸리는지 에러메시지를 통해 알 수 있다. -- 테이블 시트 - 제약 조건 탭을 보면 CONSTRAINT_NAME 컬럼에 저장되어 있다.

 

o  테이블 레벨 제약조건은 여러 컬럼을 사용할 수도 있으며 AND조건으로 성립된다.

CREATE TABLE TBL_USER_UQ3(     USER_NO NUMBER,     USER_ID VARCHAR2(20),     USER_PWD VARCHAR2(20),     USER_NAME VARCHAR2(20),     EMAIL VARCHAR2(20),     UNIQUE(USER_ID,EMAIL) -- 두 가지 모두 중복일때만 제약 조건이 성립된다.(AND) );   INSERT INTO TBL_USER_UQ3 VALUES(1,'USER11','USER11','유저일','U@U.COM'); -- 여러 번 입력 시 중복으로 에러가 발생한다. INSERT INTO TBL_USER_UQ3 VALUES(1,'USER11','USER11','유저일','U1@U1.COM'); -- USER_ID가 중복이나 EMAIL이 중복이 아니기에 입력이 가능하다. INSERT INTO TBL_USER_UQ3 VALUES(1,'USER12','USER11','유저일','U1@U1.COM'); -- EMAIL이 중복이나 USER_ID가 중복이 아니기에 입력이 가능하다. INSERT INTO TBL_USER_UQ3 VALUES(1,'USER12','USER11','유저일','U1@U1.COM'); SELECT * FROM TBL_USER_UQ3;

 

o  제약조건은 한 컬럼에 두개 이상을 설정할 수도 있다.

CREATE TABLE TBL_USER_CONS(     USER_NO NUMBER,     USER_ID VARCHAR2(20) NOT NULL UNIQUE,     USER_PWD VARCHAR2(20),     USER_NAME VARCHAR2(20),     EMAIL VARCHAR2(20),     UNIQUE(USER_ID,EMAIL) -- 두 가지 모두 중복일때만 제약 조건이 성립된다.(AND) );   INSERT INTO TBL_USER_CONS VALUES(1,'USER1','USER1','유저1','U1@U1.NET'); INSERT INTO TBL_USER_CONS VALUES(1,NULL,'USER1','유저1','U1@U1.NET'); -- NULL 입력 불가 INSERT INTO TBL_USER_CONS VALUES(1,'USER1','USER1','유저1','U1@U1.NET'); -- 중복값 입력 불가 SELECT * FROM TBL_USER_CONS;

 

 CHECK

o  해당 컬럼에 입력되거나 수정되는 값을 체크하여 설정된 값 이외의 값이면 에러가 발생한다.

o  비교 연산자를 이용하여 조건을 설정하며 비교 값은 리터럴만 사용 가능하고 변하는 값이나 함수 사용은 불가능하다.

o  특정 값을 명시하거나 범위를 지정할 수 있다.

CREATE TABLE USER_CHECK(     USER_NO NUMBER,     USER_NAME VARCHAR2(20),     GENDER VARCHAR2(10) CHECK(GENDER IN ('남','여')), -- GENDER에는 '남' OR '여'만 입력할 수 있다.     AGE NUMBER     CHECK(AGE BETWEEN 17 AND 19)     -- CHECK(AGE BETWEEN 17 AND 19 AND USER_NO > 10) 이러한 형식도 가능하다 ); INSERT INTO USER_CHECK VALUES(1,'CHAN','M',30); -- 'M',30이 CHECK 제약 조건에 의해 입력이 불가능하다. INSERT INTO USER_CHECK VALUES(1,'CHAN','남',30); -- 30이 CHECK 제약 조건에 의해 입력이 불가능하다. INSERT INTO USER_CHECK VALUES(1,'CHAN','남',19); SELECT * FROM USER_CHECK;