.png)
お世話になっております。kotsujiです。親子関係にあるテーブルで、子テーブルのユニーク制約に違反したときの振舞いが不自然に感じ、これは仕様なのか、それともバグなのか分からなかったので質問させてください。
親子関係にある２つのテーブルがあります。
・親テーブル：parents
・子テーブル：children
childrenテーブルはparentsテーブルのparent_idを参照して、parentsテーブルの一部として構成しています。
parentsテーブルの構成
childrenテーブルの構成
※目視できるようchild_idとparent_idもSHOWチェックボックスにチェックしています。
※デモ用に、簡単にユニーク制約違反になるようchild_nameをkeyにしています。
このテーブル構成で、新規データの追加作業を行います。
１．親フォームで親の名前(p1)を入力し、Newボタンを押します。
２．詳細フォームが表示されたら、子供の名前(c1)を入力してSaveします。
３．追加した子供の名前(c1)が親フォームで確認できます。
当然ながら、この時点ではまだ実際のテーブルには保存されていません。
４．この状態からもう1度Newボタンを押して、同じ名前の子供(c1)を追加(save)し、あえてユニーク制約に違反するようにします。本来ならここでユニーク制約に違反したメッセージが表示されると思ったのですが表示されず、先程と同様に親フォームには子供の名前（c1）が1件だけ表示されています。
同じ子供の名前なので、最初に追加した子供が表示されているように見えますが、実際はあとから追加した子供で上書きされています。その証拠に上の図のchild_idが最初に追加したchild_idと変わっています。ユニーク制約は効いておらず、さらにデータを上書きするという現象が起こっています。
そして、この時点でも当然ながら、実際のテーブルにはまだデータは追加されていません。
５．この状態で、親フォームのSaveボタンを押してテーブルにデータを保存しようとすると、ここで初めてユニーク制約に違反しているエラーメッセージが表示されます。
そして、このタイミングでスプレッドシートの親子テーブルを確認すると、エラーメッセージが出ているにもかかわらず、データが格納されています。
parentsテーブル
childrenテーブル
６．この状態でもう1度Saveボタンを押すと、子テーブルのユニーク制約に違反したエラーメッセージが消え、親テーブルのユニーク制約に違反したエラーメッセージに変化します。
この場合、スプレッドシートの親子テーブルの値に変化はありませんでした。
以上が今回おこった不自然な振舞いの再現です。お手数ですが、ご確認お願い致します。
COUNT(SELECT(CHILD[CHILD ID],[_THISROW].[CHILD NAME]=[CHILD NAME]))=0
上記の関数は、同じ親を持つ子テーブルのrowのうち、CHILD NAMEのVALUEが同じROWの数を計算させ、仮に同じCHILD NAMEのROWが既に存在する場合は1以上の値を返すことから結果FALSEを返し、入力制限を行うアプローチです。
＞Appsheetでは常にUNIQUEID()をKEYのVALUEに設定することを推奨しています。
＞Child NameにValidIFの制限を施すことがBEST APPROACHです。
なるほどです。ナチュラルキーを主キーにすると今回のような問題がおこってしまうので、今後はサロゲートキーを主キーに設定し、重複チェックはValidIFの制限を利用するよう気を付けたいと思います。ありがとうございました。
Appsheetに限らず、データベースを扱うえでのbest practiceは、KEYの値に重複が発生しないように設定することです。従い、Appsheetでは常にUNIQUEID()をKEYのVALUEに設定することを推奨しています。
仮に今回、子テーブルに同じ名前が入力されないように制御したい、ということが目的でしたら子テーブルのKEY FIELDではなく、Child NameにValidIFの制限を施すことがBEST APPROACHです。
返信ありがとうございます。仰る通りchild_idをキーにすればunique関数でidを生成しているため、子供が重複することはなくなります。しかし今回は、あえてchild_nameをキーにして重複がおこるようにすることで「キーに設定している項目が重複した場合に上書きされてしまう問題」と「ユニーク制約エラーが出力されているにも関わらずテーブルに登録されてしまう」という2点の問題を取り上げました。その点についてはどのようにお考えでしょうか？
Child テーブルにあるChild IdというカラムがKEYとして設定されていないようです。Child NAMEというカラムがKEYとされていますが、Child IDに変更してください。