削除フラグってどうなんでしょう？

質問

運用方針として、まちがってDELETEしたりしないために、削除フラグというカラムを
設けたりする場合があると思います。
（しないですか？）

※少なくとも、2人の知人に聞いたところ、1. 削除フラグを利用したことがある、
2. 削除フラグは常に利用している、という回答でした。

そこで、いろいろ考えてみたところ、削除フラグという考え方は、
RDBの以下の機能とバッティングするのではないかと思いました。

・外部キー制約のON DELETE CASCADEが利用できない
・参照先が削除された（delete_flag = 1）データであってもPRIMARY KEYを
　参照できるので、外部キー制約違反にならない。

よって、参照整合性の維持を、DBを操作する側（PHPとか）で行わなくては
ならず、かなり面倒です。

解決案や、みなさんの考えをお聞きしたく思います。

※削除フラグカラムもプライマリーキーに含めて、マルチカラムプライマリーキーに
　して、参照元でも、マルチカラムで参照するという方法があるかもしれませんが、
　どうもしっくりいきません。

回答No.4

chukenkenkou · ベストアンサー

#3です。

>> 削除フラグを利用するのは作業履歴が必要なもの。
>作業履歴！確かに、言われてみればそうですね。。
>可能であれば、削除データは別テーブルに移して、そこで履歴も保存したい
>ところですが、同じテーブルで削除フラグを利用したほうが、手っ取り早い
>というのはあるのかもしれません。

「削除フラグ」を「間違って削除」、「作業履歴」にも活用という点で、気になったのでコメントします。

「削除フラグ」で「誤って削除」を確認＆復活させたり、「作業履歴を確認」というのは、かなり簡易的な方法で限界があると思います。

RDBMSのログを調べたり、トリガで履歴を残すといった方法の方が、一般的ではないでしょうか。

お礼

度々、ご回答ありがとうございます。

>「削除フラグ」で「誤って削除」を確認＆復活させたり、「作業履歴を確認」というのは、かなり簡易的な方法で限界があると思います。

確かにそうですよね。「とりあえず感」が否めません。

>RDBMSのログを調べたり、トリガで履歴を残すといった方法の方が、一般的ではないでしょうか。

ログという方法もありますね。万が一の時に、DBAに泣きつくとか。。
トリガ！そうですね。トリガなら、かなり理想に近いことが出来そうです。
ただ、その場合、テーブルが500個あれば、トリガも500個必要になってきますでしょうか？
何か、いい方法ありますでしょうか？

回答No.5

chukenkenkou

最初に提示すべき情報が小出しにされ、自分で調べたり試行錯誤をしている様子もなく、あまり気持ちよくアドバイスできなくなってきましたが。。。

>ただ、その場合、テーブルが500個あれば、トリガも500個必要になってきますでしょうか？

そんなのは、ケースバイケースです。前に回答したように、テーブルの特性によります。

>その、「削除フラグ」を含めた構成にする、というのがどうもしっくりいきません。

「しっくりしない」といった曖昧な表現では、アドバイスのしようがありません。

>InnoDBは、主キーの更新コストが高いらしいのです

InnoDB、あるいはMySQLに限った話ではないですが？

>上記のようなマルチカラムで主キーにする場合はどうなるのでしょうか？
>できれば、
>　id INT NOT NULL AUTO_INCREMENT
>のような人工キーを単独で、主キーにしたいなと考えています。

こういった情報は、最初に提示してください。また、MySQLのバージョンも未提示ですよね？
「削除の印」は、必ずしもフラグ用の列を設けなければならない訳ではありません。例えば、主キーをcharなどで定義し、アプリ側では「主キー＋削除フラグ」といった構造体で参照するといった方法もあります。ただ、そういう方式にすると、MySQLのauto_incrementは使えなくなります。
こういった方法でなくても、行を一意に識別でき、なおかつ有効なデータかどうかが分かればいいので、例えば提示のケースでは、擬似削除の印として、「id列の値を負にする」といった方法もあります。ただ、インデクスの先頭構成列の更新は、高コストになるので、できれば避けたいところです。

最後にSQL例を示しますので、自分でも調べたり、試行錯誤してみてください。

＝＝＝＝＝例題＝＝＝＝＝
drop table if exists t11;
drop table if exists t21;

create table t11
(t11key int not null auto_increment,
t11data varchar(30),
t11log timestamp,
primary key(t11key) # 削除データは負で表す
-- primary key(t11key,t11flag) # auto_incrementが先頭なら、InnoDBでも動く
-- MySQLで保証した使い方かは未確認
-- primary key(t11flag,t11key) # auto_incrementが2番目なら、1075で想定通りエラー
)
engine=innodb
;

create table t21
(t21key bigint primary key auto_increment, # 削除は負で表す
t11key int not null,
t21data varchar(30),
t21log timestamp,
constraint t11t21
foreign key(t11key)
references t11(t11key)
on delete cascade
on update cascade
)
engine=innodb
;

insert into t11(t11data) values
('aaa'),
('ccc'),
('eee');

insert into t21(t11key,t21data)
select
t11key,
'aaa1'
from t11
where t11data='aaa' and t11key>0
;
insert into t21(t11key,t21data)
select
t11key,
'ccc1'
from t11
where t11data='ccc' and t11key>0
;
insert into t21(t11key,t21data)
select
t11key,
'ccc2'
from t11
where t11data='ccc' and t11key>0
;
insert into t21(t11key,t21data)
select
t11key,
'ccc3'
from t11
where t11data='ccc' and t11key>0
;

-- 結果確認（全データ）
select * from t11 order by t11key;
select * from t21 order by t11key,t21key;

-- 結果確認（有効データのみ）
select * from t11 where t11key>0 order by t11key;
select * from t21 where t21key>0 and t11key>0 order by t11key,t21key;

-- 親の擬似削除（参照整合性の動作で、子も擬似削除）
update t11
set t11key=-t11key
where t11key=1
;

-- 子だけの擬似削除
update t21
set t21key=-t21key
where t21key=3 and t11key>0
;

お礼

ご回答ありがとうございました。

SQL確認しました。これなら、削除フラグカラムを用意しなくても、
参照整合性を保ったまま、擬似削除ができますね。
この方法は初めて知りました。ありがとうございます。

ちなみに、この質問は幾分アンケート的にお伺いしたつもりです。
なのに、「人工キーを単独カラムで主キー」と後で付け加えたため、
あたかも、今まさに開発に取り掛かっていて困っているみたいな
雰囲気を出してしまい、申し訳ありませんでした。

念のため、付け加えておきます。

・トリガー
ON DELETE CASCADE は、トリガーを起動できないようなので諦めます。
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
> Currently, triggers are not activated by cascaded foreign key actions.

・InnoDBの主キーの更新コスト
MyISAMと比べてということです。言葉足らずでした。

・MySQLのバージョン
特にバージョンは関係ないのです。どのDBMSということも関係ありません。
削除フラグを使うと、外部キーがややこしくなるけれども、皆さんは
どうされてますか？ということを聞きたかったのです。

・しっくりしないという意味
理由が明確な訳ではありませんが、私は、自然キーより人工キーを
主キーにする方がいいのではないかと思っています。
理由は、自然キーは変化する可能性があるということと、人工キーは、
いい加減な理由ですが、Ruby on Rails/symfony/cakePHPといった
FWで採用されているので、今の流行かな？また、第一カラムが
常にidなら、カラム名も考えなくていいし楽だ、という理由です。

また、私の考える人工キーとは、INT NOT NULL AUTO_INCREMENT のことであり、
全くなんの意味もない情報であるべきだと思っています。
ですので、ご提示いただいた例のように「値を負にする」ことによって、
擬似削除されたという「意味」を含有してしまうのは、私の考える人工キーに
反します。
主キーを更新するくらいなら、削除フラグの方を選ぶかもしれません。
削除フラグもいやだという理由は、せっかく完璧な人工キーがあるのに、
削除フラグカラムとセットになっているのでは、主キーが純粋に「人工的」に
ならないと思うためです。

ただ、これは私の、漠然とした考にすぎません。

ご意見、SQL例ありがとうございました。

回答No.3

chukenkenkou

テーブルの特性により、殆ど削除が発生しないものもあれば、頻繁に更新や削除を行うものもあります。
高性能を追求する上で、トラフィックの高い時間帯では、「削除フラグを立てる」（削除でなく、更新する）といった手法を採る場合もあります。

「削除フラグ」といった方法を採る場合、擬似削除したデータを、「いつまで残しておくか」、「いつ消すか」といったことを考慮する必要があります。これは、通常の操作時は、検索条件で「削除フラグが立っていないデータだけ扱う」ということでいいですが、バックアップ・リストア等を高速に行いたい場合などは、擬似削除したデータの分がオーバーヘッド要因になることがあるからです。

>外部キー制約のON DELETE CASCADEが利用できない

通常の操作では、ON DELETE CASCADEでなく、ON UPDATE CASCADEを利用することになります。メンテ日などで子テーブルのデータを実際に削除する場合、参照整合性に頼るより、参照整合性を働かないようにして、子テーブルのデータを一括して削除した方が早いこともあります。

>・参照先が削除された（delete_flag = 1）データであってもPRIMARY KEYを
>参照できるので、外部キー制約違反にならない。

「削除フラグ」といった方法を採るなら、親テーブルの操作にしても子テーブルの操作にしても、データを絞り込むキーに加え、「削除フラグが立っていない」という条件が必須になります。そうしないと、擬似削除したデータも操作することになります。
また、参照整合性使う場合、親の削除時には、子の削除も必須ですから、主キーと外部キーは、「削除フラグ」を含めた構成にする必要があります。
子だけ削除という場合を考えると、子テーブルには、さらに「子テーブルの削除」を示す「削除フラグ」が必要になり、子テーブルの操作時に検索条件で指定する必要があります。

お礼

ご回答ありがとうございます。

>高性能を追求する上で、トラフィックの高い時間帯では、「削除フラグを立てる」（削除でなく、更新する）といった手法を採る場合もあります。

そうなんですね。DELETEが高コストだとは知りませんでした。

>バックアップ・リストア等を高速に行いたい場合などは、擬似削除したデータの分がオーバーヘッド要因になることがあるからです。

必然的に、擬似削除したデータもバックアップを取ることになりますね。

>通常の操作では、ON DELETE CASCADEでなく、ON UPDATE CASCADEを利用することになります。
>また、参照整合性使う場合、親の削除時には、子の削除も必須ですから、主キーと外部キーは、「削除フラグ」を含めた構成にする必要があります。

その、「削除フラグ」を含めた構成にする、というのがどうもしっくりいきません。
InnoDBは、主キーの更新コストが高いらしいのですが、上記のようなマルチカラムで主キーにする場合はどうなるのでしょうか？

できれば、
　id INT NOT NULL AUTO_INCREMENT
のような人工キーを単独で、主キーにしたいなと考えています。

回答No.2

kitchenkabu

No1さんのような目的が主だと思いますが、
性能の観点から「削除フラグ」を利用することもあります。
deleteはコストが高いので、フラグを更新するだけにして、
例えば低負荷の時間帯にまとめてdeleteするような設計に
することがあります。

お礼

ご回答ありがとうございます。

> deleteはコストが高いので、フラグを更新するだけにして、
そうなんですか。知りませんでした。
ただ、それと外部キーの便利さを天秤にかけたら、やはり、外部キーは捨てがたいです。

回答No.1

yambejp

個人的には削除というもの自体ほとんど使うことはありません。
削除フラグを利用するのは作業履歴が必要なもの。
例えば修正作業であってものそのアカクロ処理というのをするのが普通。
管理・設計者が必要があって削除するならともなく、使用者がなにかする
作業については削除（＝なかったことにする）ということはあまり
考えられないとおもいます。

もちろんＳＱＬの使い方は人それぞれなので、とっておく価値のないデータ
が頻繁に発生するようなシステムであれば、修正や削除を自由になさっても
問題はないと思いますが・・・

お礼

ご回答ありがとうございます。

> 削除フラグを利用するのは作業履歴が必要なもの。
作業履歴！確かに、言われてみればそうですね。。
可能であれば、削除データは別テーブルに移して、そこで履歴も保存したい
ところですが、同じテーブルで削除フラグを利用したほうが、手っ取り早い
というのはあるのかもしれません。

ただ、可能であれば、削除フラグというものはなんとかしたいですね。
（個人で何か作る時は削除フラグは使いません。）

ご意見、ありがとうございました。