データ定義言語（DDL）の概要

制約

制約には、次の表に示すものがありました。

さて、これらの制約を定義するときの注意事項にはどんなことがあったでしょう。

設問から、正しい選択肢を見つける要素は「データ型」と「制約」であることが分かります。

データ型に関しては、桁数の指定が明記されていなければ、数値型の場合はNUMBERで十分です。

文字型は、固定長が最適だと読み取れるならCHAR、そうでなければVARCHAR2が使用してあるものを選択すればいいでしょう。 格納するデータのサイズが大きければ、LONGかCLOBですが、問1で説明したようにLONGは制限事項が多いので、それを匂わすことが書いてあればCLOBが正解になるはずです。

日付型には、DATE、TIMESTAMP、INTERVALがありますが、設問中にタイムゾーンを意識する必要があることが書いてない限り、DATEを選択すれば正解です。INTERVALは問2で説明したように期間を扱うデータ型ですから、「注文日は○年○月○日」を管理するのには使わないですね。よって、この時点で選択肢eは不正解と判断しましょう。

制約については、次の点に着目して正解を見つけましょう。

• 列制約と表制約のどちらで宣言されているか
• 列制約と表制約の構文の違い

では、実行例を見ながら、各選択肢を確認していきます。

選択肢a

まず、「列制約と表制約のどちらで宣言されているか」を確認してみましょう。 「ORDER_IDとPROD_IDの組み合わせの値を一意にして、NULL値は設定できないようにする」にはPRIMARY KEY制約を使用するのが最適だと思いますが、UNIQUE制約とNOT NULL制約の組み合わせでも実現できます。選択肢aは組み合わせる方法を使っています。

このとき、注意するべきことは、NOT NULL制約は、列制約でのみ宣言ができるということです。言い換えると、表制約でNOT NULL制約を宣言している選択肢は不正解だということです（選択肢eは不正解）。 そして、複数の列を組み合わせて制約を設定する場合は、表制約を使用しなければいけません。選択肢aは「ORDER_IDとPROD_IDの組み合わせの値を一意」にするためには表制約が使用されています。

次に「列制約と表制約の構文の違い」を確認します。 まず、表制約ではすべての列を宣言した後に制約を定義するので、制約タイプの後に、制約を定義する列を明記する必要があります。 例えば、制約タイプがUNIQUE、制約を定義する列が(order_id,prod_id)の場合、表制約の記述はUNIQUE(order_id,prod_id)となります。

さらに、FOREIGN KEY制約に関しては、列制約ではFOREIGN KEYという制約のタイプを表すキーワードは不要で、参照先（references 表名(列名)）のみ指定します。

選択肢aは以上のことが満たされており、CHECK制約とDEFAULTの定義も適切なので、正解です。

SQL> --選択肢a
SQL> CREATE TABLE order_items
  2  (order_id   NUMBER NOT NULL
  3  ,prod_id    NUMBER NOT NULL REFERENCES products(prod_id)
  4  ,order_date DATE DEFAULT SYSDATE
  5  ,qty        NUMBER CHECK (qty >= 1)
  6  ,CONSTRAINTS oi_uk UNIQUE(order_id ,prod_id));

表が作成されました。

選択肢ｂ

選択肢bで着目する点は、2つあります。 1つは、FOREIGN KEY制約が表制約で定義してあることです。選択肢aの解説で述べたように、表制約の場合にはFOREIGN KEYという制約のタイプを表すキーワードが必要です。

次に着目する点は、1つの列に対して2つの制約を同時に列制約で宣言していることです。 次の実行例から確認できるように、列制約では1つの列に2つの制約を同時に宣言することが可能です。 また、CONSTRAINTSキーワードは制約名を定義しているだけなので、省略できます（デフォルト名が適用されるだけ）。 つまり、選択肢bのCREATE TABLE文は、構文としては間違っていません。表も作成されます。けれども、実際に値を挿入してみると、設問の要求どおりではないことが分かります。

SQL> --選択肢b
SQL> CREATE TABLE order_items
  2  (order_id   NUMBER NOT NULL UNIQUE
  3  ,prod_id    NUMBER NOT NULL UNIQUE
  4  ,order_date DATE DEFAULT SYSDATE
  5  ,qty        NUMBER CHECK (qty >= 1)
  6  ,CONSTRAINTS oi_fk FOREIGN KEY(prod_id) REFERENCES products(prod_id));

表が作成されました。

SQL> insert into order_items values(1,100,SYSDATE,2);

1行が作成されました。

SQL> insert into order_items values(2,100,SYSDATE,4);
insert into order_items values(2,100,SYSDATE,4)
*
行1でエラーが発生しました。:
ORA-00001: 一意制約(HR.SYS_C007014)に反しています

設問では「ORDER_IDとPROD_IDの組み合わせの値を一意に」といっているのに、選択肢bではORDER_IDとPROD_IDのそれぞれで値の一意チェックが入っています。設問の条件に沿えば、(ORDER_ID,PROD_ID)=(1,100)と(ORDER_ID,PROD_ID)=(2,100)は挿入できていいのに、実行してみると「prod_id列に挿入しようとしている「100」が重複している」といってエラーになっています。

このように、選択肢bは表の作成はできても、設問の意図に適した表ではないので不正解です。

選択肢c

選択肢cは、「ORDER_IDとPROD_IDの組み合わせの値を一意にして、NULL値は設定できないようにする」という要件をPRIMARY KEY制約で実現しています。選択肢cは正解です。そのほかの要件に関しては、選択肢aとbで説明したとおりです。

SQL> --選択肢c
SQL>  CREATE TABLE order_items
  2  (order_id   NUMBER
  3  ,prod_id    NUMBER
  4  ,order_date DATE DEFAULT SYSDATE
  5  ,qty        NUMBER CHECK (qty >= 1)
  6  ,CONSTRAINTS oi_pk PRIMARY KEY(order_id ,prod_id)
  7  ,CONSTRAINTS oi_fk FOREIGN KEY(prod_id) REFERENCES products(prod_id));

表が作成されました。

選択肢d

選択肢aの解説で、列制約でしか定義できないのはNOT NULLと説明しましたが、DEFAULTも列制約でしか定義できません。 厳密にいうと、DEFAULTは制約ではなく、列制約や表制約という言葉を使って理解するのはおかしいので、DEFAULTは列ごとに定義すると覚えてください。したがって、あたかも表制約で定義しているかのような選択肢dは不正解です。

SQL> --選択肢d
SQL>  CREATE TABLE order_items
  2  (order_id   NUMBER
  3  ,prod_id    NUMBER
  4  ,order_date DATE
  5  ,qty        NUMBER
  6  ,CONSTRAINTS oi_df DEFAULT (order_date = SYADATE)
  7  ,CONSTRAINTS oi_ck CHECK (qty >= 1)
  8  ,CONSTRAINTS oi_pk PRIMARY KEY(order_id ,prod_id)
  9  ,CONSTRAINTS oi_fk FOREIGN KEY(prod_id) REFERENCES products(prod_id));
,CONSTRAINTS oi_df DEFAULT (order_date = SYADATE)
                   *
行6でエラーが発生しました。:
ORA-00904: : 無効な識別子です。

選択肢e

選択肢eも不正解です。 選択肢aで説明したとおり、order_date列にINTERVAL型を使用しているので要件を満たしていないことと、NOT NULL制約を表制約で定義しようとしていることが不正解の理由です。

SQL> --選択肢e
SQL>  CREATE TABLE order_items
  2  (order_id   NUMBER
  3  ,prod_id    NUMBER
  4  ,order_date INTERVAL YEAR TO MONTH DEFAULT SYSDATE
  5  ,qty        NUMBER CHECK (qty >= 1)
  6  ,CONSTRAINTS oi_nn NOT NULL (order_id ,prod_id)
  7  ,CONSTRAINTS oi_uk UNIQUE (order_id ,prod_id)
  8  ,CONSTRAINTS oi_fk FOREIGN KEY(prod_id) REFERENCES products(prod_id));
,CONSTRAINTS oi_nn NOT NULL (order_id ,prod_id)
                   *
行6でエラーが発生しました。:
ORA-00904: : 無効な識別子です。