自己参照テーブルでNOT NULL制約のidをどのように入れたらいいでしょうか

Question

実現したいこと

下記サンプル構造で、新規データをエラーなくインサートするにはどのようにしたらいいでしょうか？

前提

編集した履歴用のテーブルを作ると膨大な数になるため一つのテーブルで編集履歴も兼ねたテーブル構成になっていて、特に下記がネックになっています。
copy_idには、
・新規インサートは新規インサート時のPKを入れる　←これがネック
・データ編集時は元のデータのPKを入れる
(データ編集・更新時も新規インサートするが、copy_idは編集元のidを入れる)

削除は物理削除対応です。

サンプルテーブル構造

users テーブル

column	型	NULL
id	PK autoincrement	×
copy_id	int	×
name	varchar	×
email	varchar	×
penname	varchar	〇
created_at	datetime	×
updated_at	datetime	×

外部キー制約

column	対象	制約
copy_id	users.id	NO ACTION

試したこと

copy_idのNOT NULL制約を外せば簡単に解決するのは分かっているのですが、
外部キー制約もしてかっちり固めたい意図もあるようです。
また、新規インサート時に最新データのPKを取得してそこから次のPKを予想して入れる方法も考えたのですが、危険かな・・と思い止めました。
どのように実現したらいいか、またその他方法などお知恵をお借りいただければと思います。

Comments

[can110]

PKであるid列の値はオートナンバーのようなものであるということでしょうか？
すなわち問題は以下の質問と同じものでしょうか？
https://stackoverflow.com/questions/17828162/computed-field-value-based-on-other-column-while-insertion

[sazi]

> 一つのテーブルで編集履歴も兼ねたテーブル構成
履歴に関するキーは何ですか？
質問内容では、単に更新しているだけで履歴にはなっていないように見えますが？

[ogeeeeepon]

can110さん
id列はオートナンバーで、copy_idはオートナンバーではありません。
ご提示いただいたURLと同じような質問です。

saziさん
履歴に関するキーはcopy_idです。
編集する毎に編集した内容を新たにインサートしますが、編集元がどれか分かるようにcopy_idに編集元のidを入れるというような感じです。

[sazi]

copy_idを更新時に保存してツリー状態にするという事ですね
idがオートナンバーなら、新規時にネックになるという意味が分かりません。
オートナンバーは、insert時に指定しなければ新たな番号が振られるわけですから。

Answer 1

たたき台①

🔑id	copy_id	name	email	penname
1		太郎	email①	子太郎
2		花子	email②	ハナコ
3	1	太郎	email①	桃太郎
4		次郎	email③	小次郎
5	3	太郎	email④	桃太郎
6	5	タロウ	email④	桃太郎

たたき台②

🔑id	name	email	penname
1	太郎	email①	子太郎
2	花子	email②	ハナコ
3	太郎	email①	桃太郎
4	次郎	email③	小次郎
5	太郎	email④	桃太郎
6	タロウ	email④	桃太郎

🔑id	copy_id
3	1
5	3
6	5

たたき台③

🔑user_id	newest_name	newest_email	newest_penname
1	タロウ	email④	桃太郎
2	花子	email②	ハナコ
3	次郎	email③	小次郎

🔑user_id	🔑user_row_#	name	email	penname
1	1	太郎	email①	子太郎
2	1	花子	email②	ハナコ
1	2	太郎	email①	桃太郎
3	1	次郎	email③	小次郎
1	3	太郎	email④	桃太郎
1	4	タロウ	email④	桃太郎

Answer 2

自己結合で自分に外部キー結合するとon updateをno actionにしても参照されているidは変更が効かないのでおそらく運用時にはまると思いますよ。やめたほうが良いと思います。
またどうしてもというならご自身でもご指摘がある通りnullを許容するだけの話です。

上記前提をすっとばして、なんとか運用でカバーしたいなら、id=1をダミーデータとして、初期登録時にはcopy_idに1を入れて投入して、すぐ書き換えるという手はあるでしょう。ただそこまでしてやる必要があるか微妙です。

create table users(
id int primary key auto_increment,
copy_id int not null default 1,
val varchar(20),
foreign key (copy_id) references users(id) on delete no action on update no action);

/* ダミー を投入しておく */
insert into users values(1,1,'dummy');

データ投入

start transaction;
insert into users(copy_id,val) values
(1,'aaa'),(1,'bbb'),(1,'ccc');
update users set copy_id=id where copy_id=1;
commit;

Answer 3

構造を変えたくないのであれば、もう一文SQLを記載するのはいかがでしょうか。
具体的にはINSERTの直後にUPDATEをさせる方法です。
直前のINSERTしたidを取ってきてcopy_idに入れるSQLとなります。

Answer 4

また、新規インサート時に最新データのPKを取得してそこから次のPKを予想して入れる方法も考えたのですが、危険かな・・と思い止めました。

PKの自動採番を止めてしまってはどうでしょうか？