#12 12일차_제약조건 | DML : UPDATE, DELETE, INSERT

 

12일차에 배운 내용을 정리하였다.

목차는 다음과 같다. :

 

01 제약조건

 - 01 종류

 - 02 생성

 - 03 조회

 - 04 삭제

 

02 DML

 - 01 UPDATE

 - 02 DELETE

 - 03 INSERT

---

01 제약조건

 - 01 종류

 1) primary key : not null + unique
 2) unique
 3) not null
 4) check
 5) foreign key
    foreign key(자식컬럼) references 부모테이블(부모컬럼)
    

 ** ctas시 복제 안되는 제약조건 : not null 제외 모두
 ** null이 삽입될 수 없는 제약조건 : primary key / not null

alter table 테이블명 add foreign key(자식컬럼) references 부모테이블(부모컬럼)

---

 - 02 생성

  (1) 테이블 생성 시

 > 부모 테이블 생성

create table test10_p(
profno number primary key, 
name   varchar2(10) not null
);

create table test11_p(
profno number, 
name   varchar2(10)
);

 

 > 자식 테이블 생성

  >> 제약조건 및 이름 없이

create table test10_c(
no     number       primary key,
name   varchar2(10) unique,
vdate  date         default sysdate not null,
grade  number       check(grade in (1,2,3,4)),
profno number       references test10_p(profno));

 

  >> 제약조건 이름과 같이

create table test11_c(
no     number       constraint test11_no_pk     primary key,
name   varchar2(10) constraint test11_name_uk   unique,
vdate  date         default sysdate constraint test11_vdate_nn not null,
grade  number       constraint test11_grade_ck  check(grade in (1,2,3,4)),
profno number);

 

  (2) 기존컬럼에 제약조건 추가

alter table test11_c add constraint test11c_profno_fk 
                         foreign key(profno) references test11_p(profno);  -- error
  => 자식 테이블에 fk 설정 시 반드시 사전에 부모테이블의 참조컬럼에 pk 또는 uk가 만들어져야 한다.                      

alter table test11_p add constraint test11p_profno_pk primary key(profno);
alter table test11_c add constraint test11c_profno_fk 
                         foreign key(profno) references test11_p(profno);   -- 정상

 

  (3) 컬럼 추가 시 제약조건과 함께

alter table test11_c add (sal number constraint test11c_sal_nn not null); 

 

** 참조 제약조건으로 인한 제약

         부모(dept)              자식(emp)
        deptno(pk)            empno(pk)
                                ...
                              deptno(fk)

          수정제한                입력제한
          삭제제한                수정제한
 (drop/truncate/delete)                
        

---

 - 03 조회

어느 테이블에 어떤 제약조건이 어떤 형태로 어떤 컬럼에 달려있는지 확인하고 싶을 때 사용할 수 있는 뷰

 

select C1.table_name,       -- 테이블명
       C1.constraint_name,  -- 제약조건명
       C2.column_name,      -- 컬럼명
       C1.constraint_type,  -- 제약조건타입(P:PK, U:UK, R:FOREIGN KEY, C:CHECK OR NOT NULL)
       C1.search_condition, -- CHECK 제약의 형태 또는 NOT NULL 여부
       C1.r_constraint_name -- 참조키(부모)의 제약조건명***
  from user_constraints C1,
       user_cons_columns C2
 where C1.constraint_name = C2.constraint_name
   and C1.table_name in ('MEMBER10','BOARD','TEST11','EMP')
 order by C1.table_name;

select *
  from user_cons_columns;

 

 

 foreign key의 경우 부모 테이블을 찾기 위한 상황일 때 미리 이름을 지정하여 (예:  PK_DEPT) 였으면 EMP 테이블의 부모 테이블은 DEPT 테이블이라고 바로 찾을 수 있다.
 그러나 이름을 지정하지 않은 상태로 만들어진 테이블이기에 SYS_C0011169와 같은 형태면 부모 테이블을 한 눈에 찾기 어렵다.
  => 이 경우 self join을 통해 알 수 있다 (r_constraint_name의 값이 constraint_name과 일치하는 행 찾기)

select C1.table_name,       -- 테이블명
       C1.constraint_name,  -- 제약조건명
       C2.column_name,      -- 컬럼명
       C1.constraint_type,  -- 제약조건타입(P:PK, U:UK, R:FOREIGN KEY, C:CHECK OR NOT NULL)
       C1.search_condition, -- CHECK 제약의 형태 또는 NOT NULL 여부
       C1.r_constraint_name -- 참조키(부모)의 제약조건명***
  from user_constraints C1,
       user_cons_columns C2
 where C1.constraint_name = C2.constraint_name
   and C1.table_name in ('TEST_DEPT', 'TEST_EMP')
 order by C1.table_name;

select *
  from user_cons_columns;

---

 - 04 삭제

  (1) 제약조건 삭제
alter table 테이블명 drop constraint 제약조건명;

  (2) 부모 테이블 삭제 시 제약조건과 함게 삭제
drop table 테이블명 cascade constraint;

 

  (3) 데이터 삭제

    (가) 부모 데이터 삭제(deptno=10)
alter table test_emp drop constraint test_emp_deptno_fk;

delete from test_dept  -- error : test_emp 테이블에 deptno=10 데이터가 존재하므로
 where deptno = 10;
 =>  from은 생략이 가능해서delete 테이블명~으로 작성하여도 실행된다.


delete from test_emp  -- 정상 : 자식 데이터 먼저 삭제  
 where deptno = 10;
delete from test_dept  -- 정상 : delete로 자식 데이터 먼저 삭제 후 부모 데이터 삭제는 가능
 where deptno = 10;
commit;
 
 
    (나) 부모 데이터 전체 삭제(truncate)
truncate table test_emp;        -- 정상 : 자식 데이터를 모두 삭제
truncate table test_dept;       -- error : 부모 데이터를 truncate로 삭제시,  자식 데이터 존재여부와 상관없이  자식 테이블의 fk가 존재하는 경우 항상 불가
                                
delete test_emp;                -- 정상 : 자식 데이터 먼저 모두 delete로 삭제
delete test_dept;               -- 정상 : 자식 데이터 먼저 모두 delete 후 부모 테이블 데이터 모두 삭제 가능
commit;

    (다) 부모 테이블 전체 삭제
drop table test_dept;           -- erre : 자식 데이터 존재여부와 상관없이 부모 테이블 삭제 불가(fk가 있는 경우)

> 자식 테이블 먼저 삭제(FK도 같이 삭제됨) -> 부모 테이블 삭제
> 자식 테이블 fk 삭제 -> 부모 테이블 삭제
> 부모 테이블 삭제 시 fk 동시 삭제(cascade) 
     ** 자식 테이블은 삭제되지 않음

drop table test_dept cascade constraints ;

select * from test_dept;                     -- 부모 테이블 삭제됨
select * from test_emp;                     -- 자식 테이블 남아있음

select *
  from user_constraints
 where constraint_name = 'TEST_EMP_DEPTNO_FK';  -- FK 도 함께 사라짐

 

  (4) 참조 제약조건 옵션

    (가) on delete set null
부모 삭제시 자식에 해당되는 데이터를 null로 만든다.
dept 삭제 시 emp 사원들이 모두 해고될 수는 없고 발령대기처럼 deptno = null 이 된다.
    (나) on delete cascade

 

-- [ 실습 ]
-- step 1) student, professor와 구조 및 데이터가 동일한 테이블 생성
create table test_student as select * from student;
create table test_professor as select * from professor;

desc test_professor;
-- step 2) 참조 제약조건 생성
alter table test_professor add constraint test_pro_profno primary key(profno);
alter table test_student add constraint test_std_profno_fk 
                             foreign key(profno) references test_professor(profno);
                             
-- step 3) 부모 데이터 삭제 시도
delete test_professor
 where profno = 1001;       -- error
 
-- step 4 ) fk 삭제 후 재생성(on delete cascade)
alter table test_student drop constraint test_std_profno_fk;
alter table test_student add constraint test_std_profno_fk
      foreign key(profno) references test_professor(profno) on delete cascade;
      
-- step 5) 부모 데이터 삭제 시도(profno =1001)

delete test_professor where profno = 1001;  -- 정상

select * from test_professor;   -- 삭제 확인
select * from test_student;     -- 삭제 확인
-- ON DELTE CASCADE 옵션으로 FK 생성시 부모 데이터 삭제 시 자식 데이터도 함께 삭제됨

-- step 6) fk 삭제 후 재생성 (on delete set null)
alter table test_student drop constraint test_std_profno_fk;
alter table test_student add constraint test_std_profno_fk
      foreign key(profno) references test_professor(profno) on delete set null;

-- step 7 ) 부모 데이터 삭제 시도(profno = 2001)

delete test_professor where profno = 2001;          -- 정상
commit;

select * from test_professor where profno = 2001;   -- 삭제 확인
select * from test_student where studno in (9412, 9612); -- 데이터는 존재, profno가 null이 됨

-- on delete set null 옵션으로 fk 생성시 부모 데이터 삭제 시 자식 데이터의 컬럼값이 null로 변경됨

---

02 DML

 데이터 변경 언어로 insert, update, delete, merge를 포함한다.
반드시 TCL(commit, rollback)로 작업을 종료해야 한다. 만약 수정사항 발생 후 commit / rollback을 하지 않으면 LOCK이 발생하며 다른 사용자가 수정 등을 진행할 수 없다.

 

 - 01 UPDATE

update 테이블명
   set 컬럼명 = 변경값
 [where 조건];
 변경값, where 절의 조건부에 서브쿼리 사용이 가능하다.

 

  특정 컬럼의 값을 수정하며 동시에 여러 컬럼을 수정할 수 있다.

조건절은 생략 가능하며, 생략 시 모든 행이 수정된다.

--예제) test_student 테이블의 키를 학년별 평균키로 수정
select * from test_student;

select avg(height)
  from student
 group by grade;
 
-- height가 number(4)로 되어있어서 정수형이라 반올림된 값이 들어감 
update test_student t1
  set height = (select avg(height) 
                  from test_student 
                 where grade = t1.grade);
rollback;
desc test_student;


--** 다중 컬럼 수정
update 테이블명
   set 컬럼1 = 값1, 컬럼2 = 값2...
 [where ... ]
 
update 테이블명
   set (컬럼1, 컬럼2) = (값1, 값2) -- error
 [where ... ] 

update 테이블명
   set (컬럼1, 컬럼2) = (select ... ) -- 서브쿼리를 넣어줘야 함
 [where ... ]  ;

-- 예제) 김영조 교수의 직급, 급여를 각각 정교수, 20% 인상된 값으로 출력
select *
  from test_professor;
  
update test_professor
   set position = '정교수' , pay = pay*1.2
 where name = '김영조';
commit;

-- 예제) 나한열 교수의 직급, 급여를 각각 정교수, 600으로 수정

update test_professor
  set (position, pay) = ('정교수', 600)        -- error
where name = '나한열';

update test_professor
  set (position, pay) = (select '정교수', 600
                           from dual)       
where name = '나한열';
commit;

---

 - 02 DELETE

delete [from] 테이블명
[where 조건]; 

 

  행 단위로 삭제를 진행하며, where절을 생략 시 모든 행이 삭제된다.

 delete로 데이터 삭제 시 dbms 내 기록을 남기므로 truncate보다 삭제 속도가 느리다.

delete로 데이터 삭제 시 저장공간을 즉시 반환하지 않는다 -> truncate로 삭제 시 저장공간을 반환한다.

select *
  from test_professor;
  
-- 예제) test_professor 테이블에서 전임강사 모두 삭제

delete test_professor
 where position = '전임강사';
commit;


-- 예제) 각 직급별로 평균 급여보다 높은 급여를 받는 교수들을 전부 해고
select *
  from test_professor;
  
delete test_professor t1
 where t1.pay > (select avg(pay) as avg_pay
                   from test_professor
                  where position = t1.position);

---

 - 03 INSERT

  행 단위로 입력을 진행한다.

  하나의 행만 입력 가능하며, 동시에 여러 행을 입력하려 할 시 서브쿼리를 필수적으로 작성해야 한다.

 

문법1) 모든 컬럼의 값을 입력
insert into 테이블명 values(값1, 값2, 값3, ...);

문법2) 필요한 컬럼만 선택해서 값을 입력
insert into 테이블명(컬럼1, 컬럼2, 컬럼3, ... ) values(값1, 값2, 값3, ...);
* 생략한 컬럼의 값은 자동으로 null or 기본값으로 들어감

문법3) 서브쿼리를 사용한 다중행 입력
insert into test_student(컬럼1, 컬럼2, 컬럼3, ... )
select *
  from student
 where grade = 1;
 
서로 다른 테이블에도 insert로 다중행이 입력 가능하다.
insert all
  into test_student(name, id, jumin) values ('김길동', 'c123', '9911111111111')
  into test_student(name, id, jumin) values ('박길동', 'd123', '9811111111111')
  into test_professor(profno, name, id, position, pay, hiredate) 
       values(9876, '박길동', 'e123', '정교수', 500, sysdate)
  select *
    from dual;
commit;

 

예제) test_student에 임의의 데이터 입력
전체컬럼값 다 명시, 필수컬럼만 선택한 입력, 각각 1개씩 입력
select * from test_student;
desc test_student;
insert into test_student values (9999, '가나다', 'ganada', 1, '7012032143332', '1970/12/03', '032)214-3352', '167', '52', '301', null, null);
insert into test_student(studno, name, ID, grade, JUMIN) values (9998, '다나가', 'danaga', 1, '7101011124653')

---