- このトピックには8件の返信、1人の参加者があり、最後に
により3時間、 33分前に更新されました。
-
投稿
-
x64_disAIの2.21.0と2.21.1b2をwindows11上で使っています。
sql
CREATE TABLE “mail_member” (
id TEXT NOT NULL REFERENCES mail(id)
, member INTEGER NOT NULL REFERENCES member(id)
);
で作成したテーブルが
sql
CREATE TABLE “mail_member” (
id TEXT NOT NULL REFERENCES “mails”(id)
, member INTEGER NOT NULL REFERENCES “members”(id)
);
左のリスト内からテーブルをクリックしてソースタブを選択で表示された内容FK先のテーブル名に勝手にsが付きます。
そんなテーブルは無いので操作でエラーが出ます。
なぜこうなるのかを教えてください。sqlite3です。
テーブルの制約やデータ型を変更する際に現テーブルを一時的に名前を変えて新テーブルを作ってデータを移行することをやっているときにFKで参照しているテーブルが名前が変わったからそれが反映された？ということでしょうか？ユーザに何のアラートも無く勝手に書き換えるのは問題ある挙動だと思います。回避する方法はあるのでしょうか？FKの制約を外せばこの挙動は無かったのでしょうか？かなり大きな不具合だと思います。
回答を貰えないなら削除してください
abeq さんこんにちは。お返事が遅くなり申し訳ありません。
（早くてもお返事は週末等になってしまいます。）
テーブルのソース(CREATE TABLE)で、FK先のテーブル名が正しく表示されない件ですが、こちらで検証したのですが、再現できませんでした。
後からテーブル名を変更した際に古いテーブル名で表示されるのかとも考えましたが、テーブルをリネームした後にテーブルのソースを確認すると新しいテーブル名になっていました。
A5:SQL Mk-2 の仕組みとしては、
select sql from sqlite_master where type=’table’ and name = ‘テーブル名’
のようなSQLで、CREATE TABLE 文を取得し、整形してから表示しているのですが、こちらではどのように出力されますでしょうか？>テーブルの制約やデータ型を変更する際に現テーブルを一時的に名前を変えて新テーブルを作ってデータを移行することをやっているときにFKで参照しているテーブルが名前が変わったからそれが反映された？ということでしょうか？
こちら気になったので追加で調査してみたのですが、SQLite における外部キーの管理方法からの問題ではないかと思えます。
SQLiteは他のテーブルにあるようなシステムカタログを持たず、テーブルの情報はCREATE TABLE をテキストのまま保持します。
この仕組みのため、テーブルのリネーム時は参照している外部キーを持つテーブルがないか sqlite_master テーブルを走査しリネーム対象テーブルがあればCREATE TABLE文を書き換える挙動をするようです。
しかし、参照先テーブルを削除する（エラーにならない）などすると外部キーがそのまま残るなどして、問題となるケースがあるようです。以下例)
1. テーブルA を作成
2. テーブルB を作成(外部キーでテーブルAを参照)
3. テーブルA を テーブルA´ に変更 (この時点で テーブルB の外部キーは テーブルA´ を指す)
4. 新しく テーブルA を作成
5. テーブルA´ を削除
6. 最終的に テーブルB の外部キーは(存在しなくなった) テーブルA´ を指したまま残る松原様こんにちわ、ご回答をありがとうございます。
SQLiteでは制約や型の変更ができないので、そうするために1. 現テーブルの名前を退避テーブル名に変更
ALTER TABLE stuff RENAME TO stuffs;2. 制約や型などを修正したテーブルを作成
CREATE TABLE stuff (...;3. 退避先からデータ移行
INSERT INTO stuff SELECT * FROM stuffs;という手順を行いました。
このテーブルを外部参照している別テーブルのフィールドの外部キー制約のテーブル名が退避テーブル名に勝手に変更されてしまいました。CREATE TABLE order (
…
staff INTEGER NOT NULL REFERENCES stuffs(id); –←stuff(id)なのに勝手に変更
)SQLiteの仕様と言われてもDB Browser for SQLiteで同操作を行っても問題はありませんでした。
A5SQL Mk2で特にこういう実装は行っていないということであれば承知しました。abeq さんこんにちは。
調査したところ、SQLite 3.25.0 あたりから、テーブルをリネームすると外部キーで参照元テーブルのテーブル名を変更するようになったようです。
互換性のオプションとして
PRAGMA legacy_alter_table = ON;
を設定(実行)することで、以前の挙動(テーブルリネームで参照元テーブル名を変更しない)になるようです。
https://sqlite.org/pragma.html#pragma_legacy_alter_table松原様こんにちわ、ご教示をありがとうございます。
こちらでもこの挙動がsqlite3の仕様であることを確認しました。
A5:SQL側を疑って申し訳ありませんでした。
こちらが確認した手順は古いテーブルを先にリネームするのではなく新しいテーブルを一時名で作成してデータ移行、古いテーブルをDROP、してから新しいテーブルをリネームする、ことで問題が回避できました。
ありがとうございました。
