11일차 # 9-17: Constraints

  * 종류

1) NN: NOT null

2) UK: 중복 불허

3) PK: = NN + UK, 테이블 당 한 개만

4) FK: null 또는 referenced column에 존재하는 값만 허용

5) CK: 조건

  * 정의: 테이블 생성시

1) column-level definition

2) table-level definition

drop table d1 purge;

drop table d2 purge;

create table d1 (

 deptno number(2)    primary key,

 dname  varchar2(20) not null,

 dloc   varchar2(20));

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

  * 제약조건에 대한 정보는 USER_CONSTRAINTS 뷰를 질의하면 알 수 있습니다.

SQL> desc user_constraints

SQL> select constraint_name, constraint_type, table_name

 2  from user_constraints

 3  where table_name = 'D1';

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

create table d2 (

 deptno number(2),

 dname  varchar2(20),

 dloc varchar2(20),

   primary key (deptno));

 

drop table d2 purge;

create table d2 (

 deptno number(2) constraint d2_deptno_pk primary key,

 dname  varchar2(20),

 dloc varchar2(20));

select constraint_name, constraint_type, table_name

from user_constraints

where table_name = 'D2';

drop table d2 purge;

create table d2 (

 deptno number(2),

 dname  varchar2(20),

 dloc   varchar2(20),

   constraint d2_deptno_pk primary key (deptno),

   constraint d2_dname_nn  not null (dname));

6행에 오류:

==> ORA-00904: : 부적합한 식별자

==> NOT NULL 제약조건은 Table-Level에서 정의할 수 없습니다.

                       컬럼 레벨에서 정의하거나

무슨 일이 있어도 테이블 레벨에서 정의하고 싶다면 check 제약조건을 사용해야 합니다.

create table d2 (

 deptno number(2),

 dname  varchar2(20),

 dloc   varchar2(20),

   constraint d2_deptno_pk primary key (deptno),

   constraint d2_dname_nn  check (dname is not null));

테이블이 생성되었습니다.

desc d1

이름                                                  널?      유형

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

DEPTNO                        NOT NULL NUMBER(2)

DNAME                         NOT NULL VARCHAR2(20)

DLOC                                   VARCHAR2(20)

desc d2

이름                                                  널?      유형

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

DEPTNO                        NOT NULL NUMBER(2)

DNAME                                  VARCHAR2(20)

DLOC                                   VARCHAR2(20)

==> dname 행에 NOT NULL이 없습니다.

insert into d1 values (10, null, 'Busan');

==> ORA-01400: NULL을 ("JAVA30"."D1"."DNAME") 안에 삽입할 수 없습니다

insert into d2 values (10, null, 'Busan');

==> ORA-02290: 체크 제약조건(JAVA30.D2_DNAME_NN)이 위배되었습니다

alter table d2 modify (dname not null);

desc d2;

insert into d1 values (10, 'SALES', 'Busan');

insert into d1 values (20, 'Executives', 'Seoul');

insert into d1 values (30, 'Advertisement', null);

insert into d2 values (10, 'SALES', 'Busan');

insert into d2 values (20, 'Executives', 'Seoul');

insert into d2 (deptno, dname) values (30, 'Advertisement');

commit;

select * from d1;

select * from d2;

drop table e1 purge;

drop table e2 purge;

create table e1 (

 empno  number(4)    constraint e1_empno_pk  primary key,

 ename  varchar2(20) constraint e1_ename_nn  not null,

 deptno number(2)    constraint e1_deptno_fk references d3(deptno));

==> 오류: ORA-00942: 테이블 또는 뷰가 존재하지 않습니다

create table e1 (

 empno  number(4)    constraint e1_empno_pk  primary key,

 ename  varchar2(20) constraint e1_ename_nn  not null,

 deptno number(2)    constraint e1_deptno_fk references d1(deptno),

 sal    number(8, 2));

create table e2 (

 empno  number(4),

 ename  varchar2(20),

 deptno number(2),

 sal    number(8, 2),

    constraint e2_empno_pk  primary key (empno),

    constraint e2_ename_nn  check(ename is not null),

    constraint e2_deptno_fk foreign key (deptno) references d2(deptno),

    constraint e2_sal_ck    check(sal > 1000));

alter table e1 modify (sal constraint e1_sal_ck check (sal > 1000));

insert into e1 values (1000, 'James', null, 2000);

==> null 값은 parent table의 값과 관계없이 insert 가능

insert into e1 values (2000, 'Adams', 40, 2500);

==> ORA-02291: 무결성 제약조건(JAVA30.E1_DEPTNO_FK)이 위배되었습니다- 부모 키가 없습니다

insert into e1 values (2000, 'Adams', 10, 2500);

insert into e1 values (3000, 'King', 20, 1000);

==> ORA-02290: 체크 제약조건(JAVA30.E1_SAL_CK)이 위배되었습니다

insert into e1 values (4000, 'Clark', 30, 4000);

commit;

insert into e2

 (select * from e1);

update e1

set sal = 1100

where empno = 4000;

insert into e2

 (select * from e1);

==> ORA-00001: 무결성 제약 조건(JAVA30.E2_EMPNO_PK)에 위배됩니다

select * from e1;

select * from e2;

  * 제약조건 정보 확인: user_constraints, all_constraints, dba_constraints

col table_name format a10

col constraint_name format a15

col constraint_type format a5

col search_condition format a20

select table_name, constraint_name

    , constraint_type, search_condition

from user_constraints

where table_name in ('D1', 'D2', 'E1', 'E2');

  * referenced 테이블 삭제

drop table e1 purge;

drop table e2 purge;

create table e1 (

 empno  number(4)    constraint e1_empno_pk  primary key,

 ename  varchar2(20) constraint e1_ename_nn  not null,

 deptno number(2)    constraint e1_deptno_fk references d1(deptno) on delete cascade,

 sal    number(8, 2) constraint e1_sal_ck    check (sal > 1000));

create table e2 (

 empno  number(4),

 ename  varchar2(20),

 deptno number(2),

 sal    number(8, 2),

    constraint e2_empno_pk  primary key (empno),

    constraint e2_ename_nn  check(ename is not null),

    constraint e2_deptno_fk foreign key (deptno) references d2(deptno) on delete set null,

    constraint e2_sal_ck    check(sal > 1000));

insert into e1 values (1000, 'James', null, 2000);

insert into e1 values (2000, 'Adams', 10, 2500);

insert into e1 values (3000, 'King', 20, 1100);

insert into e1 values (4000, 'Clark', 30, 4000);

insert into e2

 (select * from e1);

commit;

drop table d1 purge;

==> 에러 : ORA-02449: 외래 키에 의해 참조되는 고유/기본 키가 테이블에 있습니다

  * referenced 테이블에서 행 삭제

create table e3 (

 empno  number(4)    constraint e3_empno_pk  primary key,

 ename  varchar2(20) constraint e3_ename_nn  not null,

 deptno number(2)    constraint e3_deptno_fk references d1(deptno),

 sal    number(8, 2) constraint e3_sal_ck    check (sal > 1000));

insert into e3 select * from e1;

delete from e3

where empno = 2000;

select * from e3;

delete from d1

where deptno = 30;

==> ERROR at line 1:

   ORA-02292: integrity constraint (JAVA30.E3_DEPTNO_FK) violated - child record found

drop table e3 purge;

select * from d1;

select * from e1;

select * from e2;

select * from d2;

delete from d2

where deptno = 20

select * from d2;

select * from e2;