데이터베이스 SQL 오라클 제약조건, 시퀀스 , 트랜잭션

 

-- 컬럼 속성(제약조건)

-- 테이블에 부적합한 자료가 입력되는 것을 방지하기 위해서 테이블을 생성할 때 각 컬럼에 대해서 정의하는
-- 여러가지 규칙을 정한 것.

-- 1) not null 

-- 2) unique

-- 3) primary kiey : not null + unique

-- 4) foregin key

-- 5) check

-- 1) notnull 제약조건
-- null 값이 입력되지 못하게 하는 제약조건.
-- 특정 열에 데이터의 중복 여부와는 상관없이 null 값을 허용하지 않는 제약 조건.

create table null_test(
    col1 varchar2(10) not null,
    col2 varchar2(10) not null,
    col3 varchar2(10) 
);    
insert into null_test values('aa', 'aa1', 'aa2');

insert into null_test(col1, col2) values('bb', 'bb1');

insert into null_test(col1, col2) values('cc', '');



--2) unique 제약조건
-- 열에 저장할 데이터에 중복을 허용하지 않고자 할때 사용하는 제약조건
-- null 값은 허용

create table unique_test1(
    col1 varchar2(10) unique,
    col2 varchar2(10) unique,
    col3 varchar2(10) not null,
    col4 varchar2(10) not null
   
);

insert into unique_test1 values ('aa','aa1', 'aaa1', 'aaaa1');
insert into unique_test1 values ('bb','bb1', 'bbb1', 'bbbb1');
update unique_test1 set col2 = 'aa1' where col1 = 'bb'; -- error 발생

--3) primary kiey : not null + unique 제약조건
-- 테이블에 하나만 존재해야함
-- 보통은 주민번호나 emp테이블의 empno(사원번호) 등이 primary key의 조건이 됨.


--4) foreign key 제약조건
-- 다른 테이블의 (필드)컬럼을 참조해서 무결성을 검사하는 제약 조건.
-- 참조 키 : 부모테이블의 컬럼을 얘기함.
-- 외래 키 : 자식 테이블의 컬럼을 얘기함.

-- 자식 테이블의 컬럼값(데이터)이 부모테이블에 없는 경우 무결성의 규칙이 깨져버림.
-- 외래키가 존재하기 위해서는 우선적으로 부모테이블이 먼저 만들어져 있엇야함.

create table foreign_test(
    empno number primary key,
    ename varchar2(20) not null,
    job varchar2(50) not null,
    deptno number(2) references dept(deptno)
 );
 insert into foreign_test values('1111', '홍길동', '영업부', 30);
  insert into foreign_test values('2222', '유관순', '회계부', 10);
   insert into foreign_test values('3333', '김유신', 'IT', 50);
   
-- 5) check 제약 조건
-- 열에 저장할 수 있는 값의 범위 또는 패턴을 정의할 때 사용되는 제약 조건.
-- 주어진 값만 허용하는 제약조건
create table check_test(
    gender varchar2(6)
         constraint gender_ch check(gender in('남자', '여자'))
);
insert into check_test values ('남자');
insert into check_test values ('여자');
insert into check_test values ('남성'); -- error 발생
----------------------------------------------------------------------------------------------------------------------------

-- 시퀀스(sequence)
-- 연속적인 번호를 만들어 주는 기능
-- 형식)
--      create sequence 시퀀스 이름
--      start with n (시작번호 설정 - 기본적으로 기본값은 1)
--      increment by n (증가번호 설정 - 기본적으로 증가값은 1)
--      maxvalue n (시퀀스 최대 번호 설정)
--      minvalue n (시퀀스 최소 번호 설정)
--      cache = nocahe (시퀀스의 값을 빠르게 설정하기 위해 캐쉬 메모리 사용 여부)
--      1) cache : 시퀀스를 빨리 제공하기 위해서 미리 캐쉬 메모리에 시퀀스를 넣어 두고 
--                 준비하고 있다가 시퀀스 작업이 필요할 때 사용함.
--                 default로 20개의 시퀀스를 캐쉬 메모리에 보관하게 됨.
--      2) nocahe : cache 기능을 사용하지 않겠다는 의미.

insert into memo
    values(memo_seq.nextval, '메모4', '김연아', '연아님 글', sysdate);
    

-- 트랜잭션(transaction)
-- 데이터 처리의 한 단위를 말함.
-- 오라클에서 발생하는 여러 개의 SQL 명령문들을 하나의 논리적인 작업 단위로 처리하는 것을 말함.
-- All or Nothing 방식으로 처리함.
-- 명령어 여러 개의 집합이 정상적으로 처리가 되면 종료하고, 
-- 여러 개의 명령어 중에서 하나의 명령어라도 잘못이 되면 전체를 취소하는 것을 말함. - 중요

-- 트랜잭션 사용 이유 : 데이터의 일관성을 유지하면서 데이터의 안정성을 보장하기 위해 사용.

-- 트랜잭션 사용시 트랜잭션을 제어하기 위한 명령어.
-- 1) commit : 모든 작업을 정상적으로 처리하겠다고 확정하는 명령어.
--             트랜잭션(insert, update, delete) 작업의 내용을 실제 DB에 반영.
--             이전에 있떤 데이터에 update 현상이 발생을 함.
--             모든 사용자가 변경된 데이터의 결과를 볼 수 있음.
-- 2) rollback : 작업 중에 문제가 발생했을 때 트랜잭션 처리 과정에서 
--               발생한 변경사항을 취소하여 이전 상태로 되돌리는 명령어.
--               트랜잭션(insert, update, delete) 작업 내용을 취소함.
--               이전에 commit 한 곳까지만 복구가 됨.

--1. dept 테이블을 복사하여 dept_02 테이블을 만들어 보자.
create table dept_02
as 
select * from dept;

--2. dept_02 테이블에서 40번 부서를 삭제한 후 commit을 해보자.
delete from dept_02 where deptno = 40;
commit;

--3. dept_02 테이블의 내용을 삭제해 보자.
delete from dept_02;

-- 4. 이 때 만일 부서번호가 20번 부서에 대해서만 삭제하고 싶었는데 잘못해서 전체가 삭제된 경우.
rollback;

-- 5. 20번 부서만 삭제
delete from dept_02 where deptno = 20;

-- 6. 데이터 베이스에 적용시키자.
commit;



-- savepoint : 트랜잭션을 작게 분할하는 것
--             사용자가 트랜잭션 중간 단계에서 포인트를 지정하여
--             트랜잭션 내의 특정 savepoint까지 rollback을 할수 있게 하는 것을 말함.

-- 1. dept 테이블을 복사하여 dept_03 테이블을 만들어보자.
create table dept_03 as select * from dept;

-- 2. dept_03 테이블에서 40번 부서를 삭제한 후에 commit을 해보자
delete from dept_03 where deptno =40;
commit;

-- 3. dept_03 테이블에서 30번 부서를 삭제해보자.
delete from dept_03 where deptno = 30;

-- 4. 이 때 savepoint c1을 설정해보자.
savepoint c1;

-- 5. 부서번호가 20번인 부서를 삭제해 보자.
delete from dept_03 where deptno = 20;

-- 6. savepoint c2를 설정해보자.
savepoint c2;

-- 7. 마지막으로 부서번호가 10번인 부서를 삭제해보자.
delete from dept_03 where deptno = 10;

-- 8. 부서번호가 20번 부서를 삭제하기 바로전으로 되돌림.
-- rollback을 이용하면 특정지점으로 되돌아 가게됨.
rollback to c1;