FileMaker って、、、あれかなぁ

2012-12-20

簡単？　FileMakerで在庫管理（３） ―― 倉庫など場所別に在庫数を把握する

　弊社では在庫管理システムの講習を行っていますが、時折「うちは在庫の保管場所が複数あるので、保管場所毎に在庫を管理したいのだが、おたくの講習は対応できる？」との問い合わせを受けることがあります。　

　実のところ、複数の在庫場所に対応した在庫管理システムの構築は難度が上がってしまうのですが、なんとかコアの部分だけでも本記事でご説明させていただきます。

倉庫など場所別に在庫数を把握する

　ここでは、『FMEasy在庫 R1.0』で複数の在庫場所を管理できるように、仕様変更してみることにします。
　これまでに弊社の在庫管理講習を受講された方、または『FMEasy在庫R1.0（開発版）』ユーザ、倉庫別在庫管理の考え方を学びたい方の参考になれば幸いです。

テーブル設計

在庫場所テーブル（新設、倉庫/保管場所等管理用マスタ）

ID（主キー）
場所名（テキスト）

場所別在庫TB（新設）

ID（主キー）
場所ID（外部キー）
商品ID（外部キー）
導入時在庫数（数値、導入時に元々あった商品在庫数を在庫場所別に記録）
繰越在庫数（数値、繰越処理実行時に特定時点の商品在庫数を在庫場所別に記録）
c場所別在庫数（場所別在庫算出用計算フィールド）
g在庫基準日1/g在庫基準日2（入出庫の期間特定用グローバルフィールド）

入庫テーブル/出庫テーブル（変更）

入庫場所ID/出庫場所ID（新設、入出庫場所を登録）

入出明細TB（変更）

在庫場所ID（新設、入庫テーブル/出庫テーブルの入庫場所ID/出庫場所IDをコピーする）

リレーションシップ設計

レイアウト(LAY)/スクリプト設計

在庫場所LAY（新設、場所別の在庫数をポータルに表示）
入庫LAY/出庫LAY（変更）

入庫場所ID/出庫場所IDを配置する
OnRecordCommitで入庫場所ID/出庫場所IDを入出明細テーブルの在庫場所IDフィールドにコピー
OnRecordCommit時、必要に応じて場所別在庫レコードを作成(4-a参照)

考慮すべき事項

場所別在庫レコードを作成するタイミング

倉庫間移動に対応

出庫時、倉庫1の出庫伝票を、入庫時、倉庫2の入庫伝票を作成
倉庫2の入庫伝票作成漏れを防ぐ
倉庫2の入庫伝票作成後の関連伝票更新をどう考えるか？

出入庫同時作成機能（商品の近接場所間移動時）
その他

リレーションシップ

　まず、テーブルでキモとなるのが場所別在庫テーブルですね。このテーブルにより、場所別に商品在庫数の算出が可能となります。
　ここで特に重要なフィールドは、在庫場所ID、商品ID、c場所別在庫数(計算フィールド)です。なお、下図で場所名と商品名は商品テーブルと在庫場所テーブルを参照しています。

【場所別在庫テーブルを表形式レイアウトに表示】

在庫算出部分のリレーション。

↓　

　入出明細テーブルには商品ID/在庫場所ID(出庫元/入庫先の在庫場所ID)と入出庫数が登録されているわけだから、入出明細テーブルと場所別商品在庫テーブル上記のようにリレートして、以下のように計算式フィールをを作ればよいでしょう。

元々ある在庫＋特定期間のSum(入庫数)－特定期間のSum(出庫数)

注：

　「元々ある在庫」は繰越処理が未実行であれば［導入時在庫数］、実行済であれば［繰越在庫数］となり、「特定期間」はユーザが指定する［g在庫基準日1］と、［g在庫基準日1］のOnObjectValidate/Saveで起動するスクリプトにより制御された［g在庫基準日2］の値により決まります。

　［g在庫基準日2］の設定値については、「簡単？ FileMakerで在庫管理（2）」の図が参考になると思います。

　他にもリレーションシップの追加はあるのですが、難しくないので省略します。

レイアウトとスクリプト

　入庫/出庫レイアウトには、それぞれのテーブルで新設した［入庫場所ID］と［出庫場所ID］（入出庫した場所を示すID）を登録し、OnRecordCommit時に入出庫明細テーブルの［在庫場所ID］にコピーします。

　なお、Commit時に、入出明細テーブルに入力されている［商品ＩＤ］で場所別在庫テーブルに未登録の商品は登録するという、第2のキモともいうべきスクリプトについては、できれば機会を改めて書きたいと思います。

【出庫レイアウト】

　最後に在庫場所別に商品在庫数を表示するレイアウト。ユーザが［在庫基準日］に日付入力することにより、その日付時点の各商品の在庫数が表示されます。

【在庫場所レイアウト】

ポータル部分に在庫数(=場所別在庫::c場所別在庫数)と商品テーブルの情報が表示されます。

以上、『FMEasy在庫』というテンプレートをベースにカスタマイズしてみましたが、半日強の時間でとりあえず場所別在庫数は表示できるようになりました。
　上述の場所別在庫のレコード作成処理や、繰越処理は別途作成しなければなりませんが、意外と実装に時間はかかりませんでした。

　その他の在庫関連記事を読む

（土屋）

2012-11-08

SQL Server 2000 を FileMaker 11で使う

　FileMakerが公式サポートしているMicrosoft SQL Server のバージョンはMS SQL Server 2008 R2/MS SQL Server 2008 SP2/MS SQL Server 2005 SP3のみです。

　が、10年程前に SQL Server 2000 と ASP で作成したシステムのデータをちょこちょこ弄る必要がでてきました。
　これを Active Server Page でやるのは大変すぎるので、FileMaker Pro 11でSQL-Server 2000のDBを扱えないものかと無謀にも思い立ち、試してみました。

　本記事は、その備忘録となります。

　例によって「システムDSN」を登録。
　環境は Windows 32bit、ODBCドライバは「SQL Server 6.00」。
　問題なく登録できる。

　次に FileMaker Pro 11 Advanced を起動してFileMakerデータベースを作成。

　上記で登録したDSNをFileMakerの外部データソースとして登録する。

　さらに、リレーションシップのウインドウで、上記で登録した SQL Server データベースのテーブルをTO（Table Occurrence）として現出させ、リレーションとかもしてみる。問題なし。

　FileMaker では TO 毎にレイアウト（フォーム）が自動作成される。ブラウズモードにして、表示方法を「表形式」を選択すると、以下のような表が表示される。

　ここまで、慣れていれば5分程度で作成できる。

　いまのところ、照会/検索は普通にできている。

　最後に躓いた点のメモ：

　上記のFileMakerデータベースを Windows Server 2008 x64 の FileMaker Server で運用する場合、普通にDSN登録をすると、上記のリレーションシップのところでエラーが起こると思われる。

　この場合、SysWoW64フォルダの odbcad32.exeを起動して、DSN登録を行うこと。

参考リンク

http://social.msdn.microsoft.com/Forums/ja/sqlserverja/thread/7bec5ba8-3164-402f-8050-1ce4a0acaa8e

（土屋）

【IWP関連記事】

【弊社のIWP関連製品・サービス】

2012-07-18

QNAP障害発生時のマイグレーション

今回発生したQNAPの障害を機に、ハードディスクではなく、QNAP本体で障害が発生した場合のマイグレーション（Migration）をシミュレートしてみた。　ここでいうマイグレーションとは、障害が発生した元QNAPのハードディスクを新QNAPに移動し以後QNAP2で運用することを言う。
以下、今回行ったシミュレーションの手順・注意点を記録しておく。

1.障害はハードディスクかQNAP本体かを見分ける

ハードディスクの障害であればマイグレートしてもしかたがない、というか余計症状が悪化する可能性もある。ハードディスク障害の症状、チェック方法、復旧方法については、以下を参照。

QNAPで恐怖のエラーがぁぁぁぁああああ

ターミナルエミュレータを使って QNAP の状態を確認する

本体のHDDのLEDが赤く点滅していればHDD障害の可能性大。LEDが緑（点滅）であっても、PCからアクセスできない、ブラウザからQNAP管理画面の左ペインの（いくつかの）リンクにアクセスしようとすると「Loading Driver Please Wait... 」と表示されたままになりアクセス不能、RAID情報を見ると理由もないのに「Sychronizing...」していてHDDのLEDが緑点滅している、という状況があればHDDが疑わしい。

今回のようにHDDが中途半端に生きている場合、「Sychronizing...」（十数時間）→アクセス障害→手動再起動→アクセス復旧→「Sychronizing...」（十数時間）→アクセス障害→手動再起動…　をHDDが完全に死んでLEDが赤点灯するまで何十時間も繰り返すことがあるので、障害原因がハードディスクかQNAP本体かをいかに早く見極めるかが、腕の見せ所。

3.事前確認
今回のシミュレーションでは、「元QNAP（いままで運用中のQNAP）本体が障害を起こしたと仮定し、元QNAPの4台のHDDを新しいQNAPに移し（マイグレーション）、ネットワーク上のマシンから問題なくアクセスできることを確認、その後4つのHDDを元QNAPに戻して運用を再開する」という一連の作業を行う。　かなり不安な作業なので、QNAPサポートにこれを実行して問題ないかを問い合わせたところ、「問題ない筈だが、マイグレーションに関する以下のURLを読んでみろ」とのこと。

http://docs.qnap.com/nas/en/index.html?system_migration.htm

曰く、「After the migration has finished, all the settings and data will be kept and applied to the new NAS. However, the system settings of the source NAS cannot be imported to the destination NAS via “System Administration” > “Backup/Restore Settings”. Configure the NAS again if the settings were lost.」

つまり、元QNAPの設定とデータは新しいQNAPに継承される。が、“System Administration” > “Backup/Restore Settings”を利用しても、元の設定を取り込めないこともあるので、その時は手動で設定してね、と怖いことが書いてある。　元QNAPで行った設定はすべてスクリーンショットを取っておき、万が一の場合は手動設定する準備をしておく必要がある。

4.元QNAPのファームウェアのアップグレード
実際にQNAP本体に障害発生した場合、ファームのアップグレードはできないが、今回はシミュレーションなので、マイグレーション前に元QNAP機のファームをアップグレードした。　

まず、アップグレード前にファームの更新履歴をみて、アップグレードの可否の判断を行う。　QNAPファームは頻繁にアップグレードされバグも多いようなので、この判断は慎重に行うこと。　今回は3.5.0→3.7.1へとアップグレードを行った。

アップグレードに要した時間は10分程度だったか。終了後は再起動を求めてきたので再起動。　ところが、再起動後にまた「Synchironizing...」が始まった。　これに要した時間が10時間強。QNAPのサポートにこの件を問い合わせたが、「通常、Synchironizeはそうそう起こるものではない。今回の原因はわからない」とのことだった。

5.マイグレーション、そして再度元のQNAPへ戻す

10時間強のSynchronizeが終わったところで、元QNAPの電源を落とし4台のHDDを抜き取り、新QNAPに移して電源をいれた。　数分で問題なく起動してきた。これでマイグレーション作業は終了。

ところがログをチェックしてみると、「The firmware versions of the system built-in flash (3.4.2 Build 0331T) and the hard drive (3.4.2 Build 20120615) are not consistent. It is recommended to update the firmware again for higher system stability.」と出ている。ファームウェアは、ハードディスクと本体フラッシュメモリの両方にあるらしい。マイグレーション先の新QNAPのフラッシュ内のファームが古いというのは当然だが、元QNAP内にあった4台のハードディスクのファームが3.4.2というのはおかしい。

一方、“Firmware Update”のリンクをクリックすると、「Current firmware version: 3.7.1 Build 20120615」と表示される。　よってthe hard drive (3.4.2 Build 20120615)　の表記はバグと思われる。

今回はシミュレーションなので新QNAPでのファームのアップグレードは見送り、この状態でブラウザのQNAP管理画面からチェックをおこなった。IPアドレス、その他は旧QNAPの設定がそのまま継承され、マシン名も旧QNAPと同様である。その他の設定も旧設定をそのまま継承している。ここで、旧QNAPを使用していた仮想/物理マシンを起動してみたが、問題なくアクセスできた。

一通りチェックが終わったところで、新QNAPの電源を落とし、4つのハードディスクを新QNAPから取り出し、元QNAPに戻した。　されに元QNAPの電源を入れてログをチェック。　問題がないことを確認後、各マシンから再びアクセスをさせたところ、問題なく動作した。

（土屋）

Amazon.co.jp ウィジェット

2012-07-12

ターミナルエミュレータを使って QNAP の状態を確認する

先日の QNAP の不具合でわかったことですが、QNAP 本体にトラブルが発生すると、Web ブラウザによる管理ツールで使えない部分が出てくることがあります。

たとえば、RAID 構成を表示させるページや、ISCSI の構成を表示させるページ等は読み込み中のまま、まともに動かなくなってしまったりします。

そういった場合に、エラーの発生したディスクカートリッジを交換したり修理に出したりする前に、直接 QNAP の OS にアクセスして状態の確認やログ参照を行うことにより、原因を特定しやすくなるかもしれません。

ここでは、PuTTY というターミナルエミュレータを使うことによって、QNAP にログインし、基本的なステータス確認コマンドを走らせる方法について説明します。

1. PuTTY のダウンロード

以下のページにアクセスし、適切なバージョンの PuTTY をダウンロードします。

PuTTY Download Page

PuTTY は実行ファイル単体で動作しますので、Windows ユーザの方は putty.exe をダウンロードすればまず問題ないでしょう。

2. PuTTY の起動

putty.exe を起動すると、PuTTY Configuration という画面が表示されます。

上図の青囲みのように Host Name に QNAP の IP アドレス、ポートに 22 (SSH) を指定してから、“Open”ボタンをクリックします。

3. QNAP へのログイン

接続に成功すると、以下のようなPuTTY のターミナル画面が開きます。

ログインを求められますので、admin を指定し、正しいパスワードを入力して Enter キーを押します。

4. mdstat でメディアの状態をチェック

以下のコマンド入力して Enter キーを押すと、現在のメディアの状態を確認できます。

cat /proc/mdstat

上記の黄色囲みに注目してください。

UUU_ と文字が並んでいますが、4 文字目がアンダースコア _ になってしまっています。

これは、4 番目のメディアに何らかの障害が発生しているため、mdstat では読み取り不能であることを示しています。

つまり、QNAP の 4 番目のディスクカートリッジに障害が起こっていることがこれでわかります。

6. klogd.sh dump でさらに状態を詳しくチェック

以下のコマンド入力して Enter キーを押すと、カーネルログデーモンで発生しているイベントのログを照会できます。

/etc/init.d/klogd.sh dump

しかし、これではすべてのログが表示されてしまいますので、後尾に grep を付けてキーワードの絞り込みをすると場所の特定が容易になるでしょう。

たとえば、入出力のエラーを確認したいときは以下のように入力します。

/etc/init.d/klogd.sh dump | grep "I/O error"

I/O error ログのみが抽出されます。

上記の黄色囲みの部分を確認すると、sdd デバイスに入出力エラーが発生していることがわかりますね。

【補足】

メディア名をコマンドでチェックするには、以下のコマンドを入力します。

ls /dev/sdd*

sdd、sdd1、sdd2、sdd3、sdd4 とメディア名が定義されており、最初から順に QNAP のカートリッジと対応しています。上記 4. の mdstat で説明したとおり、今回は4 番目のデバイス sdd3 (QNAP 本体では物理的に 4 番目のカートリッジ)に障害が起こっています。

また、sdd4 は物理的には存在していないデバイスとして定義されているようですが、この部分は当方は未確認です。

その他、QNAP の不具合に関して具体的なサポートを受けたい方は、以下にお問い合わせください。

QNAP HELP DESK　（問い合わせは英語）

http://helpdesk.qnap.com

関連記事：
QNAPで恐怖のエラーがぁぁぁぁああああ

2012-07-11

QNAPで恐怖のエラーがぁぁぁぁああああ

知人のQNAPはiSCSIを構成しており、Hyper-Vの仮想マシンと物理マシンがそのiSCSIボリュームを使用している。　ある朝突然、それらのiSCSIボリュームにアクセスできなくなったと言う。「Hyper-Vマネージャー」を起動してみてみると、「仮想マシン」の一覧に表示されるマシンのいくつかに「一時停止 - 重大」と表示されている。　これらのマシンはみなiSCSIボリュームを使用しているものだ。
ブラウザでQNAPのシステムログを見ようとしても「Loading data. Please wait...」と出てくきて、先へ進めない。　仕方がないのでブラウザからQNAPを再起動しようと試みるもいつまでたってもシャットダウンしない。　またまた仕方がないので、QNAP本体の“POWER”ボタンを長押しして無理やり電源を落とす。次に再度“POWER”ボタンを押して起動を試みるも、

「SYSTEM BOOTING」

が本体ディスプレーに表示されたまま、10分ほど経っても起動してこない。「qnap system booting」や「qnap system booting forever」でググってみると、「ハードの故障だ」とか「PSU（Power Supply Unit）だ」とか面倒そうなことが一杯書いてあり、気分が悪くなってくる。　さらに仕方がないので、“POWER”ボタンを2回押して再起動。やはり、「SYSTEM BOOTING」が出てきて、起動してきそうもない。　上記の記事を丹念に読み始める。　どうみても簡単に直りそうもない、と絶望的な気分に陥りそうになったところで、チラっと本体のディスプレーを見ると、なんと！「SYSTEM STARTING...」（だっけな？）みたいなのが表示されており、狂喜する。　しばらくすると、ディスプレーからメッセージが消え、PINGも返るようになる、ブラウザからもアクセスでき、最後にiSCSIボリュームも復活して、Hyper-VのクライアントOSからもそれらを利用できるようになった。

それでログを見てみると、「[RAID6 Disk Volume: Drive 1 2 3 4] The file system is not clean. It is suggested that you run "check disk".」と表示されている。さらに「RAID MANAGEMENT」をクリックしてみると、「Synchronizing (0%)」と表示されている。　ところがこのSynchronizingが遅々として進まず。15時間が経過した午前2時過ぎに97%と表示され、この時点でiSCSIボリュームが再度使用不能になっていることに気づく。　この期に及んで、いくら待ってもSynchronizingは成功しそうにないことにやっと気づくが、とりあえず、朝までSynchronizeを走らせることにして就寝。　翌朝見てみるとやはりSynchronizingは97％のままで終わっていない。　24時間が経過しようとしてもまだ終わらない。

諦め気分でふとQNAP本体を見ると、4つの目のHDDのLEDが緑点滅から赤点灯に変わっている。
「RAID Management」の「Current Disk Volume Configuration」は、"RAID 6 Disk Volume: Drive 1 2 3"を示し、第4ドライブが表示されていない。24時間かかって、「やっぱりハードディスクか」とかなり確信を持つ。念のため、今回の現象をQNAPのサポートにメールしてみると、「問題のハードディスクを取り外して、もう一度入れてみろ」というので、その通りにする。すると、本体のSTATUSが赤く点滅し、第4ドライブのLEDは緑に点灯。　以下のようなログが表示された。

2012-07-10	16:13:11	System	127.0.0.1	localhost	[RAID6 Disk Volume: Drive 1 2 3 4] RAID device in degraded mode.
2012-07-10	16:13:09	System	127.0.0.1	localhost	[RAID6 Disk Volume: Drive 1 2 3 4] Drive 4 removed.
2012-07-10	16:12:59	System	127.0.0.1	localhost	Drive 4 plugged out.

Drive 1 2 3 のSynchronizing　が無事終了するまでにさらに10時間程が経過。以下のような状態になった。

Current Disk Volume Configuration : Physical Disks

Disk	Model	Capacity	Status	Bad Blocks Scan	SMART Information
Drive 1	Hitachi HDS722020ALA330 JKAO	1863.02 GB	Ready	SCAN NOW	GOOD
Drive 2	Hitachi HDS722020ALA330 JKAO	1863.02 GB	Ready	SCAN NOW	GOOD
Drive 3	Hitachi HDS722020ALA330 JKAO	1863.02 GB	Ready	SCAN NOW	GOOD
Drive 4	Hitachi HDS722020ALA330 JKAO	1863.02 GB	Disk Read/Write Error	SCAN NOW	GOOD

Note that if you are going to install a hard drive (new or used) which has never been installed on the NAS before, the hard drive will be formatted and partitioned automatically and all the disk data will be cleared.

Current Disk Volume Configuration : Logical Volumes

Disk/ Volume	File System	Total Size	Free Size	Status
RAID 6 Disk Volume: Drive 1 2 3	EXT4	3664.62 GB	3023.18 GB	In degraded mode
FORMAT NOW CHECK NOW REMOVE NOW

かくしてディスク交換の止む無きに至り、Amazonから Segate ST2000VX000 を注文、待つこと2日でブツが到着。　いそいそとDirve 4を抜く。

Drive4を抜いた状態

調達した新ドライブをいれると、Rebuildが始まった。

15時間後、Rebuild無事終了。

【メモ】

ディスクが中途半端に壊れると、その壊れかかったディスクを使用して、Synchronizeを行おうとする。　このSynchronizeはQNAPが完全にそのディスクが壊れていると認識できるまで、続行する。　問題は、この Synchronizeの過程で、iSCSIほかのサービスが異常中断し、ブラウザのQNAP管理画面からも、アクセスできない機能があること。
今回は原因特定に時間がかかり過ぎた。次回、HDDの障害が疑われる場合（の一案）→　cat /proc/mdstat　を実行して障害HDDを特定→QNAPの電源を落とし障害HDDを抜き再起動→Synchronizingが実行される（十数時間）→新HDDを入れてRebuildする。
SSHによるRAID状態チェック→こちら
QNAPが完全にそのディスクが壊れていると認識した時点でSynchronizeは中断し、当該HDDのLEDには赤が点灯とする。このディスクが少ない状態（今回は4→3に減った状態）で、再度Synchronizeが実行される。
HDD交換時は→ QNAP互換HDDリスト
コンパクトで省エネ、比較的お手軽に導入できるQNAPのiSCSI。　が、一旦障害が発生すると復旧はかなり面倒になることも。　ググると、障害対応で苦労しているユーザが一杯いる。ただ、ここ（本社）のサポートはかなり秀逸だったりする。　 Danny、謝謝。

土屋

2012-07-06

MySQL Workbench の EER モデリングツールでリレーションシップ検証 3 （リレーションシップアイコンの種類と使い方）

前回、前々回の記事で、外部キーの設定と制約の定義方法について説明してきましたが、今回は EER モデリングツールのリレーションシップアイコンの種類と使い方について説明します。

EER モデリングでリレーションシップの設定を行う場合は、画面の左下に並ぶ 6 種類のアイコンを使います。

既存のテーブル列を使ってリレーションシップを設定する

大抵の場合は、先に基本的なテーブルを予め定義しておき、後でリレーションシップを設定する作業手順となるでしょう。そのような場合は、上記赤囲みの一番下のアイコンを使用します。

このアイコンを使って作成されたリレーションは、たとえば次のようになります。

青囲みの列に注目してください。ブランドテーブルの [ブランドID]、および取扱商品テーブルの [ブランドID] は共に既存の列にが使われています。

外部キー専用の列を子テーブルに新規追加し、1対1 の非依存型（non identifying）リレーションシップを設定する

子テーブルに外部キー用の列が作成されていない場合に、親テーブルと 1対1 の非依存型のリレーションシップを設定する場合は、一番上のアイコンをクリックします。

非依存型（non identifying）リレーションシップについては、こちらの記事をご覧ください。

このアイコンを使って作成されたリレーションは、たとえば次のようになります。

青囲みの列に注目してください。ブランドテーブルの [ブランドID]はそのままですが、取扱商品テーブルには、 [ブランド_ブランドID] という専用の列が新たに追加されています。

また、ブランドテーブル側、および取扱商品テーブル側の支線は枝分かれしていないため、このリレーションシップが 1対1 の関係となっていることがわかります。

非依存型リレーションシップの場合、コネクタは破線で表現されます。

外部キー専用の列を子テーブルに新規追加し、1対多の非依存型（non identifying）リレーションシップを設定する

子テーブルに外部キー用の列が作成されていない場合に、親テーブルと 1対多の非依存型リレーションシップを設定する場合は、上から二番目のアイコンをクリックします。

非依存型（non identifying）リレーションシップについては、こちらの記事をご覧ください。

このアイコンを使って作成されたリレーションは、たとえば次のようになります。

また、ブランドテーブル側の支線が枝分かれしていないのに対し、取扱商品テーブル側の支線が三つに枝分かれしているため、ブランド 1 に対し、取扱商品が多の関係になっていることがわかります。

非依存型リレーションシップの場合、コネクタは破線で表現されます。

外部キー専用の列を子テーブルに新規追加し、1対1 の依存型（identifying）リレーションシップを設定する

子テーブルに外部キー用の列が作成されていない場合に、親テーブルと 1対1 の依存型のリレーションシップを設定する場合は、上から三番目のアイコンをクリックします。

依存型（identifying）リレーションシップについては、こちらの記事をご覧ください。

このアイコンを使って作成されたリレーションは、たとえば次のようになります。

依存型リレーションシップの場合、コネクタは実線で表現されます。

外部キー専用の列を子テーブルに新規追加し、1対多の依存型（identifying）リレーションシップを設定する

子テーブルに外部キー用の列が作成されていない場合に、親テーブルと 1対多の依存型リレーションシップを設定する場合は、上から四番目のアイコンをクリックします。

依存型（identifying）リレーションシップについては、こちらの記事をご覧ください。

このアイコンを使って作成されたリレーションは、たとえば次のようになります。

依存型リレーションシップの場合、コネクタは実線で表現されます。

多対多のリレーションシップを設定する

子テーブルに外部キー用の列が作成されていない場合に、親テーブルと多対多のリレーションシップを設定する場合は、下から二番目のアイコンをクリックします。

このアイコンを使って作成されたリレーションは、たとえば次のようになります。

多対多の場合は、子テーブルの外部キーと親テーブルの主キーを結び付けると、テーブル名_has_テーブル名 という名前のテーブルが新たに作成されます。
このテーブルは、子テーブルに対する外部キーおよび親テーブルに対する外部キーをそれぞれ持っている点に注目してください。

上図の場合では、ブランド_has_取扱商品 というテーブルに、取扱商品テーブルの主キーに対する外部キー、およびブランドテーブルの主キーに対する外部キーが作成されることを示しています。

外部キーを確認すると、以下のようになっています。

○ブランドテーブルに対する外部キー

ブランドテーブルの主キー[ブランドID]に対する外部キーが作成されています。

○取扱商品テーブルに対する外部キー

取扱商品テーブルの主キー[商品ID]に対する外部キーが作成されています。

【関連リンク】
MySQL Workbench 基礎講習コース

2012-07-05

MySQL Workbench の EER モデリングツールでリレーションシップ検証 2 （外部キー制約の種類と動作）

前回の記事では、リレーションシップ構築時の idendifying （依存型）および non identifying (非依存型) の考え方と設計方法について述べましたが、今回は外部キーで設定可能な制約の種類と動作について説明します。

【外部キー制約とは】

子テーブル側の外部キーと、親テーブルの主キーの参照整合性を維持するために、子テーブル側の外部キーの動作を制限するものです。

たとえば、前回のブランドと取扱商品との関係性では、取扱商品テーブル側に不明な外部キー（[ブランドID]）が存在しないように制御したり、ブランドテーブル側から特定のブランドを削除した場合、そのブランドに関連する商品を取扱商品から自動的に連動して削除させたりといった振る舞い（制約）を設定します。

MySQL Workbench の EER モデリングでは、下図のように Foreign Key タブ上の Foreign Key Option を選択するだけで外部キー制約の設定が行えます。

【On Update と On Delete】

外部キー制約のオプションは、更新時（On Update）、削除時（On Delete）に大別されます。

これらは、親テーブル側（上図ではブランド）側の動作を示しており、親テーブル側のレコードが更新されたり（On Update）、削除されたり（On Delete）したときに、子テーブル（上図では取扱商品）の関連レコードの扱いを定義するという意味です。
そして、実際の関連レコードの扱いは、上図の赤囲みのドロップダウンリストから選択します。

【外部キー制約の種類】

ドロップダウンリストから選択可能な外部キー制約は次のとおりです。

RESTRICT/NO ACTION
子テーブルに関連レコードがあったら、親レコードの更新/削除を許可しない。

NO ACTION はステートメントおよびすべてのトリガーが起動された後に子テーブルの値がまだ有効であれば変更は許可される。
これに対し、RESTRICT は、子テーブルに関連レコードが存在する場合、変更は絶対に許可されない。
RDBMS ではこのような厳密な動作の違いがあるものもあるが、MySQL では RESTRICT と NO ACTION の挙動は同じ。

たとえば、取扱商品（子テーブル側）の登録を始めたら、ブランド名（親テーブル側）の変更をさせないようにする場合がこれに相当します。

CASCADE
親テーブルのレコードを更新/削除したら、子テーブルのレコードも更新/削除する。
厳密には、親テーブルの主キーの値を更新したら、子テーブルの外部キーの値も同じ値で更新され、親テーブルのレコードを削除したら、そのレコードに関連する子テーブルのレコードも連動して削除される。

たとえば、特定のブランド（親テーブル側）を削除したら、そのブランドを持つすべての取扱商品（子テーブル側）を削除するようにする場合がこれに相当します。

SET NULL
親テーブルのレコードを更新/削除したら、子テーブルの外部キーの値を NULL に設定する。
これにより、当該レコードの親子関係が解除されることになる。

たとえば、ブランド（親テーブル側）を削除したとしても、取扱商品（子テーブル側）は将来の管理用にデッドストックとして残しておく場合がこれに相当します。

親テーブルの主キーを変更した場合、および親レコードを削除した場合の外部キー制約の振る舞いを表にすると、以下のようになります。

動作条件	挙動	親（子なし）	親（子あり）	子
On Update	RESTRICT/NO ACTION	親主キー更新可	親主キー更新不可	変化なし
	CASCADE	親主キー更新可	親主キー更新可	外部キー連動更新
	SET NULL	親主キー更新可	親主キー更新可	外部キーNULL
On Delete	RESTRICT/NO ACTION	親レコード削除可	親レコード削除不可	変化なし
	CASCADE	親レコード削除可	親レコード削除可	子レコード連動削除
	SET NULL	親レコード削除可	親レコード削除可	外部キーNULL

【SQL による外部キー制約の例】

前述の外部キー制約のパターンのうちのいくつかを、実際の SQL による定義でいくつか確認してみましょう。太字が外部キーの設定と制約を定義している部分です。特に赤字の部分に注目してください。

挙動については、前項のテーブルをご覧ください。

例1:
On Update: NO ACTION

On Delete: CASCADE

-- Table structure for table `取扱商品`

DROP TABLE IF EXISTS `取扱商品`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `取扱商品` (

`ブランドID` int(11) DEFAULT NULL,

`商品ID` int(11) NOT NULL AUTO_INCREMENT,

`商品名` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

PRIMARY KEY (`商品ID`),

KEY `fk_取扱商品_ブランド` (`ブランドID`),

CONSTRAINT `fk_取扱商品_ブランド` FOREIGN KEY (`ブランドID`) REFERENCES `ブランド` (`ブランドID`) ON DELETE CASCADE ON UPDATE NO ACTION

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

SET character_set_client = @saved_cs_client;

例2:
On Update: CASCADE

On Delete: SET NULL

--
-- Table structure for table `取扱商品`
--

DROP TABLE IF EXISTS `取扱商品`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `取扱商品` (
`ブランドID` int(11) DEFAULT NULL,
`商品ID` int(11) NOT NULL AUTO_INCREMENT,
`商品名` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`商品ID`),
KEY `fk_取扱商品_ブランド` (`ブランドID`),
CONSTRAINT `fk_取扱商品_ブランド` FOREIGN KEY (`ブランドID`) REFERENCES `ブランド` (`ブランドID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

【関連リンク】
MySQL Workbench 基礎講習コース

登録: 投稿 (Atom)

メニューバー

2012-12-20

倉庫など場所別に在庫数を把握する

リレーションシップ

レイアウトとスクリプト

2012-11-08

最後に躓いた点のメモ：

2012-07-18

2012-07-12

2012-07-11

2012-07-06

2012-07-05

　最後に躓いた点のメモ：