【Oracle】テーブルを作成する方法完全ガイド｜CREATE TABLE・全データ型・制約・CTAS・表領域指定まで解説

CREATE TABLE テーブル名 (
    列名1  データ型  [DEFAULT デフォルト値]  [列レベル制約],
    列名2  データ型  [DEFAULT デフォルト値]  [列レベル制約],
    ...
    [テーブルレベル制約]
)
[TABLESPACE 表領域名];

CREATE TABLE emp (
    emp_id    NUMBER(10)     NOT NULL,
    emp_name  VARCHAR2(100)  NOT NULL,
    dept_id   NUMBER(5),
    salary    NUMBER(10, 2),
    hire_date DATE           DEFAULT SYSDATE,
    CONSTRAINT pk_emp PRIMARY KEY (emp_id)
);

CREATE TABLE emp (
    emp_id    NUMBER(10)    CONSTRAINT pk_emp PRIMARY KEY,
    emp_name  VARCHAR2(100) NOT NULL
);

CREATE TABLE order_detail (
    order_id   NUMBER(10)  NOT NULL,
    line_no    NUMBER(5)   NOT NULL,
    product_id NUMBER(10)  NOT NULL,
    qty        NUMBER(10)  NOT NULL,
    CONSTRAINT pk_order_detail PRIMARY KEY (order_id, line_no)
);

-- 部署マスター（参照される側）
CREATE TABLE dept (
    dept_id   NUMBER(5)     NOT NULL,
    dept_name VARCHAR2(100) NOT NULL,
    CONSTRAINT pk_dept PRIMARY KEY (dept_id)
);

-- 社員テーブル（参照する側）
CREATE TABLE emp (
    emp_id   NUMBER(10)     NOT NULL,
    emp_name VARCHAR2(100)  NOT NULL,
    dept_id  NUMBER(5),
    CONSTRAINT pk_emp     PRIMARY KEY (emp_id),
    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES dept (dept_id)
);

CREATE TABLE emp (
    emp_id   NUMBER(10)  NOT NULL,
    dept_id  NUMBER(5),
    CONSTRAINT pk_emp      PRIMARY KEY (emp_id),
    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id)
        REFERENCES dept (dept_id)
        ON DELETE CASCADE
);

CREATE TABLE emp (
    emp_id    NUMBER(10)    NOT NULL,
    emp_email VARCHAR2(200) NOT NULL,
    CONSTRAINT pk_emp       PRIMARY KEY (emp_id),
    CONSTRAINT uq_emp_email UNIQUE (emp_email)
);

CREATE TABLE emp (
    emp_id   NUMBER(10)    NOT NULL,
    emp_name VARCHAR2(100) NOT NULL,   -- 必須項目
    memo     VARCHAR2(500)             -- NULL許可（省略可能な列）
);

CREATE TABLE emp (
    emp_id   NUMBER(10)    NOT NULL,
    salary   NUMBER(10, 2) NOT NULL,
    gender   CHAR(1),
    CONSTRAINT pk_emp         PRIMARY KEY (emp_id),
    CONSTRAINT ck_emp_salary  CHECK (salary >= 0),
    CONSTRAINT ck_emp_gender  CHECK (gender IN ('M', 'F', 'X'))
);

CREATE TABLE emp (
    emp_id       NUMBER(10)     NOT NULL,
    status       VARCHAR2(10)   DEFAULT 'ACTIVE'  NOT NULL,
    created_at   DATE           DEFAULT SYSDATE   NOT NULL,
    updated_at   TIMESTAMP      DEFAULT SYSTIMESTAMP NOT NULL,
    is_deleted   NUMBER(1)      DEFAULT 0         NOT NULL,
    CONSTRAINT pk_emp PRIMARY KEY (emp_id)
);

CREATE TABLE dept (
    dept_id    NUMBER(5)     NOT NULL,
    dept_code  VARCHAR2(10)  NOT NULL,
    dept_name  VARCHAR2(100) NOT NULL,
    parent_id  NUMBER(5),
    sort_order NUMBER(3)     DEFAULT 0  NOT NULL,
    is_active  NUMBER(1)     DEFAULT 1  NOT NULL,
    created_at DATE          DEFAULT SYSDATE NOT NULL,
    updated_at DATE          DEFAULT SYSDATE NOT NULL,
    CONSTRAINT pk_dept          PRIMARY KEY (dept_id),
    CONSTRAINT uq_dept_code     UNIQUE (dept_code),
    CONSTRAINT fk_dept_parent   FOREIGN KEY (parent_id) REFERENCES dept (dept_id),
    CONSTRAINT ck_dept_active   CHECK (is_active IN (0, 1))
);

CREATE TABLE order_detail (
    order_id     NUMBER(10)     NOT NULL,
    line_no      NUMBER(5)      NOT NULL,
    product_id   NUMBER(10)     NOT NULL,
    qty          NUMBER(10)     NOT NULL,
    unit_price   NUMBER(12, 2)  NOT NULL,
    tax_rate     NUMBER(5, 2)   DEFAULT 0.10  NOT NULL,
    created_at   DATE           DEFAULT SYSDATE NOT NULL,
    CONSTRAINT pk_order_detail    PRIMARY KEY (order_id, line_no),
    CONSTRAINT fk_od_product      FOREIGN KEY (product_id)
                                  REFERENCES product (product_id),
    CONSTRAINT ck_od_qty          CHECK (qty > 0),
    CONSTRAINT ck_od_unit_price   CHECK (unit_price >= 0),
    CONSTRAINT ck_od_tax_rate     CHECK (tax_rate BETWEEN 0 AND 1)
);

-- emp テーブルのデータを emp_bak にコピー
CREATE TABLE emp_bak AS
SELECT * FROM emp;

-- WHERE 1=2 で必ず0件になる条件を使い、構造のみコピー
CREATE TABLE emp_work AS
SELECT * FROM emp WHERE 1 = 2;

-- 特定列・特定部署のみコピー
CREATE TABLE emp_dept10 AS
SELECT emp_id, emp_name, salary
FROM emp
WHERE dept_id = 10;

-- 部署別集計テーブルを作成
CREATE TABLE dept_summary AS
SELECT
    dept_id,
    COUNT(*)          AS emp_count,
    AVG(salary)       AS avg_salary,
    MAX(salary)       AS max_salary
FROM emp
GROUP BY dept_id;

CREATE TABLE emp (
    emp_id   NUMBER(10)    NOT NULL,
    emp_name VARCHAR2(100) NOT NULL,
    CONSTRAINT pk_emp PRIMARY KEY (emp_id)
)
TABLESPACE users;

CREATE TABLE emp (
    emp_id   NUMBER(10)    NOT NULL,
    emp_name VARCHAR2(100) NOT NULL,
    CONSTRAINT pk_emp PRIMARY KEY (emp_id)
        USING INDEX TABLESPACE indx   -- インデックスは indx 表領域
)
TABLESPACE data;                      -- テーブル本体は data 表領域

SELECT table_name, tablespace_name, num_rows, status
FROM USER_TABLES
WHERE TABLE_NAME = 'EMP';

SELECT
    column_id,
    column_name,
    data_type,
    data_length,
    data_precision,
    data_scale,
    nullable,
    data_default
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'EMP'
ORDER BY column_id;

SELECT
    constraint_name,
    constraint_type,
    search_condition,
    r_constraint_name,
    status
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP'
ORDER BY constraint_type, constraint_name;

-- CREATE TABLE 権限を付与
GRANT CREATE TABLE TO scott;

-- 表領域の使用枠を付与（10MB）
ALTER USER scott QUOTA 10M ON users;

-- 表領域の使用を無制限に許可
GRANT UNLIMITED TABLESPACE TO scott;