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

upsert処理

アップデートではまった件

今回は、updated_at、created_atのカラム部分ではまったところを書いておきます。

参考にしてください。

【はまった】

・updated_at のデフォルト制約

・時間について

・not null制約をつけるのか？

・カラムがない時は、insertして、カラムがあったら、updateしたい時のプロ・グラムをどうしたらいいのか？

➡INSERT ON DUPLICATE KEY UPDATE構文　かも。

・主キーとユニークキーがあると使えない

・アクティブレコードのreplace文の挙動について

➡updateしてない問題

・is_uniquでの判定

【結論】

時間カラムでは、not null 制約を外しておいた

・使わなくてよい

初期値に注意

DATETIMEの初期値？

既にデータが入っているカラムがある状態から、カラムを追加する場合は、not null制約に注意して、カラムを追加する事。

■解決策

'初期値の設定をnot null属性はつけず、デフォルト無しにしたら作れた

'datetimeでカラムはデフォルト値が not nullになっている場合がある。

VARCHARもカラムを作ると、自動でnot nullが入る

※カラムを作る場合は、型を決める。

型によってdefault値などの、初期値があるようなので、注意が必要。

nullの理解

nullの理解を深めておくと良かった。

NOT NULL制約とは何か？

SQLにおいて、NOT NULLがどのように扱われるのか？

nullは第三の真理値。普通のプログラムは、TorFのみ

■nullとは？

nullは長さがない、空文字は、長さがゼロで空

NULLとは>>

nullが入っていると、検索でエラーの温床になるので注意

NULLの検索について>>

新規登録+更新をどうするのか？

更新の基本

・UPSERT処理についてどうするか？決める

・データがある場合は、INSERT。ない場合はUPDATEをする処理の事。

INSERT ON DUPLICATE KEY UPDATE構文

REPLACE構文

調べると、この2つの手法に行きつく。

※INSERT ON DUPLICATE KEY UPDATE構文はmysql5.6~はupdateの時にインクリメント（+1）しないので、使いやすい。

【疑問】

通常、ユニーク制約があるテーブルへの更新処理をする場合と、ユニーク制約がないテーブル（カラム）への更新で違うのでは？？

1.SELECTして重複チェックを行った上でINSERTするかUPDATEするか分岐させる

2.INSERTしてみてDuplicateしたら例外をキャッチしてUPDATE処理に回すワイルド更新

の二択を迫られると思いますが、REPLACE句による更新処理も存在します。

■選択肢

1.セレクトして重複を探し、新規登録か？アップデートか？分岐をさせる。プログラムを書く。

2.insert on dupulicate を使って、一発で分岐処理をさせる。

3.REPLACE文を使ってデータを挿入する。

この辺りが考えられます。

重複しています。を表示させるには、phpでのプログラムを書く必要がある。

UPDATE

更新の基本について。

■何が出来るのか？

特定のカラムを更新するときに利用する。

値の変更をするときに使う

■mysqlの構文

UPDATE テーブル名 SET カラム名 = 値;

UPDATE mytbl SET price=1000 WHERE id=10;

whereをつけて、カラムを指定する事が大事。whereがないと全て更新してしまいます。

■いつ使うのか？

カラムが更新された更新日時をつけておく。

created_at、更新カラムを作っておき、更新されるたびに、記述をしておく。トランザクションテーブルに指定しておくと良い。

MYSQLではデフォルト値で、設定が出来る。

replace文を使うと、

■ユニーク制約が付いたカラム・フィールドの更新

Ｑ、主キー、構文でアップデートできないのか？？

REPLACE

■何ができるのか？

insertとdeleteをして、ﾘﾌﾟﾚｲｽしているように見せる構文。

■注意点

updateのように見えて、「削除」と「インサート」をしているだけです。更新してません。

※updated_atのカラムは、カラムが変更されたときに時間を記録しておけばよいのか？？

作った時のインサートは1回。更新、変更はは複数回ある。

MySQL には便利な拡張機能が用意されており、適切に使用すればコーディング量を減らすことができる。

■updateではない

REPLACE 構文は、プログラムからは INSERT と UPDATE を1文で実現するように見えるが違います。

実際には INSERT と DELETE を MySQL が組み合わせて実行。

REPLACE INTO table ( id, category_name, display_order )

VALUES ( 1, 'カテゴリ名', 10 )

具体的な動き

重複していた時と、していない時で挙動が違います。

【重複していた時】

1.既存の行をDELETE

2.あたらしい行をiNSERT

【重複しない】

1.新しい行をINSERTする

注意点

テーブルにプライマリーキーか、UNIQUE インデックスを用意しないとREPLACE 構文は利用できない

・AUTO_INCREMENT に注意

プライマリキーで AUTO_INCREMENT を利用すると、REPLACE 構文の実行後にプライマリーキーの値が変わってしまう可能性がある(キーが一致した場合、既存の行を DELETE で削除してしまうため)

しかしこれは、mysql5.6~だと大丈夫。

・未指定の場合は、デフォルト値が入る（insertと同じ）

・他のDBエンジンでエラー

REPLACE 構文は MySQL の拡張であり、他のデータベースエンジンで利用できない。

複数のデータベースエンジンに対応するアプリケーションを作成する場合は、違いを吸収する仕組みがいる。

【参照】

MySQL で INSERT と UPDATE を1文で実現する～REPLACE 構文編～

コードイグナイターでは

$this->db->replace()

$this->db->insert('users', $input_data_c);

$this->db->update()

リファレンス>>

$this->input->post()

使い分けについて

'新しいレコードを挿入する場合： $this->db->insert()
既存のレコードを更新する場合： $this->db->update()
既存のレコードを更新するか、またはなければ新しいレコードを挿入する場合： $this->db->replace()

$this->db->replace() メソッドを使用する場合、テーブルに？？主キーが必要。
$this->db->replace() メソッドを使用する場合、データベースのパフォーマンスに影響を与えることがあるため、必要な場合にのみ使用することが推奨されます。

Q、replaceにはなぜ、主キーが必要なのか？

replaceは、指定されたテーブルにレコードを挿入、レコードが存在する場合は更新をします。

挿入または更新する「レコードの値を指定」する事が必要です。

レコードを一意に識別するための主キーまたは一意のキーを指定する必要があります。

リファレンスを参照>>

INSERT ON DUPLICATE KEY UPDATE

■INSERT ON DUPLICATE KEY UPDATE

・コードイグナイターではない

※アクティブレコードでね

■メリット

・1行のSQLで、セレクト、インサート、更新の処理ができる。

・insert文とUPDATE文を個別に書かなくて良い

・データによってINSERT処理とUPDATE処理を切り分けたい時に便利。

■構文

「t1」テーブルのカラム「id」だけが、主キーであるとします。

INSERT INTO t1 (id,b,c) VALUES (1,2,3)

ON DUPLICATE KEY UPDATE c=c+1;

UPDATE t1 SET c=c+1 WHERE id=1;

同じ意味になる。

id	b	c
1	2	3

id	b	c
1	2	4

c=c+1なので、Cカラムは3+1=4になります。

■構文の説明

・idに1がなかったら（重複してない時）

INSERT INTO t1 (id,b,c) VALUES (1,2,3) を実行します。

・id=1があったら（重複している時）

cのカラムを、ｃ+1にする。

構文の参照>>

■注意点について

カラムｂがユニークの場合

UPDATE t1 SET c=c+1

WHERE a=1 OR b=2 LIMIT 1;

1つの行だけが更新されます。

つまり、変な動き、意図してない振る舞いをするので注意が必要です。

さらに詳しく>>

■動き

ユニークキー、プライマリキーによって、入力した値が重複する場合、古い行をアップデートしてくれます。

・insertはインクリメント（+1）を増やす

・updateは増やさない

複数行の更新はできません。

INSERT INTO テーブル名 (カラム1の名前, カラム2の名前)

VALUES (カラム1の値, カラム2の値)

ON DUPLICATE KEY UPDATE

カラム1の名前 = VALUES(カラム1の名前),

カラム2の名前 = VALUES(カラム2の名前)

※上記と書き方が違う＝複数の書き方があるのか？？

■3つの処理ができる

対象のレコードがあるかどうかをチェックする(SELECT文)

対象のレコードが無かったらデータを挿入する(INSERT文)

対象のレコードがあったらデータを更新する(UPDATE文)

INSERT INTO service_plans (customer_id, category, plan)

VALUES

(101, '髭脱毛コース', '無料プラン'),

(101, '背中脱毛コース', '無料プラン'),

(101, '指脱毛コース', 'Aプラン'),

(101, '膝下脱毛コース', 'Bプラン')

ON DUPLICATE KEY UPDATE plan = VALUES (plan);

この場合、planが既に重複してたら、新しくupdateされ

planがない場合は、新しく挿入します。

※planを基準にしている。重複チェックと、インサートを一気にやってくれるので便利！！

参照サイトはこちら>>

VALUES(col_name) 関数を使うことで、INSERT 構文中の値をUPDATEの値として使うことができます。？？？

VALUES（）

挿入するデータの値を指定するための関数です。VALUES()に指定された値は、INSERT文で指定されたカラムの順序に従って挿入されます。

INSERT INTO users (id, name, age)

VALUES (1, 'John', 25);

INSERT INTO users (id, name, age)

VALUES (1, 'John', 25), (2, 'Bob', 30);

2つ導入する事もできる。

setについて

UPDATE table_name SET column1 = value1, column2 = value2, column3 = value3

WHERE some_column = some_value;

SETはUPDATEの時に使う

VALUES()は更新の時に使う

■デメリット

UPDATEの時に意図せず AUTO_INCREMENT が進む（最近のバージョンのMySQLでは"ON DUPLICATE KEY UPDATE"してもINSERTされなければ自動連番は進みません。）

MySQL version 5.6～大丈夫です。

int で保存できる最大値に達する (カンスト)
新しくデータが保存できなくなるデメリットが存在します

複合主（一意）キーを持つテーブルに対して使うのは安全ではない

■メリット

1行のSQLで、セレクト、インサート、更新の処理ができる。

insert文とUPDATE文を個別に書かなくて良い

■使える条件

テーブルに主キーが設定されている事。

'レコードが無い場合→INSERT、ある場合→UPDATEする「INSERT ... ON DUPLICATE KEY UPDATE 構文」を試してみる

参照>>

DUPLICATE ＝重複する

【参照】

MySQLのINSERT ... ON DUPLICATE KEY UPDATEでレコードの挿入/更新を便利に実行>>

論理設計と時間カラム

■画面構成を考える

時間を入力する事と、時間を記録しておくカラムを用意する事。この2つの視点から、どんなプログラムを書くか？考える必要がある。

・新規登録画面　（変えてはいけない項目

・編集画面　（変化してよい項目を表示）

■編集について考える

update文は便利なのですが、変わってはいけないマスタデータを誤って編集してしまう危険性をはらんでいる。

つまり、間違いを最小限にする、画面設計を考える必要性がある。

■考えた事

会社idは会社nameは、管理者だけが触れるようにする。

塾idなども、勝手に追加されては困るので（お金にできなくなる）アドミンだけ、編集出来るようにする。

■管理画面

新規登録画面では、重複チェック+insert文だけで構成してもいいのかも？？

変更不可の部分は、

replace構文は使わないようにするのか？？

insertなので？？

created_at

アプリで制御しなくていいらしい。

DB側で持たせた方がいい。

■手法1

作成日時に「current_timestamp」
更新日時に「current_timestamp on update current_timestamp」

参照>>

■分かった事

上記のように設定しても、初回には、両方の時間が入る。

insertを利用すると。

※よく考えると当たり前。

CURRENT_TIMESTAMP

デフォルト値で設定するか、insert文で使うか？がある。

■いつ使うのか？

・レコードの作成日時や更新日時を記録する場合

データベース内のテーブルにレコードを挿入するときや、レコードを更新するときに、"CURRENT_TIMESTAMP"を使用して、その操作が実行された日付と時刻を自動的に取得することができます。

これにより、特定のレコードがいつ作成されたか、いつ最後に更新されたかを追跡することができる。

・タイムスタンプを使用する場合

アプリケーションにおいて、タイムスタンプを使用する場合に"CURRENT_TIMESTAMP"を使用することができます。例えば、Webアプリケーションのログファイルや、トランザクション履歴をタイムスタンプで記録する場合に使用されます。

・特定の日時を参照する場合

"CURRENT_TIMESTAMP"を使用することで、現在の日時を取得することができます。これにより、現在の日時に基づいて何らかの処理を行うことができます。たとえば、現在の日時を比較することで、期限切れのデータを自動的に削除するなどの処理が行われます。

■どうやって設定するのか？

カラムのデフォルト値として、設定する。

CREATE TABLE mytable (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
message VARCHAR(255)
);

isert文で使う

INSERT INTO mytable (message, created_at)

VALUES ('Hello', CURRENT_TIMESTAMP);

プログラムで組んで、時間を入れる。

■基本

・レコードを追加

・レコード更新した場合

自動でtimestampを追加してくれる。機能？？

CREATE TABLE t1 (

ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

DEFAULT CURRENT_TIMESTAMP と ON UPDATE CURRENT_TIMESTAMP の両方を使用した場合

カラムは、デフォルト値が現在のタイムスタンプになり、現在のタイムスタンプに自動的に更新されます。

➡つまり更新、追加の両方で記録されてしまうのか？？

■デフォルト

timestamp型には、デフォルトで「CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP」が付いてしまうようです。

注意点>>

on update CURRENT_TIMESTAMP

CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

update文をかけた時に自動更新されるような設定

MySQLのタイムスタンプ型

DEFAULT句に追加でon updateをつけることで、更新時にも日時を自動設定できる。

■CURRENT_TIMESTAMP

■挙動について

CURRENT_TIMESTAMPをつけると、勝手に時間をつけてしまうらしい。

■疑問

アップデートの時だけ、更新日時を表示させるには？どうしたらいいのか？？

php myadmin

on delete設定

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

on delete cascade

参照データも削除される

ON DELEE restrict

エラーだけ表示される

制限をするだけ

削除しない

ON DELEE set null

データがNULLになる

【結論】restrict

基本的には、restrictにしておくと良い。DELETEの制約はエラーだけでよい。

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

※どこが削除されているか、見て分からないので、思わぬエラーになる。

だったら、消すのではなく、エラーだけ表示させる方が安全。

よって、restrictにする。

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

UPDATEの時は？

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

ON UPDATE cascadeがよい。

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

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

ON UPDATE cascade

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

ON UPDATE restrict

エラーが表示される

アップデートしない

ON UPDATE set null

データがNULLになる

まとめ

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

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

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と同じ