230714 Oracle SQL 15 [제약 (CONSTRAINT), primary key, foreign key, not null, unique, check]
오라클 정규교재 영문 책PDF 'D49996GC20_sg2.pdf' 80p
104. 데이터의 품질 높이기 1(PRIMARY KEY)
제약 (CONSTRAINT)
✅ 제약이 필요한 이유 : 데이터의 품질을 높여서 정확한 데이터 분석을 하기 위해서 제한을 거는 기능
✅ 제약을 테이블에 걸면 어떤일이 벌어지는가? : 테이블에 데이터를 함부로 입력, 수정, 삭제할 수 없게 됩니다.
데이터가 함부로 엉뚱한 데이터로 입력되지 않게하고,
null 값이나 중복된 data를 허용하지 못하게 막는 기능제약의 종류 5가지
primary key: 중복된 data 와 null 을 허용하지 못하게 막는 제약unique: 중복된 data 를 허용하지 못하게 막는 제약not null: null 값을 허용하지 못하게 막는 제약check: 지정된 data 만 입력되고 수정될 수 있도록 막는 제약foreign key: 자식키에 해당하는 컬럼에 거는 제약
PRIMARY KEY 제약
- 중복된 데이터를 입력 또는 수정 못하게
- null 을 입력 못하게
pk 제약을 거는 문법 2가지
테이블을 만들 때 -> 오라클에서 둘중 아무거나 쓰라고 만들어준 것 같음
- column level : 컬럼 옆에 제약을 기술하는 것
empno 에 프라이머리 키 제약을 거는 문법create table emp23 ( empno number(10) constraint emp23_empno_pk primary key, ename varchar2(10), sal number(10) );
empno23_empno_pk 는제약이름인데 의미있게 만들어야 관리하기 편해짐! (삭제, 중지 등)
⬇️ 사전 활용해서 프라이머리키 걸린 것 확인하기select table_name, constraint_name, constraint_type from user_constraints where table_name='EMP23';
오라클 -> 테이블 -> 클릭 -> 제약조건 에서도 확인가능
- table level : 컬럼 밑에 제약을 기술하는것
컬럼밑에 제약을 기술하는것create table emp24 ( empno number(10), ename varchar2(10), sal number(10), constraint emp24_empno_pk primary key(empno) );
primary key(제약 걸 컬럼이름)
테이블이 만들어진 후에
create table emp29 ( empno number(10), ename varchar2(10), sal number(10) ); alter table emp29 add constraint emp29_empno_pk primary key(empno);alter table 테이블명
add constraint 제약이름 primary key(컬럼명)
문제 608.
emp 테이블에 empno 에 primary key 제약을 거시오alter table emp add constraint emp_empno_pk primary key(empno);
- EMP테이블에 중복과 null값이 없기 때문에 pk가 잘 걸렸음!
❓constraint_type 의 c와 P는 무슨 차이인가욥?
문제 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)을 검증할 수 없습니다 - 잘못된 기본 키입니다
- 중복된 데이터가 있기 때문에 pk가 걸리지 않는다!
중복된 데이터 확인하는 방법 ★
문제 610.
dept 테이블에 deptno 에 중복된 데이터가 무엇인지 출력하시오 ★★select deptno,count(*) from dept group by deptno having count(*) >= 2;➡️ 중복데이터가 있는
데이터가 뭔지 확인하는것select * from dept where deptno in ( select deptno from dept group by deptno having count(*) >=2);
➡️ 서브쿼리 사용해서 중복데이터 내용까지 확인하는것
-
중복된 데이터를 emp23과 emp24 에 empno 에 입력해본다면?insert into emp23 values (1111,'scott', 3000); insert into emp23 values (2222,'allen', 2000); insert into emp23 values (1111,'james', 4000);마지막 1111은 입력 불가능
ORA-00001: 무결성 제약 조건(C##SCOTT.EMP23_EMPNO_PK)에 위배됩니다 -
null값을 입력해본다면?insert into emp23 values (null, 'KING', 5000);null 값 입력 불가능
SQL 오류: ORA-01400: NULL을 ("C##SCOTT"."EMP23"."EMPNO") 안에 삽입할 수 없습니다
✅ primary key 나 unique 제약을 걸면 자동으로 index 가 생성이 된다!
방금 만든 emp24 테이블에 empno에 인덱스가 있는지 확인하시오
select index_name, uniqueness from user_indexes where table_name='EMP24';
index 이름이 제약 이름으로 나온다.
➡️ index 이름을 제약이름이 아닌 별도의 index 이름으로 만드는 방법 ❗❗
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';
✅ 제약이름을 주지 않으면 SYS_Cn 로 자동으로 만들어진다.
문제 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';
이렇게 되면 어느테이블의 어느컬럼의 무슨 제약인지 알 수가 없기 때문에 사용하기 어렵다.
그래서 제약이름을 주고 만드는게 바람직하다.
ex. 공공기업의 db를 1년에 2번 받기 때문에 이런 이름관리를 잘해주어야한다.
NOT NULL 제약
null 값이 입력되지 못하게 거는 제약
ex) null 을 입력하지않아야 하는 사원번호, 월급 등에 not null 제약을 건다.
not null 제약 생성
- 테이블 생성시
- table level : 불가능 :
constraint 제약이름 not null- column level : 가능
- 테이블 생성후 :
modify사용
✅ 컬럼레벨로 not null 제약 만들기
문제 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';
type C 면 일단not null이다.insert into emp611 values (1111,'scott', 3000); insert into emp611 values (2222, null, 2000);SQL 오류: ORA-01400: NULL을 ("C##SCOTT"."EMP611"."ENAME") 안에 삽입할 수 없습니다
✔️ null 값 입력 안된다!
✅ 테이블 레벨로 not null 제약 만들어지는지 확인해보기 (X)
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 제약 생성
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 ;❗❗add 가 아니라 modify 이다! (not null 만)
문제 613. 우리반 테이블의 ename 에 not null 제약을 거시오
alter table emp17 modify ename constraint emp17 not null;
문제614.
emp 테이블에 mgr에 not null 제약을 거시오alter table emp modify mgr constraint emp not null;ORA-02296: (C##SCOTT.EMP) 사용으로 설정 불가 - 널 값이 발견되었습니다.
mgr 에 null 값이 있기 때문에 오류 (하나라도 있으면)
106. 데이터의 품질 높이기 3 (UNIQUE Constraint)
유니크 제약 : 중복된 데이터를 허용 못하게 하는 제약
유니크 제약 문법
- 테이블 생성시
- column level
- teble level
- 테이블 생성 후
✅ 컬럼 레벨로 생성
문제 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)에 위배됩니다
중복된 데이터 insert 되지 않는다!
✔️ null 값은 중복해서 입력될 수 있습니다!
✅ 테이블 레벨로 생성
문제 616. 아래의 테이블을 생성하는데, ename 에 unique 제약을 걸어서 생성하시오 (테이블 레벨로)
테이블명 : emp615 컬럼명 : empno ename <- unique 제약 생성 salcreate 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
crate table emp619 ( empno number(10), ename varchar2(10), sal number(10) constraint emp619_sal_ck check (sal>1200) ); * 기존 sql 문법에서 where 절에 해당되는 느낌?➡️sal이 1200보다 큰 데이터만 입력되게 하고 아니면 입력 안되게 하라
insert into emp619 values(1111,'scott',3000); insert into emp619 values(2222,'smith',1000); ❌table level
create table emp620 (empno number(10), ename varchar2(10), sal number(10), constraint emp620_sal_ck check (sal >1200) );X
저기 콤마 있으면 체크제약, 없으면 ??? 이다 (강사님 말 제대로 돌음음)
테이블 생성후
alter table emp add constraint emp_deptno_ck check( deptno in (10,20,30) );우리반 테이블의 gender 에 check 제약을 거는데,
남 여 만 입력 또는 수정 되게끔 check 제약을 거시오alter table emp add constraint emp17_gender_ck check( gender in ('남', '여') );
문제 622.우리반 테이블에 telecom에 check 제약을 거는데, 통신사가 lg, kt, sk 만 입력되거나 수정되게끔 check 를 거시오
alter table emp add constraint emp17 check( telecom in ('lg,'kt','sk') );
문제 621.
아래의 테이블을 생성하는데 hiredate 에 check 제약을 거시오
hiredate 이 오늘날짜 (sysdate) 이후의 날짜만 입력되게끔 check 제약을 거시오테이블명 : emp621 컬럼명 : empno ename hiredatecrate table emp621 ( empno number(10), ename varchar2(10), hiredate date constraint emp621_hiredate_ck check(hiredate > sysdate) );오류❌
check 제약에 못 쓰는 것
✔️ References to CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns
✔️ Calls to SYSDATE, UID, USER, and USERENV functions
문제 622.
우리반 테이블의 age에 check 제약을 거는데
나이가 19 ~ 60 사이의 데이터만 입력되거나 수정되게끔 check 제약을 거시오alter table emp17 add constraint emp17_age_ck check(age between 19 and 60);
- between ~ and 활용
108. 데이터의 품질 높이기 5 (FOREIGN KEY 제약)
자식키 테이블 부모키 테이블
emp table dept table
(deptno 10~30) (deptno 10~40) ➡️ 함부로 삭제 수정 하지 못하도록..? 까다롭게 설정하는 제약 키
자식키 테이블에 deptno 70 입력불가
부모키 테이블의 deptno 10 못지움 (자식키에 있는거라), 40은 지워짐 (자식키에 없어서)
✔️ 부모키(pk)는 테이블에 1개만 가능, 자식키(fk)는 여러개가능
문제 623. 명령프롬포트창 열어서 emp 테이블 초기화 하고
dept 테이블의 deptno 에 primary key 제약을 거시오alter table dept add constraint dept_empno_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);➡️ dept 테이블의 deptno는 부모키(primary key), emp 테이블의 deptno 는 자식키(foreign key)
문제 625. dept테이블에 부서번호 10번 데이터를 지우시오
delete from dept where deptno = 10;ORA-02292: 무결성 제약조건(C##SCOTT.EMP_DEPTNO_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다
emp 테이블에 10번 deptno 가 있기 때문에!
문제 626. dept 테이블의 부서번호 40번 데이터를 지우시오
delete from dept where deptno = 40;emp 테이블에 40번 부서번호가 없어서 지워진다
문제 627.
emp 테이블에 아래의 데이터를 입력하시오empno : 3845 ename : jane sal : 4000 deptno : 80insert into emp(empno, ename, sal, deptno) values (3845, 'jane', 4000, 80);ORA-02291: 무결성 제약조건(C##SCOTT.EMP_DEPTNO_FK)이 위배되었습니다- 부모 키가 없습니다
deptno 80 가 없어서
PK,FK 삭제 옵션 두가지
✅ 둘다 지워지게 할 수는 없을까? -> on delete cascade 라는 옵션을 사용
dept 테이블의 20번을 지우면 emp 테이블의 20번도 같이 지워지게 된다!
on delete cascade옵션을 써서 제약을 다시 생성하시오
1. emp, dept 를 초기화 합니다.
2. dept 테이블의 deptno 에 primary key 제약을 겁니다.alter table dept add constraint dept_empno_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;옵션 위치 : references 테이블(컬럼이름) on delect cascade;
4. dept 테이블에 10번 부서번호에 대한 데이터를 지우시오delete from dept where deptno = 10;지워진다.
✅ on delete cascade 옵션
-> dept 테이블의 deptno 를 지우면 , emp 테이블의 deptno 도 같이 지워진다.
✅ on delete set null 옵션
-> dept 테이블의 deptno 를 지우면 , emp 테이블의 deptno은 null 로 변경된다.
(행이 지워지는 것은 아님)
✔️ primary key (부모키) : dept 테이블의 deptno
foreign key (자식키) : emp 테이블의 deptno
➡️ on delete cascade 테스트
문제637. on delete cascade 옵션을 테스트 하시오
1) emp 와 dept 를 초기화 합니다.@init_emp.sql2)
on delete cascade옵션을 써서 부모-자식 관계를 형성 합니다.alter table dept add constraint dept_empno_pk primary key(deptno);alter table emp add constraint emp_deptno_fk foreign key(deptno) references dept(deptno) on delete cascade;3) dept 테이블(부모)의 10번 데이터를 지우면 emp 테이블(자식)의 10번 사원들의 데이터도 지워지는지 확인하시오
delete from dept where deptno = 10;select * from dept where deptno = 10;select * from emp where deptno = 10;
➡️ on delete set null 테스트
문제 638. on delete set null 을 테스트하시오 (위 문제와 똑같이 진행)
select ename, deptno from emp;
부서번호 10번은 null 로 변경되었다.
❓근데 부서번호 10번 where 절 출력하면 안나온다 (null되서 10은 안나오나봄)
제약 삭제
drop
문제 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;
pk, fk 한번에 삭제
cascade
문제 630.
dept 테이블에 deptno 에 걸린 primary key 제약을 삭제하시오alter table dept drop constraint dept_deptno_pk;자식키가 reference (참조) 하고 있어서 지울 수 없다!
자식키를 먼저 삭제하고 나서 삭제해야 합니다.그런데 다 한번에 삭제하는 명령어가 있다!
❗자식키와 부모키 한번에 삭제❗alter table dept drop constraint dept_deptno_pk cascade;18c 이전에서는 cascade constraints 로 해야했는데,
18c에서는 그냥cascade로 하면 된다.
제약 이름 바꾸기 (rename)
rename
문제 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';
제약 중지 기능
disable
-> 삭제하는 것은 아니고, 잠시 중지시키는 것
🤔 언제 제약을 중지시키는가?
ex ) 주로 check 제약 관련해서 중지를 많이 시키는데,
예를 들면 사원테이블에 월급을 check 제약을 거는데, 월급이 0-9000 사이의 데이터만 입력 혹은 수정되게끔 check 제약을 걸면 앞으로 월급을 0-9000 사이외의 값으로는 갱신하지 못하게 된다.
그런데 사장인 KING 은 예외로 9500 으로 월급을 갱신하면서 계속해서 다른 사원들의 데이터는 0-9000 사이의 데이터로 제약을 유지하고 싶을때
TEST
- emp 와 dept 를 초기화 합니다.
- emp 테이블의 월급을 0~9000 사이의 데이터만 입력 또는 수정되게끔 check 제약을 겁니다.
alter table emp add constraint emp_sal_ck check (sal between 0 and 9000);update emp set sal = 9500 where ename = 'KING'
check 제약 걸려서 변경 불가능!
- 체크 제약을 중지 시킵니다.
alter table emp disable constraint emp_sal_ck;select constraint_name, status from user_constraints where table_name ='EMP';
- KING 의 월급을 9500 으로 변경합니다.
update emp set sal=9500 where ename ='KING';➡️ 2개 행 이(가) 업데이트되었습니다!
5. 다시 check 제약을 활성화 시킵니다.alter table emp enable constraint emp_sal_ck;
➡️ 제약에 위반 데이터인 9500이 있기 때문에 다시 활성화가 안되었다.
⬇️
조건 위반데이터 무시하기
🤔 하지만, 제약에 위반된 데이터가 있지만 제약을 활성화 시키고 싶다면,
novalidate
alter table emp
enable novalidate constraint emp_sal_ck;❗novalidate 옵션을 쓰면, 제약에 위반된 데이터가 있어도 제약을 활성화 시킬 수 있다. (예외 옵션)
ocp문제. (카페 OCP SQL 5번 기출문제)
답 A,C,E,G -> 정답 틀렸다!
A : 프라이머리키 1개, 폴린키 여러개 가능 ⭕
B : 폴린키 여러개 가능해서 ❌
C : 테스트 결과 ⬇️ 반드시 컬럼이름 같지않아도 데이터만 맞으면 형성되기 때문에 ❌
D : 부모테이블의 데이터가 지워졌을때 자식테이블의 데이터는 남아있다 ⭕ (on delete set null 옵션때문에 가능)
E : on delete cascade 얘기 ⭕
F : 프라이머리키만이 컬럼레베로가 테이블 레벨로 정의할수있다 ❌ (하나빼고 다됨..몬지까먹음)
G : PK FK 제약은 컬럼레벨, 테이블 레벨로 설정가능하다 ⭕
✅ 테이블당 Primary key 는 1개, Foreign key 는 여러개 사용 가능
문제 633. (ocp 최신 시험문제)
테이블에 primary key 가 하나여야만 한다는 것을 테스트로 확인하시오
- dept 테이블에 deptno 에 primary key 제약을 거시오.
alter table dept add constraint dept_deptno_pk primary key(deptno);
- dept 테이블에 loc에 primary key 제약을 거시오
alter table dept add constraint dept_loc_pk primary key(loc);
alter table dept add constraint dept_pk primary key(deptno, loc);이렇게는 된다..? 컬럼 여러개를 한번에 pk 만들수는 있다? ❓🤔
그럼 둘다 걸린거임?
✅ primary key 의 컬럼들 이름과 foreign key 의 컬럼들 이름이 서로 같아야한다?
alter table dept add constraint dept_pk primary key(deptno, loc);를 통해서 알아보자.
- emp 테이블에 loc 컬럼을 추가한다.
alter table emp add loc varchar2(10);merge into emp e using dept d on(e.deptno = d.deptno) whem matched then update set e.loc=d.loc; commitalter table emp add constraint emp_fk foreign key (deptno, loc) references dept(deptno, loc);➡️ 자식키, 부모키 2개씩 같게 했을땐 생긴다.
alter table emp drop constraint emp_fk;alter table emp add constraint emp_fk foreign key (deptno, job) references dept(deptno, loc);ORA-02298: 제약 (C##SCOTT.EMP_FK)을 사용 가능하게 할 수 없음 - 부모 키가 없습니다
➡️ 부모키와 자식키의 컬럼 이름이 다르면 생성되지 않는다!
문제 634.
HR 계정의 departments 테이블에 어떤 제약이 있는지 확인하시오
➡️ sql developer 에서 확인하기
➡️ 명령어로 확인하기select table_name, constraint_name, constraint_type from dba_constraints where table_name='DEPARTMENTS' and owner='HR';
constraint type
R : Foreign key
C : NOT NULL
P : Primary key
문제 635.
emp 와 dept 를 초기화 하고 emp 테이블의 deptno 의 컬럼 이름을 kptno 로 변경하시오
(@init_emp.sql)alter table emp rename column deptno to kptno;
문제 636. (오늘의 마지막 문제)
OCP 예상문제를 확실히 맞추기 위해서 테스트합니다.
부모키의 컬럼이름과 자식키의 컬럼이름이 서로 다르지만 data가 같으면 부모키-자식키 관계를 맺을 수 있는지 테스트하시오emp dept kptno deptno loc dnamealter 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'; select table_name, constraint_name, constraint_type from user_constraints where table_name ='DEPT';
➡️ 둘다 생겼음!
pk fk 확인 쿼리
🟥 부모키(primary key) <-> 자식키(foreign key)
뭐가 연결되어 있는지 확인하는 쿼리
SELECT *
FROM ALL_CONSTRAINTS A
, ALL_CONS_COLUMNS B
WHERE A.TABLE_NAME = 'DEPT' -- 여기에 테이블명 대문자로 기입
AND A.OWNER = B.OWNER
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
ORDER BY B.POSITION;
✔️테이블 -> Model 에서도 확인가능함!
