SQL_시퀀스, 트랜잭션, 무결성제약조건

시퀀스(Sequence)

 

• 일련번호를 발행하는 오라클의 데이터베이스 객체다
• 시퀀스명은 _seq로 끝나도록 짓는것이 관례다

 

시퀀스 객체 생성

  create sequence 시퀀스명;
  
  create sequence category_seq;
  create sequence product_seq;

 

 

일련번호 발행

• 시퀀스명.nextval : 새로운 일련번호를 제공한다
  • 시퀀스명.currval : 직전에 발행된 일련번호를 다시 제공한다

 

시퀀스 사용하기

 

• INSERT문에서 시퀀스를 사용해서 새로운 번호를 저장되게 하기

  insert into tb_categories(cate_no, cate_name, cate_parent_no)
  values(cate_seq.nextval, ?, ?);

  insert into tb_products(prod_no, prod_name, prod_maker, prod_price, prod_discount_rate)
  values(product_seq.nextval, ?, ?, ?, ?);

 

• SELECT문에서 시퀀스를 사용해서 새로운 번호 발행하기

  select cate_seq.nextval from dual;
  select product_seq.nextval from dual;

 

 

시퀀스 사용예시

 

 

 

 

 

 

트랜잭션(Transaction)

• 논리적인 작업 단위를 구성하는 데이터 조작 명령문의 모음

 

트랜잭션 처리 대상

• 데이터 조작어(DML)
  • 데이터베이스 테이블에 데이터를 추가, 갱신 또는 삭제할 때 사용하는 명령어
  • INSERT, UPDATE, DELETE 명령어

 

트랜잭션 사용예제

-- 이체하기
내 계좌의 잔액 감소 - UPDATE
상대방 계좌의 잔액 증가 - UPDATE
내 계좌에 이체 발송 내용 추가 - INSERT
상대방 계좌에 이체 수신 내용 추가 - INSERT
이체하기는 4번의 DML 작업으로 구성된다. <---- 논리적인 작업단위

 

-- 구매하기
구매 정보 저장 - INSERT
배송 정보 저장 - INSERT
결재 정보 저장 - INSERT
상품 재고 감소 - UPDATE
상품 판재지수 증가 - UPDATE
고객의 포인트 증가 - UPDATE
구매하기는 6번의 DML 작업으로 구성된다. <---- 논리적인 작업단위

 

 

트랜잭션의 사용 목적

• 트랜잭션내의 명령문 중 하나라도 실행되지 못하면 나머지 명령문도 모두 데이터베이스 바녕이 취소되어야 한다
• 트랜잭션내의 명령문이 모두 성공적으로 실행되면 데이터베이스에 영구적으로 실행결과를 영구적으로 반영시킨다
• 데이터의 일관성이 깨지지 않도록 하는 것이 목적이다

 

트랜잭션 처리 명령어

• COMMIT : 트랜잭션 내의 DML 실행결과를 데이터베이스에 반영한다
• ROLLBACK : 트랜잭션 내의 DML 실행결과의 데이터베이스 반영을 전부 취소한다

 

 

트랜잭션의 시작과 종료

• 트랜잭션의 시작
  • 첫번째 DML 명령이 실행될때 새 트랜잭션 자동으로 시작된다
• 트랜잭션의 종료
  • COMMIT 또는 ROLLBACK 명령이 실행될 때 기존 트랜잭션이 종료된다
  • 트랜잭션이 종료되면 새로운 트랜잭션이 자동으로 시작된다
  • SQL 명령어 편집툴을 종료할때 기존 트랜잭션이 종료된다
  • 시스템에 장애가 발생할 떄 기존 트랜잭션이 종료된다

 

COMMIT과 ROLLBACK의 이해

• COMMIT/ROLLBACK 실행 이전
  • 현재 사용자는 SELECT문을 사용해서 DML작업결과를 검토할 수 있다
  • 현재 사용자의 DML작업결과를 다른 사용자는 볼 수 없다 
  • 현재 사용자가 작업하는 관련행은 행 잠금 때문에 다른 사용자가 관련행의 데이터를 변경할 수 없다
  • 데이터를 이전상태로 되돌릴 수 있다
• COMMIT 실행 후
  • DML 작업 결과가 데이터베이스에 영구적으로 저장된다
  • 모든 사용자가 작업 결과를 볼 수 있다
  • 관련행의 행 잠금이 해제되어 다른 사용자가 행을 조작할 수 있다
• ROLLBACK 실행 후
  • DML작업 결과의 반영이 취소된다
  • 데이터가 이전상태로 복구된다
  • 관련행의 행 잠금이 해제된다

 

트랜잭션의 성질

원자성(Atomicity)	트랜잭션과 관련된 작업들은 부분적인 성공을 허용하지 않는다
일관성(Consistency)	트랜잭션이 종료되면 언제나 데이터는 일관성을 유지하는 상태가 된다
고립성(Isolation)	트랜잭션 수행 시 다른 트랜잭션이 끼어들지 못한다
지속성(Durability)	성공적으로 수행된 트랜잭션은 데이터베이스에 영구적으로 반영된다

 

 

트랜잭션 동작예시

 

 

 

 

 

무결성 제약조건

• 테이블에 유효하지 않은 데이터가 입력되는 것을 방지한다
• 해당 테이블에 데이터가 추가, 삭제, 변경될 때마다 무결성 제약조건을 검사한다

 

무결성 제약조건 종류

NOT NULL 제약조건	해당 컬럼에 NULL값이 입력되지 못하도록 지정한다 고객테이블의 이름컬럼, 도서테이블의 제목컬럼, 상품테이블의 상품명컬럼
UNIQUE 제약조건 (고유키 제약조건)	해당 컬럼의 값은 테이블전체에서 고유한 값을 가져야한다 NULL값의 입력은 가능하다 고객테이블의 핸드폰번호, 사원테이블의 주민번호컬럼, 학생테이블의 이메일컬럼
PRIMARY KEY 제약조건 (기본키 제약조건)	테이블의 각 행을 고유하게 식별할 수 있는 값을 가져야한다 테이블전체에서 고유한 값을 가져야 하고, NULL값을 허용하지 않는다 학생테이블의 학번, 사원테이블의 사원번호, 과목테이블 과목코드, 택배테이블의 송장번호
FOREIGN KEY 제약조건 (외래키 제약조건, 참조키 제약조건)	해당 컬럼의 값은 다른 테이블 혹은 같은 테이블의 특정 컬럼의 값과 관련된 값만 가져야 한다 종속 테이블(자식테이블) : FOREIGN KEY로 설정된 컬럼은 부모테이블의 기존 값과 일치하거나 NULL이어야 한다 참조 테이블(부모테이블) : 자식테이블이 참조하는 부모테이블의 컬럼은 PRIMARY KEY 혹은 UNIQUE 제약조건이 정의된 컬럼이어야 한다 사원테이블(종속테이블)의 부서아이디는 부서테이블(참조테이블)의 부서 아이디를 참조한다 부서테이블(자식테이블)의 관리자 아이디는 사원테이블(부모테이블)의 사원번호를 참조한다 사원테이블(자식테이블)의 직종아이디는 직종테이블(부모테이블)의 직종아이디를 참조한다 자식테이블에서 참조하고 있는 부모테이블의 행은 삭제할 수 없다
CHECK 제약조건	제시된 조건을 만족하는 값을 가져야한다 사원테이블의 급여는 0보다 큰 값을 가져야한다 학생테이블의 학년은 1,2,3,4 중의 하나여야 한다 고객테이블의 성별은 M, F 중의 하나여야 한다 학생테이블의 성적은 0.0 ~ 4.5 사이의 값이어야 한다

 

 

제약조건의 정의

• 컬럼 레벨 제약조건 정의

  CREATE TABLE 테이블명 ( 
    컬럼명 데이터타입 [CONSTRAINT 제약조건별칭] 제약조건,
    컬럼명 데이터타입 [CONSTRAINT 제약조건별칭] 제약조건,
    ...
  );

 

• 테이블 레벨 제약조건 정의

  CREATE TABLE 테이블명 (
    컬럼명 데이터타입,
    컬럼명 데이터타입,
    ...,
    [CONSTRAINT 제약조건별칭] 제약조건 (컬럼명),
    [CONSTRAINT 제약조건별칭] 제약조건 (컬럼명, 컬럼명, ...)
  );
  
  -- 하나 이상의 컬럼을 조합하여 제약조건을 정의할 수 있다.
  -- NOT NULL 제약조건을 제외한 모든 제약조건을 정의할 수 있다.

 

 

제약조건 정의하기

 

• NOT NULL 제약조건 정의

  CREATE TABLE users (
    user_name varchar2(30) NOT NULL,
    user_tel varchar2(20) CONSTRAINT user_tel_nn NOT NULL,
    ...
  );

  -- NOT NULL 제약조건이 정의되지 않은 컬럼은 기본적으로 NULL 값을 가질 수 있다.

 

• UNIQUE 제약조건 정의

  CREATE TABLLE users (
    user_name varchar2(30),
    user_tel varchar2(20) CONSTRAINT user_tel_nn NOT NULL,
    user_email varchar2(200) CONSTRAINT user_email_uk UNIQUE,
    ...,
    CONSTRAINT user_tel_uk UNIQUE(user_tel)
  );
  
  -- user_tel 컬럼은 NOT NULL, UNIQUE 제약조건이 정의되어 있음
  -- user_tel의 NOT NULL제약조건은 컬럼 레벨 제약조건 방식으로 정의 UNIQUE제약조건은 테이블 레벨 제약조건 방식으로 정의했음
  -- user_email 컬럼은 UNIQUE 제약조건이 정의되어 있음, NULL값이 허용됨

 

• PRIMARY KEY 제약조건 정의

  CREATE TABLE users (
    user_no number(10) CONSTRAINT user_no_pk PRIMAY KEY,
    ...
  );
  
  CREATE TABLE users (
    user_no number(10),
    ...,
    CONSTRAINT user_no_pk PRIMARY KEY(user_no)
  );
  
  CREATE TABLE course_registerations (
    stud_no number(10),
    course_no number(5),
    ...,
    CONSTRAINT course_regist_pk PRIMARY KEY(stud_no, course_no)
  );
  
  -- 하나 이상의 열을 조합해서 기본키 제약조건 정의하기

 

• FOREIGN KEY 제약조건

  CREATE TABLE emps(
    ...
    dept_id number(10) CONSTRAINT emp_dept_id_fk REFERENCES depts(dept_id),
    ...
  );
  -- emps 테이블의 dept_id 컬럼의 값은 depts테이블의 dept_id 값을 참조한다.

  CREATE TABLE emps(
    ...
    dept_id number(10),
    ...,
    CONSTRAINT emp_dept_id_fk FOREIGN KEY (dept_id) REFERENCES depts(dept_id),
  );
  -- REFERENCES 키워드와 참조대상테이블(컬럼명)을 지정한다.
  -- 참조대상 컬럼은 PRIMARY KEY 혹은 UNIQUE 제약조건이 정의된 컬럼만 가능하다.

 

• CHECK 제약조건

  CREATE TABLE products (
    ...
    pro_price number(10) CONSTRAINT pro_price_ck CHECK (pro_price > 0),
    pro_sell varchar2(20) CONSTRAINT pro_sell_ck CHECK (pro_sell in ('판매중', '재고부족', '절판')),
    pro_discount number(4, 3) CONSTRAINT pro_discount_ck CHECK (pro_discount >= 0.0 and pro_discount <= 0.5),
  );

 

 

 

제약조건 예시

 

TABLE에 제약조건들을 지정해서 생성한 모습

 

 

제약조건이 있는 테이블 삭제