Oracle SQL – 테이블 생성, 시퀀스, 제약조건 정리

1. 테이블 생성 (CREATE TABLE)

CREATE TABLE users (
    user_idx NUMBER PRIMARY KEY,
    id       VARCHAR2(20) NOT NULL,
    name     VARCHAR2(20) NOT NULL,
    password VARCHAR2(20) NOT NULL,
    email    VARCHAR2(50),
    address  VARCHAR2(200)
);

• Oracle은 정수 타입을 NUMBER로 통일한다.
• 문자열은 VARCHAR2를 사용한다 (VARCHAR2는 가변 길이 문자열).
• 스키마 지정 시:

CREATE TABLE hr.employees (...);

• 현재 스키마에서 내가 가진 테이블은 다음으로 확인 가능:

SELECT table_name FROM user_tables;

---

2. 자동 증가 컬럼 – MySQL vs Oracle

MySQL

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);
INSERT INTO users (name) VALUES ('Alice'); -- id 자동 증가

Oracle (전통 방식 – 11g 이하)

Oracle에는 AUTO_INCREMENT가 없다.
시퀀스 + 트리거를 사용한다.

CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER trg_users_id
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  :NEW.user_idx := user_seq.NEXTVAL;
END;

Oracle (12c 이상 – 간단한 방식)

CREATE TABLE users (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR2(50)
);

---

3. 시퀀스 (SEQUENCE)

CREATE SEQUENCE user_id_seq
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;

• START WITH: 시작 값
• INCREMENT BY: 증가 단위
• NOCACHE: 캐싱 안 함
• NOCYCLE: 값 재사용 안 함

사용 예시

SELECT user_id_seq.NEXTVAL FROM dual;

INSERT INTO users (user_idx, name)
VALUES (user_id_seq.NEXTVAL, 'Alice');

확인 및 삭제

SELECT sequence_name FROM user_sequences;
DROP SEQUENCE user_id_seq;

---

4. 제약조건 (CONSTRAINT)

(1) 기본키 (PRIMARY KEY)

• 컬럼 레벨 방식

CREATE TABLE department (
    deptno NUMBER(2) CONSTRAINT dept_deptno_pk PRIMARY KEY,
    dname  VARCHAR2(15),
    loc    VARCHAR2(15)
);

• 테이블 레벨 방식 (복합키 가능)

CREATE TABLE department3 (
    deptno NUMBER(2),
    loc    VARCHAR2(15),
    CONSTRAINT dept3_pk PRIMARY KEY (deptno, loc)
);

➡︎ 부서번호가 같아도 지역이 다르면 입력 가능.

---

(2) 유니크 (UNIQUE)

CREATE TABLE department4 (
    deptno NUMBER(2) PRIMARY KEY,
    dname  VARCHAR2(20) CONSTRAINT dept4_dname_uk UNIQUE
);

• dname에 같은 값이 들어오면 오류 발생.

---

(3) 외래키 (FOREIGN KEY)

CREATE TABLE employee (
    emp_id  NUMBER PRIMARY KEY,
    deptno  NUMBER,
    CONSTRAINT emp_dept_fk FOREIGN KEY (deptno)
        REFERENCES department(deptno)
);

---

(4) 체크 (CHECK)

CREATE TABLE product (
    product_id NUMBER PRIMARY KEY,
    price NUMBER CONSTRAINT product_price_ck CHECK (price > 0)
);

• 조건을 위반하면 입력 불가.

---

5. 데이터 사전 (Data Dictionary)

Oracle은 시스템 뷰를 통해 제약조건, 시퀀스 등을 확인할 수 있다.

제약조건 조회

SELECT * 
FROM user_constraints 
WHERE table_name = 'DEPARTMENT';

• 주요 타입:
  • P → PRIMARY KEY
  • U → UNIQUE
  • C → CHECK
  • R → FOREIGN KEY

 

 

6. MyBatis + Oracle 시퀀스 연동

(1) 매퍼 XML (insert 구문)

<insert id="insertUser" parameterType="User">
    INSERT INTO users (user_id, username, email)
    VALUES (user_seq.NEXTVAL, #{username}, #{email})
</insert>

➡︎ 트리거를 쓰지 않고, XML에서 직접 NEXTVAL 호출 가능.

---

(2) 매퍼 XML (selectKey 활용)

트리거 없이, MyBatis에서 시퀀스를 먼저 조회해오는 방식도 자주 사용한다.

<insert id="insertUser" parameterType="User" useGeneratedKeys="false">
    <selectKey keyProperty="userId" resultType="int" order="BEFORE">
        SELECT user_seq.NEXTVAL FROM dual
    </selectKey>
    INSERT INTO users (user_id, username, email)
    VALUES (#{userId}, #{username}, #{email})
</insert>

• order="BEFORE" → INSERT 전에 실행
• keyProperty="userId" → 시퀀스 값을 DTO(User 객체)의 userId에 매핑

---

(3) DTO (User.java or User.cs in C# 프로젝트)

public class User {
    private int userId;
    private String username;
    private String email;

    // getter, setter
}

➡︎ userId가 MyBatis에서 자동 세팅됨.

 

• Oracle에서는 AUTO_INCREMENT 대신 시퀀스 + 트리거 또는 MyBatis selectKey 활용
• 제약조건은 반드시 명명 규칙(CONSTRAINT 이름)을 붙여야 관리 용이
• 데이터 사전(user_constraints, user_sequences)을 활용하면 구조 점검 가능
• MyBatis에서는 트리거 방식(DB가 알아서 번호 생성)과 selectKey 방식(MyBatis에서 번호 받아와 DTO에 넣음) 두 가지를 선택 가능