【学習メモ】テーブル作成における制約のかけ方 ※引用多め

色々な制約方法

• NOT NULL制約
• DEFAULT制約
• PRIMARY KEY制約
• UNIQUE制約
• AUTO_INCREMENT

※本当は外部キー制約などもっとありますが、またの機会に。

NOT NULL制約

テーブルを作成するときにカラムに対して NOT NULL 制約を付けることが可能。書式は次の通り。

CREATE TABLE db_name.tbl_name
  (col_name data_type [NOT NULL | NULL], ...)

例：

create table friends(
  name varchar(10) not null,
  address varchar(10)
  );

補足：作成したテーブルのカラム情報の確認方法

show columns from tbl_name;

DEFAULT制約(カラムにデフォルト値を設定する)

カラムに DEFAULT 制約をつけることでカラムにデフォルトの値を設定できる。データを追加したとき、対象のカラムに値を指定しなかった場合にはデフォルト値がカラムに格納される。MySQL における DEFAULT 制約の使い方について解説。

CREATE TABLE db_name.tbl_name
  (col_name data_type [DEFAULT {literal | (expr)}], ...)

例：

CREATE TABLE hoge
  (
    num1 int default 10,
    str1 varchar(10) default 'Hello',
    num2 float default (rand() + 3.2),
    d date default (CURRENT_DATE)
  );

カラムのデータ型のあとに DEFAULT 定数 と記述することでカラムにデフォルトの値を設定できます。テーブルにデータを追加するとき、すべてのカラムに値を指定する必要はありませんが、デフォルト値が設定されているカラムに値が指定されなかった時にデフォルト値に設定された定数が格納されます。

以前のバージョンではデフォルト値に定数以外で指定できるのは NOW() 、 CURRENT_DATE() 、 CURRENT_TIMESTAMP() の3つだけでしたが、 **MySQL 8.0.13 以降では定数の他に組み込み関数や演算子を指定することができます。**定数以外を指定する場合は括弧()で囲むことで値と区別します。

PRIMARY KEY制約(主キー/プライマリキーを設定する)

プライマリーキー(主キー)とは作成したテーブルの中の1つまたは複数のカラムの組み合わせに対して設定するもので、テーブルに格納されているデータに格納されているデータを識別するための目印のようなものです。ここではプライマリキーについての簡単な解説と、MySQLのテーブルでプライマリキーを設定して利用する方法について解説します。

プライマリーキー(主キー)とは

テーブルには多くのデータが含まれており、 1 つ 1 つのデータには複数のカラムの値が格納されています。格納されたすべてのデータの中から 1 つのデータを特定したいときに、 1 つまたは複数のカラムの値を検索してデータを特定しますが、その時に最も適したカラムに設定されるのがプライマリーキーです。

**カラムにプライマリーキー制約を設定すると、カラムには他のデータの値を重複することのない値しか格納することができなくなります。また NULL も格納することができません。**その結果、プライマリーキー制約が設定されたカラムの値を検索することで、テーブルの中でただ一つのデータを特定することができます。

**プライマリーキーが設定されたカラムに対しては自動的にインデックスが作成されます。**インデックスとは指定したカラムのデータを取り出して検索用にまとめたもので、インデックスを作成しておくとテーブルのデータを高速に検索できます。インデックスはテーブルの中で複数作成することができますが、その中でも重複する値が格納されておらず特に重要なカラムに対してプライマリーキーが設定されます。

プライマリーキーと似た制約にユニーク制約というものがあります。ユニーク制約が設定されたカラムも重複した値を格納することができませんが、 NULL を格納することができ、またテーブルに複数作成できるという点が異なります。

PRIMARY KEY制約の使い方

CREATE TABLE db_name.tbl_name
  (col_name data_type NOT NULL PRIMARY KEY, ...)

または

CREATE TABLE db_name.tbl_name
  (col_name data_type NOT NULL, ..., PRIMARY KEY(col_name, ...))

カラムのデータ型のあとに PRIMARY KEY を記述します。 PRIMARY KEY はテーブルごとに 1 つしか設定することができず、また設定するカラムには NOT NULL 制約を合わせて設定する必要があります。明示的に NOT NULL 制約を設定しなくてもエラーとはなりませんが、自動的に NOT NULL 制約が設定されます。

補足：インデックスって？

業務でデータベースの操作をする場合、データが大量に登録されているテーブルへアクセスする場合に索引（INDEX）を作成すると、SQLクエリの実行が劇的に早くなることがある。

索引（INDEX）を作成した方が良いケース

• 項目数／データ数が多い大規模な表の1%～15%程の行を頻繁にアクセスする場合
• WHERE句で頻繁に使用されている列である場合
• 列の値が比較的一意である場合
• 参照整合性制約で使用されている外部キーになっている列である場合

索引（INDEX）を作成しない方が良いケース

• データ登録件数が少ない表である場合
• 列内の値がほぼユニークである場合
• NULL値が多く、NULL以外の値を検索しない場合

索引（INDEX）のメリット

• 表検索（SELCET）する際、特定の行を素早く検索する事が出来る為、検索のパフォーマンスが早くなる
• ソート作業を省略可能

索引（INDEX）のデメリット

• データの登録（INSERT）、変更（UPDATE）の際、索引変更のためのオーバーヘッドが加わるため、索引（INDEX）がないテーブルに比べると時間が掛かる
• 索引（INDEX）を作成する領域が必要

UNIQUE制約(ユニーク制約を設定する)

重複した値の格納を防ぐ

CREATE TABLE db_name.tbl_name
  (col_name data_type UNIQUE, ...)

CREATE TABLE db_name.tbl_name
  (col_name data_type, ..., UNIQUE [index_name] (col_name, ...))

UNIQUE 制約が設定されたカラムには重複した値を格納できなくなります。この点は PRIMARY KEY 制約と似ていますが、 UNIQUE 制約の場合は値として NULL を格納することができ、また複数のカラムに NULL を格納できます。また複数のカラムに対して UNIQUE 制約を設定することができます。

例：

create table staff(
  id int unique,
  name varchar(10)
  );

※注意

UNIQUE 制約が設定された id カラムには自動でインデックスが作成されており、Key カラムの値に UNI と設定されています。

(id カラムの値が NULL 以外の既存のデータの値と同じデータを追加されると)Duplicate entry '値' for key 'インデックス名' というエラーが発生しました。 id カラムには UNIQUE 制約が付いていますので、既存の値と同じ値を格納しようとするとエラーになります。

複数のカラムの組み合わせに対してUNIQUE制約を設定する

UNIQUE 制約の対象となるカラムは 1 つだけではなく 複数のカラムの組み合わせに対して設定することができます。例えば 2 つのカラムの組み合わせに対して設定した場合、 2 つのカラムに格納されている値の組み合わせが他のデータと重複することはできなくなります。

1つまたは複数のカラムを対象とした UNIQUE 制約を設定するには次の書式を使用します。

CREATE TABLE db_name.tbl_name(
  col_name1 data_type,
  col_name2 data_type,
  ...,
  UNIQUE [index_name] (col_name1, col_name2, ...)
  )

UNIQUE 制約を設定すると自動でインデックスが作成されます。インデックス名( index_name )は省略可能です。省略した場合は対象となるカラム名などから自動的にインデックス名が付けられます。

例：

create table staff(
  joiny int,
  id int,
  name varchar(10),
  unique joiny_id_index (joiny, id));

UNIQUE 制約が設定された joiny カラムと id カラムには自動でインデックスが作成されており、 joiny カラムの Key カラムの値に MUL と設定されています。今回は複数のカラムの組み合わせに対して UNIQUE 制約を設定していますが、最初のカラムに MUL が設定されるようです。

UNIQUE 制約がどのカラムに対して設定されているのかを確認するには SHOW CREATE TABLE 文を使うと確認できます。

AUTO_INCREMENTを設定する(連続した数値を自動でカラムに格納する)

カラムに AUTO_INCREMENT をつけると、データを追加した時にカラムに対して現在格納されている最大の数値に 1 を追加した数値を自動で格納できる。カラムに連続した数値を自動で格納したい場合に便利。

CREATE TABLE db_name.tbl_name
  (col_name data_type AUTO_INCREMENT, ... , INDEX (col_name))

※AUTO_INCREMENTの注意事項

• テーブルごとに 1 つのカラムにしか設定できない
• AUTO_INCREMENT が設定されたカラムにはインデックスが設定されている必要がある
• AUTO_INCREMENT が設定されたカラムには DEFAULT 制約は設定できない
• AUTO_INCREMENT が設定されたカラムに正の数値を格納した場合だけ正しく動作する

AUTO_INCREMENTが設定されたカラムに次に追加される値を確認する

AUTO_INCREMENT が設定されたカラムに自動で格納される値を確認するには INFORMATION_SCHEMA.TABLES テーブルから次のように参照することができます。(下記では使用しているデータベース名が mydb 、対象のテーブル名が staff の場合です)。

select AUTO_INCREMENT from information_schema.tables where table_schema='mydb' and table_name='staff';

テーブル作成時にAUTO_INCREMENTで自動で格納される値の初期値を設定する

AUTO_INCREMENT が設定されたカラムに設定される初期値は 1 から開始されますが任意の値から開始することもできます。初期値を設定する場合の書式は次の通りです。

CREATE TABLE db_name.tbl_name
  (col_name data_type AUTO_INCREMENT, ... , INDEX (col_name))
  AUTO_INCREMENT = value

例：

create table customer(
 id int auto_increment,
 name varchar(10),
 index(id))

 auto_increment = 20;

AUTO_INCREMENTで自動で格納される値をリセットする

作成済みのテーブルで AUTO_INCREMENT によって次に自動で格納される値を指定したり、テーブルそのものは削除せずにデータだけをすべて削除した場合に AUTO_INCREMENT で自動で格納される値をリセットして 1 から開始したい場合には ALTER TABLE 文を使って変更することができます。

ALTER TABLE tbl_name AUTO_INCREMENT = value

最後に・・・引用元全部書いていたら、引用多めの記事ってOK？？

現在駆け出しエンジニアとして、SQLについて学習しております。かなり引用が多いので、引用元に敬意を表すため引用元を全部書きました。

ただ、流石にそのままの引用多すぎ、とかダメな点があればご教示頂ければ幸いです。

参考

NOT NULL制約(カラムにNULLの格納を許可するかどうか)

DEFAULT制約(カラムにデフォルト値を設定する)

PRIMARY KEY制約(主キー/プライマリキーを設定する)

SQL：INDEXのメリットとデメリットについて

UNIQUE制約(ユニーク制約を設定する)

AUTO_INCREMENTを設定する(連続した数値を自動でカラムに格納する)