오라클 정규 교재 영문 책 : D49996GC20_sg2 ---> 페이지 번호 80
- 제약이 필요한 이유가 뭔가 ? " 데이터의 품질을 높여서 정확한 데이터 분석을
위해서 입니다 " - 제약이 테이블에 걸면 어떤 일이 벌어지는가 ? 테이블에 데이터를 함부로 입력, 수정, 삭제 할 수 없게 됩니다.
■ PRIMARY KEY 제약
- 중복된 데이터를 입력 또는 수정 못하게
- null 을 입력 못하게
1. 테이블을 만들 때
- column level
create table emp23
( empno number(10) constraint emp23_empno_pk primary key,
ename varchar2(10),
sal number(10) );
select table_name, constraint_name, constraint_type
from user_constraints
where table_name='EMP23';
- 중복된 데이터를 emp23에 empno 에 입력해 봅니다.
insert into emp23 values( 1111, 'scott', 3000); <--- 입력가능
insert into emp23 values( 2222, 'allen', 2000 ); <--- 입력가능
insert into emp23 values( 1111, 'james', 4000 ); <--- 입력 불가능
ORA-00001: 무결성 제약 조건(C##SCOTT.EMP23_EMPNO_PK)에 위배됩니다
- null 값을 입력해봅니다.
insert into emp23 values( null, 'king', 5000 );
ORA-01400: NULL을 ("C##SCOTT"."EMP23"."EMPNO") 안에 삽입할 수 없습니다.
- table level
create table emp24
( empno number(10),
ename varchar2(10),
sal number(10),
constraint emp24_empno_pk primary key(empno) );
- 중복된 data 를 입력 시도
insert into emp24 values( 1111, 'scott', 3000); <--- 입력가능
insert into emp24 values( 2222, 'allen', 2000 ); <--- 입력가능
insert into emp24 values( 1111, 'james', 4000 ); <--- 입력 불가능
- null 값을 입력 시도
insert into emp24 values( null, 'king', 5000 ); <--- 입력 불가
※ primary key 나 unique 제약을 걸면 자동으로 생성되는것 ?
답: 인덱스 (index)
- 방금 만든 emp24 테이블에 empno 에 인덱스가 있는지 확인하시오 !
select index_name, uniqueness
from user_indexes
where table_name='EMP24';
EMP24_EMPNO_PK UNIQUE
↑
인덱스 이름이 제약이름과 같습니다.
문제607. 다음의 테이블을 생성하는데 empno 에 primary key 제약을 걸고
생성하시오. ( 제약이름을 주지 말고 생성하시오)
create table emp25
( empno number(10) primary key,
ename varchar2(10),
sal number(10) );
select table_name, constraint_name, constraint_type
from user_constraints
where table_name='EMP25';
EMP25 SYS_C0053250 P
이렇게 제약을 생성하면 어느 테이블에 어느 컬럼에 어느 제약인지 확인하기
어렵습니다. 그래서 제약이름을 주고 만드는게 바람직합니다.
공공기업의 DB를 1년에 2번 받기 때문에 이런 이름 관리를 잘 해줘야합니다.
*** primary key 를 생성 했을때 인덱스 이름이 제약 이름이 되지않게 하는 방법**
create table emp27
( empno number(10),
ename varchar2(10),
sal number(10),
**constraint emp27_empno_pk primary key(empno)
using index ( create index emp27_empno_idx
on emp27(empno) )**
);
- 데이터 사전에서 제약 이름 확인
select table_name, constraint_name, constraint_type
from user_constraints
where table_name='EMP27';
- 데이터 사전에서 인덱스 이름 확인
select index_name, uniqueness
from user_indexes
where table_name='EMP27';
2. 테이블이 만들어진 후에
create table emp29
( empno number(10),
ename varchar2(10),
sal number(10) );
**alter table** emp29
**add constraint** emp29_empno_pk primary key(empno);
select table_name, constraint_name, constraint_type
from user_constraints
where table_name ='EMP29';문제608. emp 테이블에 empno 에 primary key 제약을 거시오 !
alter table emp
add constraint emp_empno_pk primary key(empno);
※ emp 테이블에 empno 에 중복된 데이터도 없고 null 값도 없기 때문에
제약이 잘 걸린것 입니다.
문제609. dept 테이블에 다음의 데이터를 입력하고 나서 deptno 에
primary key 제약을 거시오.
insert into dept(deptno, dname, loc)
values( 20, 'HR', 'SEOUL' );
commit;
alter table dept
add constraint dept_deptno_pk primary key(deptno);
ORA-02437: (C##SCOTT.DEPT_DEPTNO_PK)을 검증할 수 없습니다.
문제610. dept 테이블에 deptno 에 중복된 데이터가 무엇인지 출력하시오 !
(초급 dba 와 중급 dba 의 차이를 확인할 수 있는 SQL )
select
from dept
where deptno in ( SELECT deptno
from dept
group by deptno
having count() >= 2 );
■ not null 제약
null 값이 입력되지 못하게 거는 제약
- 테이블 생성시
- table level : 불가능
- column level : 가능
- 테이블 생성후
문제611. 아래의 테이블을 생성하는데 ename 에 not null 제약을 걸어서
생성하시오 !
create table emp611
( empno number(10),
ename varchar2(10) constraint emp611_ename_nn not null,
sal number(10) );
select table_name, constraint_name, constraint_type
from user_constraints
where table_name='EMP611';
- null 값을 EMP611 의 ename 에 입력하고 입력 안되는지 확인하세요 insert into emp611 values( 1111, 'scott', 3000);
insert into emp611 values( 2222, null, 2000 ); ORA-01400: NULL을 ("C##SCOTT"."EMP611"."ENAME") 안에 삽입할 수 없습니다`
- 테이블 레벨로 not null 제약이 만들어지는지 확인합니다. create table emp612
( empno number(10),
ename varchar2(10),
sal number(10) ,
constraint emp612_ename_nn not null(ename) ); ORA-00904: : 부적합한 식별자 <--- 오류 나면서 수행 안됩니다. ※ not null 을 제외하고 다른 제약들은 전부 table level, column level 로
다 가능한데 not null 만 column level 로만 가능합니다.
문제612. 테이블 생성후에 not null 제약을 생성하시오 !
create table emp_nn_test
( empno number(10),
ename varchar2(10),
sal number(10) );alter table emp_nn_test
modify ename constraint emp_nn_test_nn not null ;
※ not null 제약만 add 가 아니라 modify 입니다. → column level 밖에 안되니깐 그런건가?
문제613. 우리반 테이블(emp17) 테이블의 ename 에 not null 제약을 거시오
alter table emp17
modify ename constraint emp17_ename_nn not null;
문제614. emp 테이블에 mgr 에 not null 제약을 거시오 !
alter table emp
modify mgr constraint emp_mgr_nn not null;
ORA-02296: (C##SCOTT.EMP_MGR_NN) 사용으로 설정 불가
null 값이 하나라도 있으면 not null 제약이 걸리지 않습니다.
[7월 14일 점심시간 문제] 수업시간에 배운 인덱스의 구조를 이해했는지 확인하는 문제
1. 먼저 명령 프롬프트 창을 열고 emp 테이블을 초기화합니다.
SQL> @init_emp.sql
2. 다음의 인덱스를 생성하세요
SQL> create index emp_deptno_sal
on emp(deptno, sal);
3. 아래의 SQL을 튜닝하세요.
튜닝전: select deptno, max(sal)
from emp
where deptno = 20
group by deptno;
튜닝후: 작성할 SQL은 그룹함수와 group by 절 사용하지 말고 emp_deptno_sal 인덱스를 이용해서
똑같은 결과를 출력하게 하세요.
select /*+ index_desc(emp emp_deptno_sal)*/ deptno, sal
from emp
where deptno = 20 and rownum = 1;
▣ 105 데이터의 품질 높이기 2(UNIQUE)
" 중복된 data 를 허용 못하게 하는 제약 "
문법: 1. 테이블 생성시
- column level
- table level
2. 테이블 생성후문제615. 아래의 테이블을 ename 에 unique 제약을 걸어서 생성하시오
( column level 로 생성하시오 )
create table emp615
( empno number(10),
ename varchar2(10) constraint emp615_ename_un unique,
sal number(10) ) ;
insert into emp615 values( 1111, 'scott', 3000 );
insert into emp615 values( 2222, 'scott', 4000 );
ORA-00001: 무결성 제약 조건(C##SCOTT.EMP615_ENAME_UN)에 위배됩니다
※ null 값은 중복해서 입력될 수 있습니다.
문제616. 아래의 테이블을 생성하는데 ename 에 unique 제약을 걸어서
생성하시오 ( table level 로 하시오 )
테이블명 : emp616
컬럼명 : empno
ename <---- unique 제약 생성
sal
create table emp616
( empno number(10),
ename varchar2(10),
sal number(10),
constraint emp616_ename_un unique(ename) );
문제617. emp 테이블에 ename 에 unique 제약을 생성하시오 !
alter table emp
add constraint emp_ename_un unique(ename);
※ emp 테이블에 ename 에 중복된 data 가 없었기 때문에 unique 제약이
잘 생성되었습니다.
문제618. 우리반 테이블에 email 에 unique 제약을 거시오 !
alter table emp17
add constraint emp17_email_un unique(email);
▣ 107 데이터의 품질 높이기 4(CHECK)
“ 특정 조건에 맞는 데이터만 입력 또는 수정되게끔 컬럼에 거는 제약 “
문법:
- 테이블 생성시
- column level create table emp619
( empno number(10),
ename varchar2(10),
sal number(10) constraint emp619_sal_ck check( sal > 1200 ) );
설명: sal 이 1200 보다 큰 데이터만 입력되게하고 아니면 입력 안되게해라 !
insert into emp619 values( 1111, 'scott', 3000 );
insert into emp619 values( 2222, 'smith', 1000 );
ORA-02290: 체크 제약조건(C##SCOTT.EMP619_SAL_CK)이 위배되었습니다
- table level create table emp620
( empno number(10),
ename varchar2(10),
sal number(10),
constraint emp620_sal_ck check( sal > 1200 ) );
- 테이블 생성후
- emp 테이블에 deptno 에 10,20,30 외에는 입력되거나 수정되지 못하게해라! alter table emp
add constraint emp_deptno_ck check( deptno in (10, 20, 30) ); update emp
set deptno = 90;
문제619. 우리반 테이블에 gender 에 check 제약을 거는데 남, 여 만 입력되거나
수정되게끔 check 제약을 거시오 !
alter table emp17
add constraint emp17_gender_ck check( gender in ('남', '여') );
문제620. 우리반 테이블에 telecom 에 check 제약을 거는데 통신사가
lg, kt, sk 만 입력되거나 수정되게끔 check 제약을 거시오 !
alter table emp17
add constraint emp17_telecom_ck check( telecom in ('lg', 'kt', 'sk') );
문제621. 아래의 테이블을 생성하는데 hiredate 에 check 제약을 거시오
hiredate 이 오늘날짜(sysdate) 이후의 날짜만 입력되겠금
check 제약을 거시오 !
테이블명: emp621
컬럼명 : empno
ename
hiredate <--- check 제약
create table emp621
( empno number(10),
ename varchar2(10),
hiredate date constraint emp621_hiredate_ck check( hiredate > sysdate ) );
OCP 문제 풀어봤음.
문제622. 우리반 테이블의 age 에 check 제약을 거는데 나이가
19 ~ 60 사이의 데이터만 입력되거나 수정되겠금 check 제약을 거시오
alter table emp17
add constraint emp17_age_ck check(age between 19 and 60) ;
▣ 108 데이터의 품질 높이기 5(FOREIGN KEY)
자식키 테이블
ENAME DEPTNO
-------------------- ----------
KING 10
BLAKE 30
CLARK 10
JONES 20
MARTIN 30
ALLEN 30
TURNER 30
JAMES 30
WARD 30
FORD 20
SMITH 20
SCOTT 20
ADAMS 20
MILLER 10 부모키 테이블
DEPTNO DNAME
---------- -------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
FK제약조건 : 함부로 데이터를 삭제,
입력하지 못하게 한다.
부모 테이블에 입력된 데이터만
자식테이블에 들어갈 수 있다.
자식테이블에 존재 하면
부모 테이블에서 함부로 삭제할 수 도 없다.문제623. 도스창 열고 emp 테이블을 초기화 하고 dept 테이블에
deptno 에 primary key 제약을 거시오 !
alter table dept
add constraint dept_deptno_pk primary key(deptno);
문제624. emp 테이블에 deptno 에 foreign key 제약을 걸면서 dept 테이블에
deptno 를 참조해라 라고 하시오 !
alter table emp
add constraint emp_deptno_fk foreign key(deptno)
references dept(deptno) ;
문제625. dept 테이블에 부서번호 10번 데이터를 지우시오 !
delete from dept
where deptno = 10;
ORA-02292: 무결성 제약조건(C##SCOTT.EMP_DEPTNO_FK)이 위배
- 자식 레코드가 발견되었습니다
문제626. dept 테이블의 부서번호 40번 데이터를 지우시오 !
delete from dept
where deptno = 40;
rollback;
설명: emp 테이블에 40번 부서번호인 사원들이 없기 때문에 지워집니다.
문제627. emp 테이블에 아래의 데이터를 입력하시오 !
empno : 3845
ename : jane
sal : 4000
deptno : 80
insert into emp(empno, ename, sal, deptno )
values( 3845, 'jane', 4000, 80 );
ORA-02291: 무결성 제약조건(C##SCOTT.EMP_DEPTNO_FK)이 위배되었습니다
- 부모 키가 없습니다
※ on delete cascade 라는 옵션을 사용하게 되면 dept 테이블의 20번을
지우면 emp 테이블의 20번도 같이 지워지게 됩니다.
이번에는 다시 emp 와 dept 초기화하고 다시 제약을 거는데 on delete cascade
옵션을 제약을 생성하세요.
-
emp 와 dept 를 초기화 합니다.
SQL > init_emp.sql
-
dept 테이블의 deptno 에 primary key 제약을 겁니다.
alter table dept
add constraint dept_deptno_pk primary key(deptno); -
emp 테이블의 deptno 에 foreign key 제약을 거는데 on delete cascade
옵션을 써서 거시오 !alter table emp add constraint emp_deptno_fk foreign key(deptno) references dept(deptno) on delete cascade; -
dept 테이블에 10번 부서번호에 대한 데이터를 지우시오 !
delete from dept
where deptno = 10;select *
from emp;
※ on delete cascade 옵션 : dept 테이블의 deptno 를 지우면
emp 테이블의 deptno 도 같이 지워집니다.
on delete set null 옵션 : dept 테이블의 deptno 를 지우면
emp 테이블의 deptno 는 null 로 변경됩니다.ocp 문제
문제628. emp 테이블에 ename 에 unique 제약을 거시오 !
alter table emp
add constraint emp_ename_un unique(ename);
문제629. emp 테이블에 ename 에 걸린 unique 제약을 삭제하시오 !
alter table emp
drop constraint emp_ename_un;
문제630. dept 테이블에 deptno 에 걸린 primary key 제약을 삭제하시오 !
alter table dept
drop constraint dept_deptno_pk ;
ORA-02273: 고유/기본 키가 외부 키에 의해 참조되었습니다
설명: 자식키가 참조하고 있기 때문에 자식키를 삭제하고 나서 삭제해야 합니다.
그런데 한번에 다 삭제하는 명령어가 있습니다.
alter table dept
drop constraint dept_deptno_pk cascade;
※ 18c 이전에서는 cascade constraints 로 해야했는데 18c 에서는 cascade 로
하면 됩니다.
문제631. emp 테이블에 sal 에 check 제약을 거는데 월급이 0 ~ 9000 사이의
데이터만 입력 또는 수정되겠금 check 제약을 거시오 !
alter table emp
add constraint emp_sal_ck check( sal between 0 and 9000 );
문제632. 위의 체크제약 이름을 emp_sal_ck 에서 emp_ck 로 변경하시오 !
alter table emp
rename constraint emp_sal_ck to emp_ck;
select table_name, constraint_name, constraint_type
from user_constraints
where table_name='EMP';
문제633. (ocp 최신 시험문제) 테이블에 primary key 가 하나여야만 한다는것을
테스트로 확인하시오 !
-
emp 와 dept 를 초기화 합니다.
SQL> @init_emp.sql
-
dept 테이블에 deptno 에 primary key 제약을 거시오
alter table dept
add constraint dept_deptno_pk primary key(deptno); -
dept 테이블에 loc 에 primary key 제약을 거시오 !
SQL> alter table dept
2 add constraint dept_loc_pk primary key(loc);add constraint dept_loc_pk primary key(loc)2행에 오류: *
ORA-02260: 테이블에는 하나의 기본 키만 가질 수 있습니다. -
dept 테이블에 pk 를 지우고 아래와 같이 pk 를 걸어보시오 !
alter table dept
drop constraint dept_deptno_pk;alter table dept
add constraint dept_pk primary key(deptno, loc) ; -
primary key 의 컬럼들 이름과 foreign 의 컬럼들 이름이 서로 같아야한다는 것을
테스트 합니다.alter table emp add loc varchar2(10); merge into emp e using dept d on ( e.deptno = d.deptno ) when matched then update set e.loc=d.loc; commit; alter table emp add constraint emp_fk foreign key(deptno, loc) references dept(deptno, loc) ; 잘 제약이 걸리고 있습니다. alter table emp drop constraint emp_fk; alter table emp add constraint emp_fk foreign key(deptno, job) references dept(deptno, loc) ; → 안된다!
문제634. HR 계정의 departments 테이블에 어떤 제약이 있는지 확인하시오 !
select table_name, constraint_name, constraint_type
from dba_constraints
where table_name='DEPARTMENTS' and owner='HR';
DEPARTMENTS DEPT_LOC_FK R
DEPARTMENTS DEPT_MGR_FK R
DEPARTMENTS DEPT_NAME_NN C
DEPARTMENTS DEPT_ID_PK P
문제635. emp 와 dept 를 초기화하고 emp 테이블의 deptno 의 컬럼이름을
kptno 로 변경하시오 ! (그러면 이름만 같으면 되는거야? → 당옇니 데이터도 같아야함)
SQL> @ init_emp.sql
SQL> alter table emp
rename column deptno to kptno;
문제636. (오늘의 마지막 문제) 예상문제를 확실히 맞추기 위해서 테스트 합니다.
부모키의 컬럼이름과 자식키의 컬럼 이름이 서로 다르지만
data 가 서로 같으면 부모 자식관계를 맺을수 있는지 테스트 하시오 !
alter table emp
rename column deptno to kptno;
alter table dept
add constraint dept_deptno_pk primary key(deptno);
alter table emp
add constraint emp_kptno_fk foreign key(kptno)
references dept(deptno);
select table_name, constraint_name, constraint_type
from user_constraints
where table_name = 'EMP';
→ 결론은 잘나온다!!
참고
- show lines
- show pages
set line 400 너비
set pages 500 높이
col ename for a7~9 정도