外部キーと外部キー制約とは　まとめ

外部キー制約

外部キー制約とupsert処理

【結論】

親から子へ外部キー制約を貼るとエラーになる。

delete、cascadeは使わない方がいい

■なぜ外部キーが必要なのか？

upsert処理をするときに、制約を作っておくと、エラーとプログラムを抑制できる。

・制約を作ると、エラーが減る

・プログラムが減る

1.外部キー制約

■実務では

全てのテーブルにidをつけておく、ユニーク制約+AUTO INCREMENTを設定しておく。あとで、これが使えるので！！

※複合主キーは削除して、サロゲートキーを用意。

■外部キー制約とは？

親のテーブル、カラムに値が入っていないと、子のテーブル、カラムにデータを入力できなくする制約の事。

親のテーブルにある、「部署カラム」と子テーブルにある「部署カラム」で外部キー制約をつくります。

親テーブルのカラムは一意性が必要です。だいたい、主キーに貼る事が多い。

親子のカラムの名前が違っていても、論理的に合っていれば、利用ができます。

たぶん？？

本来なら、子テーブルに部署_idを作り、親にも部署_idを作って、外部キー制約を貼ります。（正規化する）

※親子同じカラム名前でやっていたが、どうやら違っていても良いようです。

・親、子テーブルで、制約対象となるカラムに「インデックス」が必要。

・親子のカラムの名前が違っていても大丈夫

・親のテーブルのカラムには、一意性が必要

■insert処理

FK制約を作った場合、insertする場合は、親テーブルから入力する事になる。

■update時

joinをして、必要なカラムに更新をかける。

すると、cascade制約があれば、一緒に子カラムも更新してくれる。

■使い方

子テーブルのカラムを選び、インデックスを貼る

■オプション

親テーブル側でデータの削除や更新を行った場合のふるまいについて設定ができる

【ON DELETE reference_option】　RESTRICT

【ON UPDATE reference_option】 CASCADEにする事。

■DELETE

RESTRICTは警告文が出るだけ。

どこが消えたのか？分からなくなるので、エラーだけ表示するようにしておくと良い。

■UPDATE

ON UPDATEはCASCADEに設定して、一緒にアップデートしてもらうと楽。

参照はこちら>>

サロゲートキーと_idというカラムの違い

※正規化している時点で、きちんと_idという名前がついているのか？？

「_id」という名前のカラムは、外部キー制約などで他のテーブルの主キーを参照するために使用されます。

一方、サロゲートキーは、人間による理解や認識には関係なく、データベースによって自動的に生成される一意の識別子です。

主キーとして使用され、複合キーの一部として使用される場合もあります。

つまり、_idカラムは外部キー制約の参照先の主キーを参照するために使われることが多いですが、サロゲートキーはデータベース内部での管理用途に使われることが多いです。

AUTO_INCREMENT 属性を使用すると、新しい行に一意の識別子を生成

参照はこちら>>

1a.UPSERT処理

1.既にカラムにレコードが入っているか調べる。

2.レコードがあれば、update処理

3.レコードがなければ、inser処理をします。

編集画面を作る場合、必ずと言っていいほど、登場する処理になる。

■レコードのの確認

$ci=$this->input->post('company_id',TRUE);

$this->db->where('company_id', $ci);
$this->db->from('company_name_t');
$count = $this->db->count_all_results();

こんな感じにクエリを投げて

if ($count > 0) {

//重複しているのでupdate
$this->db->where('company_id', $ci);
$this->db->update('company_name_t', $input_data_c);
}else {

//重複してないので、insert
$this->db->insert('company_name_t', $input_data_c);
}

こんな感じに作ってあげると、分岐ができる。

※もっといい方法があるはず。今のところは、こんな感じで。

カウントする

COUNT関数を利用する

select count(*) from (テーブル名) where (条件式)

$this->db->where('company_id', $ci);
$this->db->from('company_name_t');
$count = $this->db->count_all_results();

$this->db->count_all_results();

で整数を返してくれるので、カラムが入っていたか？のチェックができます。

■エラーが発生

これだと、主キーがvarcharの場合、カウントがとれない？

テーブルに値が入ってないと取れない？？

upsert処理に利用ができるので、便利です。

■insert

insert into company_t (company_id) values ($ci);

$data = array( 'company_id' => $ci );

$this->db->insert('company_t', $data);

company_id' => $ci の部分でカラムとデータを指定している。

複数行を一度に更新する

update_batch()

$this->db->set

挿入、更新するデータを準備してくれる。

$this->db->set

set($key, $value = '', $escape = TRUE)

「INSERT INTO」文、

「REPLACE INTO」文

「UPDATE」文で、追加、または、更新するデータの指定を行います。

$this->db->set('first_name', '一郎');
$this->db->set('family_name', '鈴木');
$this->db->insert('any_table');

INSERT INTO `any_table` (`first_name`, `family_name`) VALUES ('一郎', '鈴木')

を生成します。

$data = array(
'first_name' => '一郎',
'family_name' => '鈴木',
);
$this->db->set($data);
$this->db->update('any_table');

UPDATE `any_table` SET `first_name` = '一郎', `family_name` = '鈴木'

参照はこちら>>

■悩み

主キーをvarcharにしたら、phpmyadminで値しか表示されない。

主キーはvarcharだとダメなのか？？

経験上、varchar項目の右余白は見つけにくい不具合になることがあります。

参照>>

主キーの業務上の属性がなんなのかが問題なのでしょう。それを無視してcharかvarcharかの議論はちょっと難があるのでは。

しかし、主キーの型にvarcharを採用した場合、
ABCとABC_(_はスペース）が別レコードとしてDBに格納される。
つまり、スペース自体が意味をもつことになる。

テーブル設計、実務での気をつける事>>

リレーションシップ関係にあるデータの更新

■やりたい事

・複数テーブルにまたがってUPDATEを行う

■キーワード

・mysql 複数テーブル更新

■手法は2つ

外部キーで関連付けられた別のテーブルのデータを更新

MySQL 複数テーブルを JOIN してデータを更新 (UPDATE)>>

※複数テーブル更新クエリでは、条件を満たす各レコードが更新されます。

基準が複数回一致した場合でも、行は 1 回だけ更新されます。

複数のテーブルを更新する構文は、ORDER BY および LIMIT キーワードでは使用できません。

Q,2つの方法があるけれど、どうしたらいいのか？？

JOINとcascade+replaceのどちらを利用するのか？？

JOINを使用する方法

この方法では、外部キーで結合されたテーブルのデータをJOINし、更新するデータを含む新しい値を設定し、UPDATEクエリを実行します。

CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_price DECIMAL(10, 2)
);

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

ALTER TABLE orders ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(id)
ON DELETE CASCADE;

ここで、ordersテーブルのcustomer_id列がcustomersテーブルのid列と外部キーで結合されています。

ordersテーブルのある行のcustomer_idを更新するには、以下のようなSQLクエリを実行します。

UPDATE orders
JOIN customers ON orders.customer_id = customers.id
SET

orders.customer_id = 2,

customers.email = 'new_email@example.com'
WHERE orders.id = 1;

この例では、ordersテーブルのcustomer_idが2に変更され、customersテーブルの対応する行の「email」が更新されます。

外部キー制約のON UPDATE CASCADEを使用する方法

外部キー制約を設定して、ON UPDATE CASCADEオプションを使用することで、関連するテーブルのすべての行が自動的に更新されます。

例えば、以下のようなordersテーブルがあるとします。

CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_price DECIMAL(10, 2),
CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(id)
ON UPDATE CASCADE
ON DELETE CASCADE
);

ここで、customer_id列に外部キー制約が設定され、ON UPDATE CASCADEオプションが使用されています。

これにより、customersテーブルのid列が更新されると、ordersテーブルのcustomer_id列も自動的に更新されます。

ordersテーブルのある行のcustomer_idを更新するには、以下のようなSQLクエリを実行します。

UPDATE customers SET id = 2 WHERE id = 1;

この例では、customersテーブルのidが1の行がidが2に更新され、ordersテーブルのすべての行のcustomer_idが自動的に更新されます。

2.外部キーとは？

外部キーとは？

親テーブルと子テーブルの2つのテーブル間でデータの整合性を保つために設定される制約。

外部キー（FOREIGN KEY)）とは

「親テーブルに存在しないデータを子テーブルが持つことが無いようにするための制約」です。

データに整合性が出ます。

これを作ると、SQLでクエリを投げた時に、エラーが出るようになる。

子テーブルで制約を作る

・phpmyadminで外部キー制約を作る場合、必ず子テーブルで制約をつくる

構文は以下の通りです。

CREATE TABLE 子テーブル名 (子カラム名データ型,

INDEX インデックス名(子カラム名), FOREIGN KEY 外部キー名(子カラム名)

REFERENCES 親テーブル名(親カラム名))

phpmyadminで外部キー制約を作る場合が多いと思うので、必ず、「子テーブル」で制約を作るようにしてください。

※私は間違えて、沼にハマりました＾＾；

■注意転

MYSQLでは、インデックスを貼ってから（どちらに？？子？？親？？）利用する。

・phpmyadminで外部キー制約を作る場合、必ず子テーブルで制約をつくる

・インデックスも子カラムで➡外部キー制約を作る

・MySQLは外部キー制約を持つカラムに対し、自動でインデックスを用意する仕様になっている。

・インデックスがあるのでデータが増えた時に、早く検索できる

3.インデックスとは

【インデックスとは】

インデックスとは、その名の通り対象のレコードがどこに格納されているのか？

をデータベースに記憶させておくこと。効率的に検索ができるようになる。

■なぜ外部キーを利用するときにつけるのか？

データが増えた時に、早く動かすため。

外部キー制約がついているカラムの更新コストは、データ量が増えると負荷がかかる。

そのため、レコードを探すときのヒント（インデックス）がないと、パフォーマンス低下してしまうことが想定される。

この事例から外部キー制約をつけたカラムにはインデックスは張っておくと良いことが分かる。基本的には外部キー制約のついたカラムにはインデックスを作成することで性能向上が期待できそう。

外部キーのインデックスについて>>

インデックスの貼り方とは？

phpmyadminでは、子テーブルの構造で、インデックスをはり、制約をつくる。（親から子に作ると、プログラムを作った時に、変なエラーが連発するので注意）

単一カラムインデックスってのは、1つのカラムをキーとして索引を作り、検索効率化を図る目的

参照はこちら>>

■使い方

MySQL では CREATE INDEX 文および ALTER TABLE 文を使ってインデックスおよび UNIQUE インデックスを作成することができます

CREATE INDEX index_name

ON tbl_name (col_name, ...)

これでインデックスが作成できる。

index_nameは英数字であれば、どんな名前でもダイジョウブ。

複数のカラムが含まれても大丈夫？？

インデックスは、1つ以上の列に定義することができます。

B-treeおよびGiSTの実装でのみ、複数列インデックスをサポートしています。

参照はこちら>>

参照する側を「子テーブル」（トランザクションテーブル）

参照される側を「親テーブル」（マスタテーブル）

と呼びます。

【役割】

外部キーは、親テーブルに存在しない値の登録をエラーにする

外部キーは、子テーブルに値が存在するのに、親テーブルのレコードを削除しようとすると、エラーとなる。（子テーブルが参照できなくなるので）

外部キーの設定だけで、充分だよね。

4.外部キーを貼るには

■使い方

親テーブル

CREATE TABLE 親テーブル名(親カラム名データ型 PRIMARY KEY);

※参照先になる、親データ、テーブルが必要

子テーブル

CREATE TABLE 子テーブル名 (子カラム名データ型,

INDEX インデックス名(子カラム名), FOREIGN KEY 外部キー名(子カラム名)

REFERENCES 親テーブル名(親カラム名))

子テーブルでインデックスも外部キー名も、関連も作っています。

・INDEXが必要(MYSQL)

・子テーブルの方（FK）で、親テーブルの(PK)に対して、リファレンスを作るイメージですね！！

・インデックスは複数行でも構わない

参照はこち>>

REFERENCESで設定した場合は、UPDATEとCASCADEの初期値はどうなるのか？

REFERENCESで設定した場合、UPDATEとDELETEの初期値はともにRESTRICTになります。

つまり、親テーブルのレコードが削除または更新されると、子テーブルの参照制約によりエラーが発生。

必要に応じて、明示的にCASCADEを指定する必要があります。

つまり、削除される設定ではない。エラーのみが表示されるようになる。

5.php myadmin

外部機では、DELETEとUPDATEの2つに、オプションをつける事ができる。

ON DELETEの時は？

on deleteの後に続く単語（cascade , restrict , set null）により、親テーブルのレコードを削除しようとしたときの動作が異なります

on delete cascade

参照データも削除される

ON DELEE restrict

エラーだけ表示される

制限をするだけ

削除しない

ON DELEE set null

データがNULLになる

【結論】restrict

基本的には、restrictにしておくと良い。DELETEの制約はエラーを表示する(restrict)だけでよい。

cascadeにしてしまうと、後でとんでもない事になる。

※どこが削除されているか、見て分からないので、思わぬエラーになる。だったら、消すのではなく、エラーだけ表示させる方が安全。よって、restrictにする。

外部キー制約　on deleteについて>>

6.UPDATEの時は？

更新するときの制約です。

ON UPDATE cascadeがよい。

親（参照先）がアップデートされた場合、参照元の子データも一緒にアップデートされる。

親テーブルのフィールドの値変更を、参照元（子）に同期させる。

ON UPDATE cascade

参照データもアップデート

ON UPDATE restrict

エラーが表示される

アップデートしない

ON UPDATE set null

データがNULLになる

まとめ

消す場合は、エラーが表示されるだけにしておき、アップデートする場合は、子も一緒に変更になるようにしておく。これにより、整合性を保つ。

phpMyAdminでのテーブル作成と外部キー制約（FOREIGN KEY）設定＞＞

7.DEFAULT制約

カラムに対してつける事ができる制約。

■いつ使うのか？

INSERT文に値が指定されなかった場合や、UPDATE文で列が指定されなかった場合に適用。

例えば、テーブルに"作成日時"（created_at)カラムがある場合、INSERT文でこのカラムに値を指定することができなくても、デフォルト値を設定すれば自動的に現在時刻が挿入されます。

更新日時のカラム、updated_atも同じです。

デフォルト値の設定には、次のような場合に使うことができます。

・列にNULLを許容しない制約がある場合、その列に自動的に値を挿入するため
・列に共通の値を設定したい場合、手動で値を指定する手間を省くため
・アプリケーションが設定する値を指定する場合、SQLで指定することで設定の一貫性を保つため

デフォルト値は、CREATE TABLE文のカラム定義時にDEFAULTキーワードを使用して設定します。

■使い方

CREATE TABLE mytable (

id INT PRIMARY KEY,

name VARCHAR(50) DEFAULT 'unknown',

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

・定数を設定する事もできる

・関数を入れても良い

ex. (rand() + 3.2),

■いつ使うのか？

特定のカラムだけに値を指定した場合、値が指定されなかったカラムにデフォルト値があれば、データを追加する時にデフォルト値が使用される。

■よく利用するもの

時間関数という

NOW()

YYYY-MM-DD HH:MM:SS

2023-02-07 11:22:33

CURRENT_DATE()

YYYY-MM-DD

2023-02-09

CURRENT_TIMESTAMP()

YYYY-MM-DD HH:MM:SS

2023-02-09 15:30:00

MYSQLの型で調べるようにしてください。

※updated_atなどに着けられるのか？？➡YES

自動でつけてくれるので、かなり楽になるはず。他のカラムに値を更新したとき、updated_atの時間が自動で更新してくれたら楽だよね。

こちらを参照>>

INSERTの時は注意かも。新規登録した時は、固定化しておきたい。

データを保存しておきたいので。

■注意点

MySQL 5.6.5以前だと、DATETIME型のカラムにCURRENT_TIMESTAMPを設定できません。 CURRENT_TIMESTAMPを設定したTIMESTAMP型はテーブル内に1つだけしか作成できません。

詳細はこちら>>

【ちょっとブレイク】

※基本+挙動+実務の順番で調べてみるとよい。

・基本はこうだが、実務では使わない。って事がシステムづくりでは多い。

■注意点

TIMESTAMP 型はデフォルトで NOT NULL が設定されている。（クエリとデータがある状態で、カラムを作った時にエラーになった原因）

■実務では

name列にデフォルト値として'unknown'を入れる

質問

■後からつける事もできる？

テーブルを作り、後から外部キー制約を付ける事はできる。

phpMyAdminでのテーブル作成と外部キー制約（FOREIGN KEY）設定

phpmyadminで設定する>>

■後から消す方法

テーブルを選ぶ➡構造>関連ビューに移動し、そこで、phpmyAdminで関係を削除する外部キーに対応する行の「ドロップ」ボタンをクリックします。

A.後付け、後消しも大丈夫です！！

外部キー制約とは

【なぜ外部キー制約を使うのか？】

制約を作るっておく事で、SQLエラーが発生するようにしています。

これにより、整合性のエラーを防ぐ目的です。

【メリット】

1.親テーブルに存在しない値を、子テーブルに入力できないようにする
2. 親テーブルに存在するデータをむやみに、消せなくなる。
3. 親テーブルのレコードを削除したときに外部キーで紐づいている子テーブルのレコードも削除できるようになる

【デメリット】

deleteの制約は使わない事

on delete cascadeが不用意に定義されてしまうと、

【現場の声】

初回設計開発時は外部キーついてても良いのですが、追加開発時の外部キー制約忘れで結局意味なくなったり、その後のリファクタやサービス分割において外部キー制約があることにより開発難度が上がってしまう場合が多く、個人的には基本は外部キーつけずにアプリケーション側で担保していくのが良いと思っています。

参照>>

制約について

【使う理由】

'親テーブルに無いデータを子テーブルに追加すると、親テーブルと子テーブルの整合性が崩れてしまいます

【役割】

1. 存在しない値を外部キーに設定できなくなる
2. 親テーブルに存在する子テーブルの外部キーで繋がれたデータを削除できなくなる
3. 親テーブルのレコードを削除したときに外部キーで紐づいている子テーブルのレコードも削除する

外部キー制約	update	delete
RESTRICT	参照先の変更に追従	エラー
CASCADE	参照先の変更に追従する	参照先が無くなると同時に削除される
SET NULL	NULLに置き換わる	NULLに置き換わる
NO ACTION	RESTRICTと同じ	RESTRICTと同じ