✏️ 오늘의 TIL

1. 데이터의 품질 높이기 1 (PRIMARY KEY)
2. 데이터의 품질 높이기 2(NOT NULL)
3. 데이터의 품질 높이기 3(UNIQUE)
4. 데이터의 품질 높이기 4(CHECK)
5. 데이터의 품질 높이기 5(FOREIGN KEY)
6. 제약 삭제
7. 제약명 바꾸기 (rename constraint)

---

[점심시간 문제]

select /*+ index_desc(emp emp_deptno_sal) */ deptno, sal
  from emp
  where sal >= 0  and deptno = 20 and rownum = 1;  

---

데이터의 품질 높이기 1 (PRIMARY KEY)

💡 PRIMARY KEY 이란 중복된 데이터를 입력, 수정 못하게 , null값 입력 못하게

제약을 거는 문법 2가지 1 (테이블을 만들 때)

1) column level

create table emp23
 ( empno  number(10) constraint emp23_empno_pk primary key,
   ename  varchar2(10),
   sal    number(10) );

✍🏻 중복된 데이터를 emp23 empno에 입력해보기

insert into emp23 values(1111, 'scott', 3000); //입력가능
insert into emp23 values(2222, 'allen', 2000); //입력가능
insert into emp23 values(1111, 'james', 4000); //입력불가능

✍🏻 null값 입력해보기

insert into emp23 values (null, 'king', 5000); //안됨

2) table level

create table emp24
( empno number(10),
ename   varchar2(10),
sal  number(10),
constraint emp24_empno_pk primary key(empno));

✍🏻 중복된 데이터를 emp23 empno에 입력해보기

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); //안됨

3. 만들어진 제약 확인

select table_name,constraint_name,constraint_type
  from user_constraints
  where table_name='EMP23';

✔️ primary key, unique에 제약을 걸면 자동으로 생성되는 것? -> index
✅ 방금만든 emp24 테이블 empno에 인덱스가 있는지 확인하기

select index_name, uniqueness
  from user_indexes
  where table_name = 'EMP24';

✔️ 제약이 필요한 이유가 무엇? -> 데이터의 품질을 높여서 정확한 데이터 분석을 위해
✔️ 제약을 테이블에 걸면 어떤일이 벌어지는가 -> 테이블에 데이터를 함부로 입력, 수정, 삭제할 수 없게 된다.

문제 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';

✅ constraint_name 부분을 보면 SYS_....어쩌구 라고 써져있다. 이렇게 제약을 생성하고 관리하면 어느테이블에 어느컬럼, 어느 제약인지 확인하기 어렵다. 그래서 제약이름을 주고 만드는것이 바람직하다!

primary key를 생성했을 때 인덱스 이름이 제약이름이 되지 않게 하는 방법

✅ 유니크 인덱스 이름이 제약이름과 똑같이 생성이 된다.

create table emp27
(empno  number(10),
 ename  varchar2(10),
 sal    number(10),
 constraint emp27_empno_pk primary key // 제약이름
 using index( create index emp27_empno_idx // 인덱스이름
                  on emp27(empno) ) );

using index( create unique 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';

답 A

제약을 거는 문법 2 (테이블이 만들어진 후 제약걸기)

create table emp29
( empno number(10),
  ename varchar2(10),
  sal   number(10) ); // table 생성

alter table emp29
 add constraint emp29_empno_pk primary key(empno); // 제약걸기

문제 608. emp table에 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'); // 20번이라는 중복된 데이터 넣기

commit; 

alter table dept
 add constraint dept_deptno_pk primary key(deptno);  

문제 610. dept테이블에 deptno에 중복된 데이터가 무엇인지 출력하기

select deptno , count(*) 
  from dept 
  group by deptno
  having count(*) >= 2;

위 sql을 서브쿼리로 넣어서

select * 
  from dept
  where deptno in (
        select deptno 
          from dept 
          group by deptno
          having count(*) >= 2
  );

데이터의 품질 높이기 2(NOT NULL)

💡 null값이 입력되지 못하게 거는 제약

1. 테이블 생성시

1) table level 불가능
2) column level 가능

2. 테이블 생성 후

문제 611. 아래의 테이블을 생성하는데 ename에 not null 제약을 걸어서 생성하기

create table emp611
( empno  number(10),
  ename  varchar2(10) constraint emp611_ename_nn not null,
  sal    number(10) );

✔️ null값을 emp611의 ename에 압력하고, 입력 안되는지 확인해보기

insert into emp611 values(1111,'scott',3000); // 되고요
insert into emp611 values(2222,null,2000); // 안돼요

✔️ 테이블 레벨로 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) ); // table생성

alter table emp_nn_test
 modify ename constraint emp_nn_test_nn not null; // 제약생성

❗not null제약만 add가 아닌 modify로 작성해야한다 !

문제 613. 우리반 테이블 ename 에 not null 제약 걸기

alter table emp17
 modify ename constraint emp17_ename_nn not null;  

✅ 우리반 테이블 이름이 null값이 없어서 잘 걸린다!

문제 614. emp 테이블에 mgr에 not null 제약을 거시오

alter table emp
 modify mgr constraint emp_mgr_nn not null;   

✅ null 값이 하나라도 있으면 not null 제약이 걸리지 않는다!

데이터의 품질 높이기 3(UNIQUE)

💡 중복된 data를 허용 못하게 하는 제약

1. 테이블 생성시

1) table level
2) column level

문제 615. 아래의 테이블을 enaem에 unique제약을 걸어서 생성하시오 (컬럼레벨로)

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); // 안됨

❗ null값은 중복해서 입력될 수 있습니다! null값을 못넣게 하고싶다면 not null로 생성

문제 616. 아래의 테이블을 생성하는데 ename에 unique 제약을 걸어 생성하세요 (테이블레벨로)
테이블명 : emp616
컬럼명 : empno, ename, sal

create table emp616
( empno number(10),
  ename   varchar2(10),
  sal  number(10),
  constraint emp616_empno_pk unique(empno));

2. 테이블 생성 후

문제 617. emp테이블에 ename에 unique 제약을 생성

alter table emp
 add constraint emp_ename_un unique(ename);

✅ emp 테이블의 ename에 중복된 데이터가 없었기 때문에 enique제약이 잘 생성됨

문제 618. 우리반 테이블 email에 유니크 제약 걸기

alter table emp17
 add constraint emp_email_un unique(email); 

데이터의 품질 높이기 4(CHECK)

💡 특정 조건에 맞는 데이터만 입력 또는 수정되게끔 컬럼에 거는 제약

1. 테이블 생성시

1) table level

create table emp620
( empno number(10),
  ename varchar2(10),
  sal   number(10),
  constraint emp620_sal_ck check(sal > 1200)) ;

2) column level

create table emp619
( empno number(10),
  ename varchar2(10),
  sal   number(10) constraint emp619_sal_ck check(sal > 1200)) ;

✅ 월급이 1200 보다 큰 데이터만 입력되게 하고, 아니면 입력되지 않게 해라.

insert into emp619 values(1111,'scott',3000); // 1200 이상이라 잘됨
insert into emp619 values(2222,'smith',1000); // 1200 이하라서 안됨

2. 테이블 생성후

alter table emp
add constraint emp_deptno_ck check(deptno in (10,20,30) );

emp테이블 deptno에 10,20,30 외에는 입력, 수정을 못하게 해라

alter table emp
add constraint emp_deptno_ck check(deptno in (10,20,30) );

문제 619. 우리반테이블 성별에 (gender) check조약을 거는데, 남. 여만 임력, 수정되게끔 chec업 걸기!

alter table emp17
add constraint emp17_gender_ck check(gender in ('남','여') ); 

문제 620. 우리반 테이블 telecon에 check 제약 거는데, 통신사가 lg,kt,sk만 입력, 수정되도록 체크제약 걸기

alter table emp17
add constraint emp17_telecom_ck check(telecom in ('kt','lg','sk') );

문제 621. 아래의 테이블을 생성하는데, 입사일에 체크제약을 걸기. hiredate 가 오늘날짜 이후의 날짜만 입력되도록 걸기
테이블명 : emp621
컬럼명 : empno, ename, hiredate

create table emp621
( empno number(10),
  ename varchar2(10),
  hiredate   date,
  constraint emp621_hiredate_ck check(hiredate > sysdate)) ;

✅ 체크 제약조건에 sysdate 쓰지 못한다.

문제 622. 우리반 테이블의 age에 check제약을 거는데, 나이가 19 ~ 60 사이의 데이터만 입력되거나 수정되도록 하시오

alter table emp17
 add constraint emp_age_ck check(age between 19 and 50 ); 

insert into emp17(ename, age) values('test',52); // 안됨 50 이상이라서
insert into emp17(ename, age) values('test',50); // 잘 들어감

데이터의 품질 높이기 5(FOREIGN KEY)

          자식키 테이블                         부모키 테이블

• emp(자식) deptno에 fk, dept(부모) deptno에 pk를 형성해놓으면,
  emp테이블에 deptno 70은 못넣고 (부모 deptno에 없으니까) 10,20,30,40은 넣을 수 있다.
• emp(자식) deptno에 있는 번호는 지우지못한다. 지우면 부모deptno도 지워짐
• dept(부모)의 deptno에는 40이 존재하고 자식deptno에는 40이 없어서 부모 deptno의 40은 지울 수 있다.
  

문제 623. dept, deptno에 프라이머리키 제약 걸기

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); 

✅ 문제 623, 624 수행으로 dept는 부모테이블, emp는 자식테이블이 되었다.

문제 625. dept 테이블에 부서번호 10번 데이터를 지우시오

delete 
 from dept
 where deptno = 10;   

문제 626. dept테이블의 부서번호 40번 데이터를 지우세요

delete 
 from dept
 where deptno = 40;  

✅ emp 테이블에 40번 부서번호인 사원들이 없기때문에 지워진다.

문제 627. emp 테이블에 아래의 데이터를 입력하시오 !
empno : 3845
ename : jane
sal : 4000
deptno 80

insert into emp (empno, ename, sal, deptno)
  values(3845,'jane',4000,80); 

on delete cascade 옵션

on delete cascade라는 옵션을 사용하게 되면 dept테이블의 20번을 지우면, emp 테이블의 20번도 같이 지워지게 된다. -> 제약 만들때 설정해야함.
1. emp, dept초기화
2. dept deptno에 primary key 걸기
3. emp 테이블의 deptno에 foreign key 제약을 거는데 이번에는 on delete cascade

alter table dept
 add constraint dept_deptno_pk primary key(deptno); // primary key
 
alter table emp
  add constraint emp_deptno_fk foreign key(deptno)
    references dept(deptno) on delete cascade; // foreign key 

4. dept 테이블에 10번 부서번호에 대한 데이터 지우기

   delete 
    from emp
    where deptno = 10;  

   
   
   emp 테이블의 10번 부서번호도 지워졌다.

💡on delete cascade 옵션 : dept 테이블의 deptno를 지우면 emp 테이블의 deptno도 같이 지워진다.
💡on delete set null 옵션 : dept 테이블의 deptno를 지우면 emp 테이블의 deptno는 null로 변경된다.

❗ 위는 delete 문법 문제! d는 뒤에 컬럼명이와서 이상하고 b는 연산자도 없어서 이상하고 a는 문법자체가 없다 c를 보면 from 없는데 , from 없어도 지워진다.

제약 삭제

문제 628. emp의 ename에 enique제약을 걸기

alter table emp
 add constraint emp_ename_un unique(ename);     

문제 629. 위에 건 제약을 삭제하기

alter table emp
  drop constraint emp_ename_un;

✔️ C:\Users\ITWILL\Desktop\문제\dba교재(영문).zip\FUND2\D49994GC20 -> sg1 p.80에 있음 참고

문제 630. dept 테이블의 deptno에 걸린 primary key 제약 삭제하기

alter table dept
  drop constraint dept_deptno_pk; 

✅ 자식키가 참조하고 있기 때문에 자식키를 삭제하고 난 후에 dept_deptno_pk를 삭제해야한다. 그런데 한번에 모두 삭제하는 명령어가 있다. ↓

alter table dept
  drop constraint dept_deptno_pk cascade;  

❗18c 이전에서는 cascade constraints로 해야했는데, 18c에서는 그냥 cascade만 써야한다.

제약명 바꾸기 (rename)

문제 631. emp 테이블에 sal에 check제약을 거는데, 월급이 0~9000 사이의 데이터만 입력, 수정되게 하세요

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;

바뀌었슴다.

✍🏻 답인 A, C를 테스트 해보자.
문제 633. (ocp최신 시험문제) 테이블에 primary key가 하나여야만 한다는 것을 테스트로 확인해보기 A번

✔️ 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);

✅ 다른제약들은 몇개던지 걸 수 있지만 primary key는 하나만 만들 수 있다.
이건된다.

 alter table dept
  add constraint dept_pk primary key(deptno,loc);

✔️ 지우고 위 제약 다시걸기 - 됨. 보통 primary key가 하나인게 애매할 때 이렇게 많이 쓴다고 함!

alter table dept
  drop constraint dept_deptno_pk;

alter table dept
  add constraint dept_pk primary key(deptno,loc);

(ocp최신 시험문제 C번확인) primary key의 컬럼들 이름과 foreign 의 컬럼들 이름이 서로 같아야 한다는 것을 테스트 합니다.

✔️ emp 테이블에 loc 컬럼 추가해서 deptno, loc라는 동일한 컬럼 생성

1. alter table emp
  add loc varchar2(10);

2. merge into emp e
  using dept d
  on (e.deptno = d.deptno)
  when matched then
  update set e.loc = d.loc;

✔️ C번 실험!! -> 제약이 잘 걸린다.

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'; 

✅ not null제약도 check 제약이라고 나온다.

문제 635.
emp, dept초기화 하고 emp테이블의 deptno의 컬럼이름을 kptno로 변경

문제 636. (마지막 문제) C번 다시 실험하는데, 위 방법 말고 예상문제를 확실히 맞추기 위해 테스트 해보기. 부모키의 컬럼이름, 자식키의 컬럼이름이 서로 다르지만 데이터가 서로 같으면 부모, 자식관계를 맺을 수 있는지?

 alter table dept
  add constraint dept_deptno_pk primary key(deptno); 

alter table emp 
 add constraint emp_keptno_fk foreign key (kptno) 
   references dept(deptno);

.........????????????왜 되는것이죠?

---

궁금한 것

1. deptno에 null 넣으면 들어간다. -> 된다.

   insert into emp (empno, ename, sal)
     values(3845,'jane',4000);