[SQL] 제약조건 설정 방법과 사용 예시모음

 

 

 

 

1) 제약조건이란?

원하는 데이터 값만을 유지하기 위해서 특정 컬럼마다 설정하는 제약이다. (데이터 무결성 보장을 목적으로 한다.)

제약조건이 부여된 컬럼에 문제가 있는지 없는지 자동으로 검사하는 시스템이다.

 

1-1) 제약조건의 종류

NOT NULL	해당 컬럼이 비어있어도 되는 컬럼인지 아닌지 지정해주는 것이다. 예를들면 회원가입시 아이디는 절대 없으면 안되는 정보이다.
PK (기본키)	테이블을 특정 지을 수 있는 기본키이다. 사람으로 치면 주민번호가 될 수 있고, 회원으로 치면 아이디가 될 수 있는 키값을 기본키라고 한다.
고유키	기본키가 되진 못했지만 고유해야 하는 값들에 설정한다. 예를 들면 회원가입시 기본키를 아이디로 설정했다면 ( 기본키도 유니크 제약조건성을 가지고 있어 겹칠 수 없다. ) 사이트에서 사용하는 명찰 같은 닉네임은 고유키로 설정할 수 있다.
체크 제약조건	설정한 값들 중에서만 데이터 값이 입력되어야 할 경우 사용한다. 예를 들어 남자는 M, 여자는 W로 값을 지정시켰을 경우 해당 컬럼에는  지정한 값 제외한 값들이 입력될 시 제약조건 미  충족으로 오류가 발생한다.
FK (외래키)	해당 컬럼에 다른 테이블에 존재하는 값이 들어와야하는 경우에 부여하는 제약조건이다. 다른 테이블을 참조한다고 표현하며 참조된 다른 테이블이 제공하고 있는 값이 들어올 수 있다. 이 외래키를 통해 테이블끼리의 관계가 형성된다.

 

 

 

 

 

 

2) 제약조건 기입 방법

제약조건의 부여는 적는 방법에 따라 두가지로 나뉘는데 하나는 컬럼레벨 방식, 하나는 테이블 레벨 방식이다.

 

2-1) 컬럼레벨

CREATE TABLE TEST(
  MEMBER_NO NUMBER PRIMARY KEY -- 컬럼테이블
  NAME VARCHAR2(20) NOT NULL
);

컬럼명, 데이터타입, 기본값, 제약조건 을 차례대로 기입하여 컬럼을 만들면서 같은 라인에제약조건을 부여하는 방법이다.

 

 

2-2)  테이블레벨

CREATE TABLE TEST(
  MEMBER_NO NUMBER,
  NAME VARCHAR2(20) NOT NULL
  PRIMARY KEY(MEMBER_NO) -- 테이블레벨 방식
);

테이블 제약조건은 테이블에 들어갈 컬럼을 모두 기입 후 가장 밑에 제약조건을 부여하는 방식이다.

 

 

 

 

 

 

3) 제약조건명 설정

제약조건에 제약조건명(별칭)을 부여 할 수 있다.

사용자가 제약조건명을 설정하지 않을 시 오라클이 자동으로 생성하니 사실상 부여하지 않아도 된다.

제약조건명을 설정해놨을 경우에 해당 제약조건에 부합하여 오류가 발생할 경우 어떤 제약조건이 위반되었는지 사용자가 설정한 제약조건명이 오류창에 출력되어 오류의 원인을 파악하기 편리하다. 

입력방법은 간단한데 제약조건 앞에 CONSTRAINT 와 부여할 제약조건명을 기재해주면 된다.

 

3-1) 제약조건 설정(컬럼레벨)

CREATE TABLE 테이블명(
  컬럼명 자료형 CONSTRAINT 제약조건명 제약조건,
  컬럼명 자료형 CONSTRAINT 제약조건명 제약조건,
  ...
);

 

3-2) 제약조건 설정(테이블레벨)

CREATE TABLE 테이블명(
  컬럼명 자료형,
  컬럼명 자료형,
  ...
  CONSTRAINT 제약조건명 제약조건(컬럼명) -- 테이블 레벨 방식
);

 

 

 

 

 

4) 제약조건 사용 방법 및 예시

4-1) NOT NULL 제약조건

꼭 입력이 있어야 하는 회원번호, 아이디, 비밀번호에 NOT NULL 제약조건 부여했을 때 아래와 같다.

NOT NULL은 컬럼레벨 방식만 사용가능하다.

CREATE TABLE MEM_NOTNULL(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20),
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    MEM_DATE DATE
);

 

 

 

 

4-2) 고유키 ( UNIQUE 제약조건 )

회원아이디는 중복될 수 없도록 회원 아이디에 고유키를 부여했을 때 아래와 같다.

 

4-2-1) 컬럼 레벨 방식

CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE, -- 컬럼레벨 방식
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    MEM_DATE DATE
);

 

4-2-2) 테이블 레벨 방식

CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    MEM_DATE DATE,
    UNIQUE(MEM_ID) -- 테이블레벨 방식
);

 

 

 

 

 

 

4-3) CHECK 제약조건

성별 컬럼에는 '여' 또는 '남'의 데이터 값만 올 수 있게 설정하고 싶다면 아래 방식으로 부여한다.

참고로 체크 제약조건엔 NULL도 들어 올 수 있다. NULL 값은 못들어오게 하려면 NOT NULL 제약조건을 추가로 부여하면 된다.

 

 

4-3-1) 컬럼레벨 표기 방식

CREATE TABLE MEM_CHECK(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')), -- 컬럼레벨 방식
    PHONE VARCHAR2(15),
    MEM_DATE DATE NOT NULL
);

 

4-3-2) 테이블레벨 표기 방식

CREATE TABLE MEM_CHECK(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    MEM_DATE DATE NOT NULL,
    CHECK(GENDER IN ('남', '여')), -- 테이블레벨 방식
);

 

 

4-3-3) 부등호 사용도 된다.

예를 들어 성인만 가입할 수 있는 사이트로 만든다면 아래처럼 나이에 제한을 둘 수 있다.

CREATE TABLE MEM_CHECK(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    MEM_DATE DATE NOT NULL,
    AGE NUMBER CHECK (AGE > 19)
);

 

 

 

 

 

4-4) PK 기본키 제약조건

PK는 테이블을 대표할 수 있는 데이터값으로써 한 테이블에 1개 이상이 될 수 없다.

다만 두개의 컬럼을 묶어서 하나의 PK로 설정하는건 가능하며, 이 방식은 테이블 레벨 방식으로만 지정이 가능하다.

기본키에는 절대로 null 값이 들어올 수 없으며

여러 컬럼을 묶어 PK로 설정한 경우도 둘 중 하나라도 NULL값이 들어온다면 오류가 발생한다.

회원 번호에 PK 제약조건을 부여하면 아래와 같다.

 

 

4-4-1) 컬럼레벨

CREATE TABLE MEM_PRIMARYKEY(
    MEM_NO NUMBER CONSTRAINT MEM_PK PRIMARY KEY, -- 컬럼레벨 방식
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    MEM_DATE DATE DEFAULT SYSDATE
);

컬럼레벨 방식임과 동시에 제약조건명(MEM_PK)도 같이 부여했을때 위처럼 사용한다.

 

 

4-4-2) 테이블 레벨

CREATE TABLE MEM_PRIMARYKEY(
    MEM_NO NUMBER,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    MEM_DATE DATE DEFAULT SYSDATE
    PRIMARY KEY(MEM_NO) -- 테이블레벨 방식
);

 

4-4-3) 두개의 컬럼을 하나의 PK로 설정 (=복합키)

CREATE TABLE MEM_PRIMARYKEY2(
    MEM_NO NUMBER,
    MEM_ID VARCHAR2(20),
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    MEM_DATE DATE DEFAULT SYSDATE,
    PRIMARY KEY(MEM_NO, MEM_ID) -- 괄호안에 묶을 컬럼을 기재
);

 

 

 

 

 

4-5) FK 외래키 제약조건

아래는 회원테이블(MEM)에서 회원등급테이블(MEM_GRADE)의 등급코드(GRADE_CODE)를 참조한다고 가정하여 쓴 예시다.

외래키 제약조건이 걸려있어도 NULL값은 들어갈 수 있다. 다만 참조하는 테이블에 없는 데이터 값은 들어갈 수 없다.

예를들어 등급코드는 A1부터 A9까지 존재하는데 C5라는 없는 코드를 입력하려 한다면  [ PARENT KEY NOT FOUND ] 라는 오류가 발생한다.

 

 

4-5-1) 컬럼레벨 방식

CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GRADE_ID CHAR(2) REFERENCES MEM_GRADE(GRADE_CODE), -- 컬럼레벨 방식
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15)
);

 

4-5-2) 테이블레벨 방식

CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GRADE_ID CHAR(2),
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15)
    FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) -- 테이블레벨 방식
);

 

 

4-5-3) 참조 데이터를 삭제 할 수 있을까?

회원테이블이 회원등급테이블을 참조하고 있다. 이해하기 편하게 회원은 자식테이블 회원등급은 부모테이블이라고 한다면, 자식테이블에서 쓰고 있는 데이터값은 부모 테이블에서 삭제할수가 없다. 삭제하고 싶다면 자식테이블에서 변경 및 삭제를 해서 참조하고 있는 관계를 모두 지워준 다음에야 삭제가 가능하다.

 

 

4-5-4) 삭제 옵션

#ON DELETE CASCADE
FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE CASCADE

위처럼 ON DELETE CASCADE라는 추가 옵션을 기재해주면 부모데이터 삭제시 해당 데이터를 쓰고 있는 자식데이터도 모두 지워준다.