SooBlending

[Oracle] 제약조건 (Constraint) 본문

Programming/DataBase

[Oracle] 제약조건 (Constraint)

블랜더 2017. 11. 7. 17:36


<무결성 제약조건> Data integrity constraint rule

 - 테이블에 부적절한 자료가 입력(insert,update)되는 것을 방지하기 위해서

   테이블을 생성할 때 각 컬럼에 대해서 정의 하는 여러가지 규칙.


  1. NOT NULL

     해당 컬럼값으로 NULL을 허용하지 않음

     - 입력시 데이터를 무조건 받겠다!!

  2. UNIQUE

     테이블내에서 해당 컬럼값은 항상 유일무이한 값을 가질 것.

     - 중복허용하지 않겠다!!

     

  3. PRIMARY KEY(기본키)- (not null+unique 동시에 만족)

     해당컬럼값은 반드시 존재해야 하고 유일해야 한다는 조건.

     - 테이블내에서 서로 다른 행을 구분하기 위해서 사용!!

     

  4. FOREIGN KEY (외래키,참조키)

     해당컬럼의 값이 타컬럼의 값을 참조해야만 함

     즉, 참조되는 컬럼에 없는 값은 입력 불가.

     - 참조하고자 하는 테이블에서 설정(예: 사원테이블의 deptno에 참조키 설정)

     - 참조되는 쪽(부서테이블)의 컬럼은 반드시 unique하거나 또는 primary키 여야함!!

        

  5. CHECK

     해당 컬럼에 저장 가능한 데이터 값의 범위나 사용자 조건을 지정. 

     age컬럼: (1~100)

     gender컬럼: (남성,여성)

     

<컬럼레벨 정의 방법으로 제약조건 지정하기>

---> 컬럼레벨: 제약조건의 명시를 테이블 생성시 컬럼정의와 함께!!

 형식) 

      create table 테이블명(

                컬럼명 자료형 CONSTRAINT_TYPE

      );    

     

drop table dept2;


create table dept2(

   deptno number(2),

   dname  varchar2(15),

   loc    varchar2(15)

);


insert into dept2 values (10,  '영업부','서울');

insert into dept2 values (10,  '자재부','서울');

insert into dept2 values (null,'총무부','서울');

insert into dept2 values (10,  '영업부','서울');

     

SQL> select * from dept2;


    DEPTNO DNAME             LOC

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

        10 영업부                         서울

        10 자재부                         서울

        20 총무부                         서울     

        10 영업부                         서울

        

  ---> 첫행의 부서명을 '영업부' ---> '개발부'로 변경 ???

  

drop table dept2;


create table dept2(

   deptno number(2) primary key,

   dname  varchar2(15),

   loc    varchar2(15)

);                  


insert into dept2 values (10,  '영업부','서울');

insert into dept2 values (10,  '자재부','서울');

===> ORA-00001: unique constraint (SCOTT.SYS_C007002) violated


insert into dept2 values (20,  '자재부','서울');

insert into dept2 values (null,'총무부','서울');

===> ORA-01400: cannot insert NULL into ("SCOTT"."DEPT2"."DEPTNO")


insert into dept2 values (30,'총무부','서울');

insert into dept2 values (40,  '영업부','서울');


SQL> select * from dept2;


    DEPTNO DNAME             LOC

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

        10 영업부            서울

        20 자재부            서울

        30 총무부            서울

        40 영업부            서울


  ---> 첫행의 부서명을 '영업부' ---> '개발부'로 변경 ???

  update dept2 set dname='개발부'

  where deptno=10;

  

SQL> select * from dept2;


    DEPTNO DNAME             LOC

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

        10 개발부             서울

        20 자재부             서울

        30 총무부             서울

        40 영업부             서울  

  

<컬럼레벨(컬럼선언시) 제약설정과 함께 이름지정!!>

  형식) 

      create table 테이블명(

             컬럼명 자료형  [CONSTRAINT 제약명] 제약타입

      );

  

   ※참고: 제약명 ---> 테이블명_컬럼명_제약


drop table dept2;


create table dept2(

   deptno number(2)   CONSTRAINT dept2_deptno_pk   primary key,

   dname  varchar2(15),

   loc    varchar2(15)

); 

---> ORA-00001: unique constraint (SCOTT.DEPT2_DEPTNO_PK) violated


==============================================================

<제약종류(constraint_type)>

1. not null

  - 데이터를 반드시 입력!!

  - 수정시 null로 변환 불가

  

  drop table emp2;

  create table emp2

  (

     empno number(4),

     ename varchar2(15),

     sal   number(7,2),

     job   varchar2(15)

  );

  

  insert into emp2  (empno, ename, sal, job)

             values (7000, '홍길동', 2000, '사원');

               

  insert into emp2  (ename, sal, job)

             values ('길라임', 2000, '사원');

               

  insert into emp2  (ename, sal, job)

             values ('길라임', 2000, '사원');  


SQL> select * from emp2;


     EMPNO ENAME           SAL            JOB  

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

      7000 홍길동          2000           사원

      7002 길라임          2000           사원

      7004 길라임          2000           사원


  drop table emp2;

  create table emp2

  (

     empno number(4) constraint emp2_empno_nn not null,

     ename varchar2(15),

     sal   number(7,2),

     job   varchar2(15)

  );

  

insert into emp2  (empno, ename, sal, job)

             values (7000, '홍길동', 2000, '사원');

               

insert into emp2  (ename, sal, job)

             values ('길라임', 2000, '사원');

===> ORA-01400: cannot insert NULL into ("SCOTT"."EMP2"."EMPNO")


insert into emp2  (empno, ename, sal, job)

             values (7002,'길라임', 2000, '사원');

             

insert into emp2  (empno, ename, sal, job)

             values (7004,'길라임', 2000, '사원');


SQL> select * from emp2;


     EMPNO ENAME           SAL            JOB  

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

      7000 홍길동           2000           사원

      7002 길라임           2000           사원

      7004 길라임           2000           사원


insert into emp2  (empno, ename, sal, job)

             values (7006,'김주원', 2000, '사원');

             

insert into emp2  (empno, ename, sal, job)

             values (7006,'김주원', 2000, '사원');

             

     EMPNO ENAME           SAL            JOB  

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

      7000 홍길동           2000           사원

      7002 길라임           2000           사원

      7004 길라임           2000           사원

      7006 김주원           2000           사원

      7006 김주원           2000           사원


2. unique

  - 중복된 데이터를 방지

  

  drop table emp2;

  create table emp2

  (

     empno number(4), -- CONSTRAINT   emp2_empno_uk   unique, --컬럼레벨

     ename varchar2(15),

     sal   number(7,2),

     job   varchar2(15),

      -- 테이블 레벨(컬럼선언이 끝나고 난 후)에 제약을 정의!!

     CONSTRAINT   emp2_empno_uk   unique (empno)

  );             

  

                        EMPNO

insert into emp2 values (7000,'홍길동', 2000, '사원');  

insert into emp2 values (7002,'길라임', 2000, '사원');  

insert into emp2 values (7004,'길라임', 2000, '사원');  

insert into emp2 values (7006,'김주원', 2000, '사원');  

insert into emp2 values (7006,'김주원', 2000, '사원');  

==> ORA-00001: unique constraint (SCOTT.EMP2_EMPNO_UK) violated  


insert into emp2 values (NULL,'김유신', 2000, '사원');  

  ==> 1 row created.

  

insert into emp2 values (NULL,'김유신', 2000, '사원');  

  ==> 1 row created. 

  ==> UNIQUE : 중복된 데이터를 방지하지만  NULL중복 체크 못함.

  

  

3. primary key (기본키, 주키)

  - not null AND unique를 동시에 만족하는 데이터 입력

  - 테이블내의 서로 다른 행을 구분하는 목적으로 사용.

  - 테이블내에 단 한 개의 primary key만 존재!!

  

 drop table emp2;

 

 create table emp2

  (

     empno number(4) primary key, 

     ename varchar2(15) primary key,

     sal   number(7,2),

     job   varchar2(15)

  ); 

==> ORA-02260: table can have only one primary key


 drop table emp2;

 

 create table emp2

  (

     empno number(4), --constraint emp2_empno_pk  primary key,

     ename varchar2(15),

     sal   number(7,2),

     job   varchar2(15) --,

     -- constraint emp2_empno_pk primary key (empno)

  );


<제약추가> : 이미 테이블 객체가 만들어진 상태에서 제약추가!!

alter table emp2

add constraint emp2_empno_pk primary key (empno);


insert into emp2 values (7000,'홍길동',3000,'사원');

==> 1 row created.


insert into emp2 values (7000,'홍길동',3000,'사원');

==> ORA-00001: unique constraint (SCOTT.EMP2_EMPNO_PK) violated


insert into emp2 values (null,'홍길동',3000,'사원');

==> ORA-01400: cannot insert NULL into ("SCOTT"."EMP2"."EMPNO")


 drop table emp2;

 

 create table emp2

  (

     empno number(4), 

     ename varchar2(15),

     sal   number(7,2),

     job   varchar2(15)

  ); 


alter table emp2

add constraint emp2_pk primary key (empno, ename); 

==> Table altered. (empno와 ename을 조합한 한 개의 primary key설정!!)


insert into emp2 values (8000,'홍길동',3000,'사원');

 -->성공

insert into emp2 values (8000,'길라임',3000,'사원');

 -->성공

insert into emp2 values (8002,'길라임',3000,'사원');

 -->성공

 

insert into emp2 values (8002,'길라임',3000,'사원');

 --> ORA-00001: unique constraint (SCOTT.EMP2_PK) violated

 

문제) emp2테이블의 primary key(emp2_pk)를 삭제하시오.

  

  삭제1)

 alter table emp2

 drop constraint emp2_pk;

 --> Table altered.(변경 성공!!)

 

 alter table emp2

 add constraint emp2_pk primary key (empno);

 --> Table altered.(변경 성공!!)


시스템테이블 : user_XXs

         ----> user_constraints


 select table_name, constraint_name, constraint_type

 from user_constraints

 where table_name='EMP2';


 삭제2)

   alter table emp2

   drop primary key;

   ===> Table altered.


문제) emp2테이블을 새롭게 생성(empno, ename, sal, deptno컬럼)하고

    테이블 생성 후 empno에 primary key 설정을 하고

            ename, sal, deptno에 not null을 설정하시오.

            

   drop table emp2;

   create table emp2

   (

      empno number(4),

      ename varchar2(15),

      sal   number(7,2) ,

      deptno number(2)    

   );           

   

  alter table emp2 

  add constraint emp2_empno_pk primary key (empno);

  ==> 변경 성공!!

   또는

  alter table emp2 

  add primary key (empno);


  alter table emp2

  add constraint emp2_ename_nn not null (ename);

                               *

  ==> ORA-00904: : invalid identifier                               

  ==> add not null 안되는 이유?

        테이블내의 모든 컬럼은 특별한 설정(not null)을 하지 않아도 

                              null값이 기본으로 설정되어 있음!!

                              

<제약수정>

 ALTER TABLE 테이블명

 MODIFY 컬럼명 CONSTRAINT 제약명 NOT NULL | NULL;


 alter table emp2

 modify ename not null;


SQL> desc emp2

 Name                                      Null?    Type

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

 EMPNO                                     NOT NULL NUMBER(4)

 ENAME                                     NOT NULL VARCHAR2(15)

 SAL                                                NUMBER(7,2)

 DEPTNO                                             NUMBER(2)


 alter table emp2 modify sal not null;

 alter table emp2 modify deptno not null;


SQL> desc emp2

 Name                                      Null?    Type

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

 EMPNO                                     NOT NULL NUMBER(4)

 ENAME                                     NOT NULL VARCHAR2(15)

 SAL                                       NOT NULL NUMBER(7,2)

 DEPTNO                                    NOT NULL NUMBER(2)


문제) emp2테이블의 sal컬럼의 not null 제약을 삭제하시오!!

   alter table emp2

   drop constraint emp2_sal_nn;

      또는

   alter table emp2

   modify sal null;

          not null ---> null

   

SQL> desc emp2

 Name                                      Null?    Type

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

 EMPNO                                     NOT NULL NUMBER(4)

 ENAME                                     NOT NULL VARCHAR2(15)

 SAL                                                NUMBER(7,2)

 DEPTNO                                    NOT NULL NUMBER(2)


4. foreign key(외래키, 참조키)

 - 참조하는 테이블의 컬럼데이터 범위내의 데이터만 입력.

 - 예) 사원들은 반드시 존재하는 부서의 번호를 속성값으로 가져와야 함!!

      ===> 사원테이블(emp)에서 사원에 대한 정보를 입력, 수정시

                    부서테이블(dept)로 부터 존재하는 부서번호인지를 참조, 확인 해야함.

 - 참조되는 컬럼은 unique 또는 primary key설정이 되어 있어야 함.                      

                                                  

 사원테이블(deptno 입력, 수정) -------> 부서테이블(deptno 참조)

                                     10  

                                     20  

                                     30  

                                     40


 --> 컬럼레벨

   create table 테이블명emp

   (

          컬럼명 자료형 [constraint 제약명] REFERENCES 참조테이블명(참조컬럼명)

     deptno number(2) REFERENCES dept(deptno)

   );

 

 --> 테이블레벨

   create table 테이블명emp

   (

           컬럼명1 자료형, 

           컬럼명2 자료형, 

           컬럼명3 자료형,

      [constraint 제약명] FOREIGN KEY (컬럼명2)

                         REFERENCES 참조테이블명(참조컬럼명)

       ----> foreign key (deptno) references dept(deptno)                         

   );


 --> 테이블 변경

  alter table 테이블명

  ADD  [constraint 제약명] foreign key (deptno) 

                        references dept(deptno);

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

 참조되는 테이블(부모테이블) dept2

 drop table dept2;

 create table dept2

 as select * from dept;

 ----> 구조와 데이터를 복사 (제약은 제외!!)


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

 참조하는 테이블(자식테이블) emp2

  drop table emp2;

  create table emp2(

     empno number(4),

     ename varchar2(15),

     sal number(7,2),

     deptno number(2)

  );

 

 문제) emp2테이블내의 empno에 primary key설정,

                 deptno에 foreign key설정을 하시오.

                 

  alter table emp2

  add constraint emp2_empno_pk primary key (empno);                 

   ---> Table altered.(변경성공!!)

   

  alter table emp2

  add constraint emp2_deptno_fk

      foreign key (deptno) -- 현재테이블의 컬럼

      references dept2(deptno);  -- 참조테이블의 컬럼


----> ORA-02270: no matching unique or primary key for this column-list

      참조되는 컬럼(dept2테이블--->deptno컬럼)은 unique 또는 primary key 설정

      

   - dept2의 deptno: unique설정

   alter table dept2

   add constraint dept2_uk unique( deptno);

   

     또는                                         

  

   alter table dept2

   add constraint dept2_pk primary key( deptno);

   ==> 변경성공!!


  alter table emp2

  add constraint emp2_deptno_fk

      foreign key (deptno) -- 현재테이블의 컬럼

      references dept2(deptno);  -- 참조테이블의 컬럼

  ==> 변경성공!!

'Programming > DataBase' 카테고리의 다른 글

[Oracle] PL/SQL  (0) 2017.11.09
[Oracle] 시퀀스(Sequence)  (0) 2017.11.09
[Oracle] 뷰 (View) 단일뷰/복합뷰  (0) 2017.11.08
[Oracle] 트랜잭션(Transaction) COMMIT/ROLLBACK  (2) 2017.11.07
[Oracle] Merge 테이블 합치기  (2) 2017.11.07
Comments