테이블의 제약조건

3.2 테이블의 제약조건

(제약조건? 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서 규칙을 적용해 놓는다.

즉, 테이블안에서 데이터의 성격을 정의하는 것)

 

제약조건

not null 조건 : 컬럼을 필수 필드화 시킬 때 사용한다.

SQL> CREATE TABLE emp(
ename VARCHAR2(20) CONSTRAINT emp_nn_ename NOT NULL );
위와같이테이블을생성하면ename 컬럼에는꼭데이터를입력해야만합니다.
-여기서emp_nn_ename은(테이블이름_제약조건이름_컬럼이름) 형식으로CONSTRAINT NAME을정의합니다.
-CONSTRAINT NAME은USER_CONSTRAINTS 뷰(VIEW)를통해서확인할수있습니다.
SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTSWHERE TABLE_NAME ='EMP' ;
CONSTRAINT_NAME
-----------------------
emp_nn_ename 이런식으로제약조건의이름을확인할수있습니다.

 

unique 조건

UNIQUE 조건: 데이터의유일성을보장합니다. (중복되는데이터가존재할수없습니다.)
유니크조건을생성하면자동으로index가생성됩니다.
SQL> ALTER TABLEemp ADD CONSTRAINTemp_uk_deptno UNIQUE(deptno) ;
테이블이변경되었습니다.
-위와같이제약조건을생성하면deptno 컬럼에중복된데이터가들어갈수없습니다.
--제약조건의삭제
SQL>ALTER TABLEemp DROP CONSTRAINTemp_uk_deptno ;

 

check 조건 : 컬럼의 값을 어떤 특정 범위로 제한할 수 있습니다.

SQL>ALTER TABLE emp ADD CONSTRAINT emp_ck_comm CHECK (comm >= 10 AND comm <= 100000) ;
테이블이변경되었습니다.
-comm컬럼은체크조건에서제한을하고있으므로1에서100까지의값만을가질수있습니다.
또체크조건에서는IN 연산자를사용할수있습니다.

 

default(컬럼 기본값)지정

데이터입력시에입력을하지않아도지정된값이디폴트로입력이됩니다.
SQL>CREATE TABLE emp(
hiredate DATE DEFAULT SYSDATE ) ;
위와같이디폴트값을설정하면hiredate 컬럼에INSERT를하지않아도오늘날짜가들어갑니다.

SQL>ALTER TABLE emp DROP CONSTRAINT emp_ck_comm ; --제약조건의삭제
테이블이변경되었습니다.
SQL> ALTER TABLE emp --IN 연산자를이용해서CHECK 제약조건을생성
ADD CONSTRAINT emp_ck_comm
CHECK (comm IN (10000,20000,30000,40000,50000)) ;
테이블이변경되었습니다.
-comm 컬럼은10000,20000,30000,40000,50000의값만을가질수있습니다.

 

primary key(기본키) 지정

PRIMARY KEY : Primary Key(기본키)는UNIQUE 와NOT NULL의결합과같습니다.
-기본키는그데이터행을대표하는컬럼으로서의역할을수행하여다른테이블에서외래키들이참조할수있는키로서의자격을가지고있습니다. 이를참조무결성이라합니다.
-UNIQUE 조건과마찬가지로기본키를정의하면자동으로인덱스를생성하며그이름은기본키제약조건의이름과같습니다.
SQL>CREATE TABLE emp(
empno NUMBER CONSTRAINT emp_pk_empno PRIMARY KEY) ;
위와같이제약조건을설정하면empno 컬럼에UNIQUE 제약조건과NOT NULL제약조건을가지게됩니다.

 

foreign key(외래 키) 지정

FOREIGN KEY(외래키)지정: 기본키를참조하는컬럼또는컬럼들의집합입니다.
-외래키를가지는컬럼의데이터형은외뢰키가참조하는기본키의컬럼과데이터형이일치해야합니다.
이를어기면참조무결성제약에의해테이블을생성할수없습니다.
-외래키에의해참조되고있는기본키는삭제할수없습니다.
-ON DELETE CASCADE 연산자와함께정의된외래키의데이터는그기본키가삭제될때같이삭제됩니다.
SQL>ALTER TABLE emp ADD CONSTRAINTemp_fk_deptno
FOREIGN KEY(deptno) REFERENCESdept(deptno)
테이블이변경되었습니다.
위와같이제약조건을생성하면emp 테이블의deptno 컬럼은dept 테이블에deptno 컬럼을참조하는외래키를가지게됩니다.



////////////////////////////////////////////////////////////////

제약조건 (Constraint)***
    - 테이블의 해당 컬럼에 원치않는 데이터를 입력/변경/삭제되는 것을 방지하기 위해 테이블 생성시
      또는 변경(alter)시 설정하는 조건

    (1) 종류
      1) PRIMARY KEY(식별키)
        - 하나의 테이블에 오직 하나만 존재하며 자동으로 인덱스가 부여되는
          하나의 ROW데이터를 대표하는 컬럼
      2) FOREIGN KEY(참조키/외래키)
        - 부모 테이블의 PK를 참조하는 컬럼
      3) UNIQUE KEY(유일키)
        - PK가 아니더라도 컬럼의 모든 값이 유일하게 해야 하는 경우에 사용되는 제약조건.
          즉, 중복데이터를 허용하지 않는 컬럼.
          PK는 NULL을 허용하지 않지만 유일키는 NULL을 허용한다.
      4) CHECK
        - 조건에 맞는 데이터만 입력되도록 조건을 부여한 제약조건.
          조건으로는 데이터의 값의 범위, 문자값 등으로 설정할 수 있다.
      5) NOT NULL
        - NULL이 입력되어서는 안되는 컬럼에 부여하는 제약조건으로
          COLUMN-LEVEL 으로만 부여할 수 있는 제약 조건

      -- 아래와 같은 테이블을 만들어보자.
      create table con_dept(
              deptno NUMBER(2) primary key,
              dname varchar(10) DEFAULT '홍길동',
              loc varchar(4) check(loc in('서울', '부산')));
      create table con_emp(
              empno number(4) primary key,
              name varchar2(10) not null,
              jumin char(13) unique,
              deptno number(2) references con_dept(deptno));

      insert into con_dept(deptno, loc) values(1,'서울');
      insert into con_emp values(100,'누구니','1234567891234',1);

      -- 제약조건 이름 알아보기
      select constraint_name from user_constraints;
      -- 테이블 삭제
      drop table con_dept;  --> 오류발생. 자식테이블이 존재하므로
      방법1) 자식테이블을 먼저 삭제한 후 부모테이블을 삭제한다.
             drop table con_emp;    --> 자식테이블 삭제
             drop table con_dept;   --> 부모테이블 삭제
      방법2) CASCADE 옵션을 사용한다.
             drop table con_dept CASCADE CONSTRAINTS;

      << 제약조건에 이름 부여하기 - COLUMN LEVEL >>
      create table con_dept(
              deptno NUMBER(2) CONSTRAINT con_dept_deptno_pk primary key,
              dname varchar(10) DEFAULT '홍길동',
              loc varchar(4) CONSTRAINT con_dept_loc_ck check(loc in('서울', '부산')));
      create table con_emp(
              empno number(4)
              CONSTRAINT con_emp_empno_pk primary key,
              name varchar2(10)
              CONSTRAINT con_emp_name_nn not null,
              jumin char(13)
              CONSTRAINT con_emp_jumin_uq unique,
              deptno number(2)
              CONSTRAINT con_emp_deptno_fk references con_dept(deptno));

      select constraint_name from user_constraints;

      << 제약조건에 이름 부여하기2 - TABLE LEVEL >>
      create table con_dept(
        deptno number(2),
        dname varchar2(10),
        loc varchar2(4),
        CONSTRAINT con_dept_deptno_pk primary key(deptno),
        CONSTRAINT con_dept_loc_ck check(loc in('서울','부산')));

      -- NOT NULL 조건은 컬럼레벨로만 부여할 수 있다.
      create table con_emp(
        empno number(2),
        name varchar2(10) CONSTRAINT con_emp_name_nn NOT NULL,
        jumin char(13),
        deptno number(2),
        CONSTRAINT con_emp_empno_pk PRIMARY KEY(empno),
        CONSTRAINT con_emp_jumin_uq UNIQUE(jumin),
        CONSTRAINT con_emp_deptno_fk FOREIGN KEY(deptno) REFERENCES con_dept(deptno));

      * 제약조건을 수정할 수는 없고 삭제만 가능하다.
      -- 제약조건 삭제
      ALTER TABLE con_emp DROP CONSTRAINT con_emp_empno_pk;
      -- 제약조건 추가
      ALTER TABLE con_emp ADD(CONSTRAINT con_com_empno_pk PRIMARY KEY(empno));

      insert into con_dept values(1,'영업부','서울');
      insert into con_emp values(10,'홍길동','1234567891234',1);

      -- 자식테이블이 참조하고 있다면 삭제할 수 없다.
      delete from con_dept where deptno = 1;

      << ON DELETE CASCADE 옵션 지정하기 >>
      create table con_emp(
            empno number(2),
            name varchar2(10) CONSTRAINT con_emp_name_nn NOT NULL,
            jumin char(13),
            deptno number(2),
            CONSTRAINT con_emp_empno_pk PRIMARY KEY(empno),
            CONSTRAINT con_emp_jumin_uq UNIQUE(jumin),
            CONSTRAINT con_emp_deptno_fk FOREIGN KEY(deptno)
                    REFERENCES con_dept(deptno) ON DELETE CASCADE);

      delete from con_dept where deptno = 1;  --> ON DELETE CASCADE 옵션을 지정했으므로
                                                  부모의 데이터 삭제 가능!

/////////////////////////////////////////////////////////////////////////////////////////

#1. Column level constraint

 

drop table t_emp purge;
drop table t_dept purge;

 

create table t_dept
(deptno number(2)   constraint t_dept_deptno_pk primary key,
 dname varchar2(30) not null,
 loc varchar2(10));

  -- number(2) (자료형) contraint (제약) t_dept_deptno_pk (제약조건이름)

     primary key (제약조건)

 

create table t_emp
(empno number(4)     constraint t_emp_empno_pk  primary key,
 ename varchar2(30)  constraint t_emp_ename_nn  not null,
 sal number          constraint t_emp_sal_ck    check(sal >= 100),
 hphone varchar2(11) constraint t_emp_hphone_uk unique,
 deptno number(2)    constraint t_emp_deptno_fk references t_dept(deptno)
                     constraint t_emp_deptno_nn not null);

 

※ select owner, table_name, tablespace_name, status, blocks, freelists, user_stats

    from dba_tables where owner = 'SCOTT'

OWNER      TABLE_NAME    TABLESPACE_NAME    STATUS    BLOCKS  FREELISTS  USE --------- ---------------- ------------------------ -------- ---------- ---------- --- SCOTT        DEPT                 USERS                VALID        5                    NO SCOTT        EMP                  USERS                VALID        5                    NO SCOTT        BONUS               USERS                VALID        0                    NO SCOTT        SALGRADE           USERS                VALID        5                    NO SCOTT        FLM_TBL              FLM_TBS             VALID        1          1         NO SCOTT        T_DEPT               USERS                VALID                             NO SCOTT        T_EMP                USERS                VALID                             NO

 

※ select owner,status, table_name, constraint_name, r_constraint_name, constraint_type

    from user_constraints where owner = 'SCOTT'

OWNER  STATUS  TABLE_NAME CONSTRAINT_NAME  R_CONSTRAINT_NAME     C ------- -------- -------------- -------------------- --------------------- ----- SCOTT  ENABLED  T_EMP       T_EMP_ENAME_NN                                   C SCOTT  ENABLED  T_EMP       T_EMP_SAL_CK                                       C SCOTT  ENABLED  T_EMP       T_EMP_DEPTNO_FK    T_DEPT_DEPTNO_PK        R SCOTT  ENABLED  EMP         FK_DEPTNO            PK_DEPT                     R SCOTT  ENABLED  DEPT        PK_DEPT                                              P SCOTT  ENABLED  EMP         PK_EMP                                                P SCOTT  ENABLED  T_DEPT      T_DEPT_DEPTNO_PK                                 P SCOTT  ENABLED  T_EMP       T_EMP_EMPNO_PK                                    P SCOTT  ENABLED  T_EMP       T_EMP_HPHONE_UK

 

※ select table_owner, index_name, index_type, table_name, table_type, tablespace_name

    from user_indexes where table_owner = 'SCOTT'

TABLE_OWNER INDEX_NAME  INDEX_TYPE  TABLE_NAME  TABLE_TYPE TABLESPACE_NAME -------------- ------------- ------------ -------------- ------------ ----------------- SCOTT          T_EMP_EMPNO_PK    NORMAL    T_EMP           TABLE       USERS SCOTT          T_EMP_HPHONE_UK   NORMA     T_EMP           TABLE       USERS SCOTT          T_DEPT_DEPTNO_PK  NORMAL    T_DEPT          TABLE       USERS SCOTT          PK_EMP               NORMAL      EMP             TABLE       USERS SCOTT          PK_DEPT              NORMAL      DEPT            TABLE       USERS

 

insert into t_dept values (10, 'A', 'A');
insert into t_dept values (10, 'B', 'B');

SCOTT@js> insert into t_dept values (10, 'A', 'A'); 1 row created. SCOTT@js> insert into t_dept values (10, 'B', 'B'); insert into t_dept values (10, 'B', 'B') * ERROR at line 1: ORA-00001: unique constraint (SCOTT.T_DEPT_DEPTNO_PK) violated

 

select * from t_dept;

#2. Table level constraint

drop table t_emp purge;
drop table t_dept purge;

 

create table t_dept
(deptno number(2),
 dname varchar2(30),
 loc varchar2(10),
   constraint t_dept_deptno_pk primary key(deptno));

Name                         Null?       Type           ----------------------- ---------- -------------------------------------     1      DEPTNO                   NOT NULL    NUMBER(2)     2      DNAME                                   VARCHAR2(30)     3      LOC                                      VARCHAR2(10)

 

create table t_emp                                               /* 사원 테이블 */
(empno number(4),                                                       /* 사번 */
 ename varchar2(30),
 sal number,
 hphone varchar2(11),
 deptno number(2),
 constraint t_emp_empno_pk  primary key(empno),
 constraint t_emp_ename_nn  check (ename is not null),
 constraint t_emp_sal_ck    check (sal >= 100),
 constraint t_emp_hphone_uk unique (hphone),
 constraint t_emp_deptno_fk foreign key (deptno) references t_dept (deptno));

Name                        Null?      Type            ----------------------- -------- ----------------------------------     1      EMPNO                    NOT NULL    NUMBER(4)     2      ENAME                                   VARCHAR2(30)     3      SAL                                      NUMBER     4      HPHONE                                 VARCHAR2(11)     5      DEPTNO                                  NUMBER(2)

 

comment on table  t_emp is 'SAWON TABLE';
comment on column t_emp.empno is 'SABUN';

 -- 테이블과 컬럼에 각각 comment 를 입력.

 

※ select * from user_tab_comments where table_name = 'T_EMP'

TABLE_NAME   TABLE_TYPE  COMMENTS ------------- ----------- ------------------ T_EMP            TABLE       SAWON TABLE

 

※ select * from user_col_comments where table_name = 'T_EMP' and column_name = 'EMPNO'

TABLE_NAME   COLUMN_NAME   COMMENTS ------------ --------------- -------------- T_EMP          EMPNO            SABUN

 

※ select owner, table_name, tablespace_name, status, blocks, freelists, user_stats

    from dba_tables where owner = 'SCOTT'

OWNER      TABLE_NAME    TABLESPACE_NAME    STATUS    BLOCKS  FREELISTS  USE --------- ---------------- ------------------------ -------- ---------- ---------- --- SCOTT        DEPT                 USERS                VALID        5                    NO SCOTT        EMP                  USERS                VALID        5                    NO SCOTT        BONUS               USERS                VALID        0                    NO SCOTT        SALGRADE           USERS                VALID        5                    NO SCOTT        FLM_TBL              FLM_TBS             VALID        1          1         NO SCOTT        T_DEPT               USERS                VALID                             NO SCOTT        T_EMP                USERS                VALID                             NO

 

※ select owner,status, table_name, constraint_name, r_constraint_name, constraint_type

    from user_constraints where owner = 'SCOTT'

OWNER  STATUS  TABLE_NAME CONSTRAINT_NAME  R_CONSTRAINT_NAME     C ------- -------- -------------- -------------------- --------------------- ----- SCOTT  ENABLED  T_EMP       T_EMP_ENAME_NN                                   C SCOTT  ENABLED  T_EMP       T_EMP_SAL_CK                                       C SCOTT  ENABLED  T_EMP       T_EMP_DEPTNO_FK    T_DEPT_DEPTNO_PK        R SCOTT  ENABLED  EMP         FK_DEPTNO            PK_DEPT                     R SCOTT  ENABLED  DEPT        PK_DEPT                                              P SCOTT  ENABLED  EMP         PK_EMP                                                P SCOTT  ENABLED  T_DEPT      T_DEPT_DEPTNO_PK                                 P SCOTT  ENABLED  T_EMP       T_EMP_EMPNO_PK                                    P SCOTT  ENABLED  T_EMP       T_EMP_HPHONE_UK

 

※ select table_owner, index_name, index_type, table_name, table_type, tablespace_name

    from user_indexes where table_owner = 'SCOTT'

TABLE_OWNER INDEX_NAME  INDEX_TYPE  TABLE_NAME  TABLE_TYPE TABLESPACE_NAME -------------- ------------- ------------ -------------- ------------ ----------------- SCOTT          T_EMP_EMPNO_PK    NORMAL    T_EMP           TABLE       USERS SCOTT          T_EMP_HPHONE_UK   NORMA     T_EMP           TABLE       USERS SCOTT          T_DEPT_DEPTNO_PK  NORMAL    T_DEPT          TABLE       USERS SCOTT          PK_EMP               NORMAL      EMP             TABLE       USERS SCOTT          PK_DEPT              NORMAL      DEPT            TABLE       USERS

 

insert into t_dept values (10, 'A', 'A');
insert into t_dept values (10, 'B', 'B');

SCOTT@js> insert into t_dept values (10, 'A', 'A'); 1 row created. SCOTT@js> insert into t_dept values (10, 'B', 'B'); insert into t_dept values (10, 'B', 'B') * ERROR at line 1: ORA-00001: unique constraint (SCOTT.T_DEPT_DEPTNO_PK) violated

 

select * from t_dept;

-----------------------------------------------------------------------------------------

  ☆ create table t_jumin
      (no  number, ju1 date, ju2 number, constraint t_jumin_ju1_ju2_uk unique (ju1, ju2));

   -- 두개 이상의 컬럼을 조합해서 하나의 제약을 선언할 경우는 반드시

       table level constraint로 생성해야 한다.

Name                                       Null?     Type  ---------------------------------- -------- --------------------------------------  NO                                                   NUMBER  JU1                                                   DATE  JU2                                                   NUMBER