Oracle Database 19cで、どの値を保存できるかをDB側に宣言する制約を説明します。NULLを許すか、一意性をどの列の組合せで守るか、親行の削除をどう扱うかを分けて設計しましょう。DEFAULTは制約とは別の列の既定値です。

関連するオブジェクトはDBオブジェクト、製品の入口はOracleから確認できます。

掲載例は一次資料に基づく学習用で、Oracleでの実行確認は行っていません。実際の表定義・型・権限・NLS・クライアントを確認し、定義変更例は独立した学習環境で検討してください。

この記事の要点

制約 (Constraint) とは

制約はアプリ以外の書込み経路にも同じルールを適用する仕組みです。ただし、定義しただけで「常に全ての不正データを拒否する」とは限りません。制約の有効・無効、既存行の検査済みか、検査時点も確認します。アプリの入力チェックを不要にするものでもありません。Oracleの整合性と制約の状態

Oracleの6種類の整合性制約にはREFが含まれ、DEFAULTは含まれません。制約の種類と構文

以下のCREATE / INSERT / ALTER例は操作と定義の学習用です。同名のusersなどを別の定義で作る例はそれぞれ独立しており、1本の連続スクリプトではありません。既存の業務表へそのまま適用せず、表・列・型・既存値と依存関係を確認してください。

NOT NULL: 必須項目

次の例はINT / VARCHARを使った一般SQL表記です。Oracle 19cでも対応する型として解釈されますが、Oracle向け新規定義ではNUMBER / VARCHAR2を明示すると型の意図を読みやすくできます。emailにDEFAULTがないこの定義で、emailを省略したINSERTはNOT NULL違反です。最後のERRORコメントはエラーの意味で、Oracleの出力文字列そのものではありません。

一般SQL表記の独立した定義です。emailにはDEFAULTがなく、省略したINSERTはNOT NULL違反です。ERRORコメントは意味の説明でOracleの実出力ではありません。

CREATE TABLE users (
    id    INT PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

INSERT INTO users (id, name) VALUES (1, 'Alice');
-- ERROR: email に NULL を入れようとした

空文字を必須の文字列として保存する設計にも注意します。Oracle 19cでは長さ0の文字列はNULLとして扱われ、NOT NULLを満たしません。OracleのNULL

UNIQUE: 重複禁止

単一列のUNIQUEは複数のNULLを許します。しかし、複合UNIQUEに「NULLがあればどの重複でも許される」という説明は当てはまりません。次の一般SQL表記の2定義は独立した例です。

独立した一般SQL定義です。単一列の複数NULLと複合UNIQUEの一部NULLは同じ扱いではありません。(1, NULL)同士の重複はOracleで違反になります。

CREATE TABLE users (
    id    INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

-- 複合 UNIQUE (組み合わせで一意)
CREATE TABLE enrollments (
    student_id INT,
    course_id  INT,
    CONSTRAINT uq_enroll UNIQUE (student_id, course_id)
);

uq_enrollに対するOracle 19cの考え方は次のとおりです。比較対象は制約が有効な同一表内の2行です。

必ず両方を入力させたいなら、UNIQUEだけでなく各列にNOT NULLを定義します。一意性には列の照合規則も関わります。UNIQUEのNULL条件

PRIMARY KEY: 主キー

主キーは単一列でも複合列でも定義できますが、1表に1つです。複合主キーなら全ての構成列が非NULLで、組合せが一意になります。次は独立した2つの一般SQL例です。

単一主キーと複合主キーの独立した例です。主キー指定だけで通常のOracle heap表が主キー順にクラスタ化されるわけではありません。全構成列が非NULLで組合せが一意になります。

-- 単一カラム主キー
CREATE TABLE products (
    id   INT PRIMARY KEY,
    name VARCHAR(100)
);

-- 複合主キー
CREATE TABLE order_items (
    order_id INT,
    item_id  INT,
    qty      INT,
    PRIMARY KEY (order_id, item_id)
);

Oracleの通常の表はheap-organizedで、PRIMARY KEYを付けるだけで行が主キー順にクラスタ化されるわけではありません。主キーの強制には利用可能な既存索引を使う場合と、索引を作成する場合があります。索引構成表は別途ORGANIZATION INDEXで指定する表の構成です。「主キーだから必ず自動クラスタ索引で高速」とは言えません。表の構成

FOREIGN KEY: 参照整合性

参照先は別表だけでなく同じ表にもできます。複合外部キーでは、対応する親キーの列順・型などを合わせます。Oracleでは複合外部キーのいずれかの列がNULLなら親との一致を要求されないため、完全な参照を必須にするなら構成列全てのNOT NULLも検討します。外部キーの定義

Oracle 19cの基本例

次は独立した説明用定義です。ON DELETEを省略しており、参照する注文が残っている顧客の削除は拒否されます。ON DELETE RESTRICTやON UPDATE CASCADEをOracleの構文として追加しません。

-- Oracle Database 19c / 新規の学習用表を想定
CREATE TABLE customers_oracle (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50)
);

CREATE TABLE orders_oracle (
    id NUMBER PRIMARY KEY,
    customer_id NUMBER NOT NULL,
    CONSTRAINT fk_orders_oracle_customer
        FOREIGN KEY (customer_id) REFERENCES customers_oracle(id)
);

MySQL InnoDBとの比較

次の枠はMySQLのInnoDB表を前提とした例です。ON DELETE RESTRICTとON UPDATE CASCADEを指定しています。Oracleの構文としては使えず、更新時の連鎖をOracleでも同じように選べるという説明でもありません。MySQL外部キー

MySQL InnoDB用の比較例です。ON DELETE RESTRICTとON UPDATE CASCADEはOracleの構文ではありません。Oracleの基本例は別枠で示し、違反解消のためにCASCADEや子削除を自動選択しません。

CREATE TABLE customers (
    id   INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

親削除・更新の動作を製品別に選ぶ

CASCADEは整合性を保つために子データも削除する選択です。外部キー違反を消すためだけに選ばず、削除の業務ルールに合うか確認します。OracleのON DELETEの選択肢はreferences_clauseの構文を参照してください。

CHECK: 行の条件

CHECKは「TRUEだけを許す」のではなく、条件がTRUEまたはUNKNOWNなら許し、FALSEを拒否します。次の一般SQL表記のageとstatusにはNOT NULLがないため、NULLを拒否する例ではありません。複数列にまたがる条件は表レベルのCHECKで定義します。任意のSQLを置けるわけではなく、他表参照やサブクエリ、SYSDATEなど使用できないものがあります。Oracle整合性のCHECK

一般SQL表記の独立したCHECK例です。age/statusにはNOT NULLがなく、NULLによるUNKNOWNも許します。TRUEだけを許す例や他表参照・任意SQLが可能な例ではありません。

CREATE TABLE employees (
    id     INT PRIMARY KEY,
    age    INT CHECK (age >= 18 AND age <= 70),
    status VARCHAR(10) CHECK (status IN ('active','retired','leave'))
);

必須の年齢範囲なら、例えばage NUMBER NOT NULL CHECK (age >= 18 AND age <= 70)とします。2つの列の大小関係をCHECKする場合も、どちらかの欠落を許すかを先に決めます。

MySQLとの比較では、8.0.16より前の限定的なCHECK構文は解析されても無視され、8.0.16以降はCHECKの実装が変わっています。これはOracleの導入時期の説明ではありません。MySQLの現在の制約でもENFORCED / NOT ENFORCEDを確認します。MySQL 8.0 CHECK

DEFAULT: 制約とは別の既定値

DEFAULTは許可される値の範囲を検査する制約ではありません。Oracleで列を省略したINSERTへ値を補う場合に使い、通常のDEFAULTは明示的なNULLを自動置換しません。DEFAULT ON NULLは別の指定で、19cではINSERTでNULLと評価される値にも適用され、NOT NULLとNOT DEFERRABLEも暗黙に指定されます。CREATE TABLEのDEFAULT / ON NULL

-- Oracle Database 19c / 独立した学習用定義
CREATE TABLE posts_oracle (
    id NUMBER PRIMARY KEY,
    title VARCHAR2(200) NOT NULL,
    status VARCHAR2(10) DEFAULT 'draft' NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);

この定義ではstatusの省略時にdraftを補いますが、明示的なNULLをdraftに変換する定義ではありません。created_atはタイムゾーン付きの型を選んでいます。CURRENT_TIMESTAMPはセッションのタイムゾーンに基づくTIMESTAMP WITH TIME ZONEを返します。CURRENT_TIMESTAMP

MySQLのDATETIMEとの比較

次の枠はMySQL用の定義で、DATETIMEと列属性の並びをOracleへそのまま移す例ではありません。日時型の名前やタイムゾーンの保存方法も別に選びます。

MySQLのDATETIMEを使う比較例です。型名と列属性の並びをOracleへそのまま移せません。通常のDEFAULTは明示NULLを置き換えず、DEFAULT ON NULLとは別です。

CREATE TABLE posts (
    id         INT PRIMARY KEY,
    title      VARCHAR(200) NOT NULL,
    status     VARCHAR(10)  NOT NULL DEFAULT 'draft',
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

後から追加する: ALTER TABLE

名前付き制約は識別や変更の対象を明確にできますが、名前を付けるだけで運用中の変更が安全になるわけではありません。既存行のNULL・重複・参照切れと、同名制約の有無・既存定義・索引・ロックへの影響を確認します。無効化でエラーを回避する前提にはしません。

次の枠のADD例とusersのDROP CONSTRAINTはOracleで使う形ですが、対象制約が既にある場合や、ここまでのCREATE例へ重ねて実行することは想定していません。最後のDROP FOREIGN KEYの1文だけはMySQLです。削除例は追加例の後に実行すべき手順ではなく、保護を外す操作の構文比較です。

独立した構文比較で、追加後に削除する推奨手順ではありません。最後のDROP FOREIGN KEYだけはMySQLです。OracleはDROP CONSTRAINTを使い、削除すると整合性の保護が失われます。

-- 追加
ALTER TABLE users
  ADD CONSTRAINT uq_users_email UNIQUE (email);

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
      FOREIGN KEY (customer_id) REFERENCES customers(id);

-- 削除
ALTER TABLE users DROP CONSTRAINT uq_users_email;
ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;  -- MySQL

Oracleで外部キーを名前指定で削除する形はALTER TABLE orders DROP CONSTRAINT fk_orders_customerです。削除すると参照整合性の保護が失われます。Oracle制約の定義と状態

よくある質問

アプリのバリデーションがあればDB制約は不要？

役割が違います。アプリは入力の案内や業務処理を行い、DB制約は他の書込み経路でも守りたい整合性を表します。ただし全ての業務ルールをCHECKで書けるわけではなく、制約の状態・既存行の検証も必要です。

複合UNIQUEと単一UNIQUEを両方付けられる？

異なる列の組合せなら定義できますが、email単独の一意性と(tenant_id, email)の一意性では要件が違います。前者は全体で同じメールを禁止し、後者はテナントごとの重複を禁止する設計です。NULLの条件と余分な索引の保守コストまで考えず「重複定義も問題ない」とは判断しません。

外部キーを付けると遅くなる？

整合性検査の仕事が増えますが、「親側に索引があればコストは最小」とは限りません。Oracleでは子側の外部キー列に索引があるかも、親キーの変更・削除時のロックや処理計画に関わります。自動作成されると決めつけず、対象処理と索引の定義を確認します。Oracleの参照整合性