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

Question

運用方針として、まちがってDELETEしたりしないために、削除フラグというカラムを
設けたりする場合があると思います。
（しないですか？）

※少なくとも、2人の知人に聞いたところ、1. 削除フラグを利用したことがある、
2. 削除フラグは常に利用している、という回答でした。

そこで、いろいろ考えてみたところ、削除フラグという考え方は、
RDBの以下の機能とバッティングするのではないかと思いました。

・外部キー制約のON DELETE CASCADEが利用できない
・参照先が削除された（delete_flag = 1）データであってもPRIMARY KEYを
　参照できるので、外部キー制約違反にならない。

よって、参照整合性の維持を、DBを操作する側（PHPとか）で行わなくては
ならず、かなり面倒です。

解決案や、みなさんの考えをお聞きしたく思います。



※削除フラグカラムもプライマリーキーに含めて、マルチカラムプライマリーキーに
　して、参照元でも、マルチカラムで参照するという方法があるかもしれませんが、
　どうもしっくりいきません。

chukenkenkou · Accepted Answer

#3です。

>> 削除フラグを利用するのは作業履歴が必要なもの。
>作業履歴！確かに、言われてみればそうですね。。
>可能であれば、削除データは別テーブルに移して、そこで履歴も保存したい
>ところですが、同じテーブルで削除フラグを利用したほうが、手っ取り早い
>というのはあるのかもしれません。

「削除フラグ」を「間違って削除」、「作業履歴」にも活用という点で、気になったのでコメントします。

「削除フラグ」で「誤って削除」を確認＆復活させたり、「作業履歴を確認」というのは、かなり簡易的な方法で限界があると思います。

RDBMSのログを調べたり、トリガで履歴を残すといった方法の方が、一般的ではないでしょうか。

chukenkenkou · Answer

最初に提示すべき情報が小出しにされ、自分で調べたり試行錯誤をしている様子もなく、あまり気持ちよくアドバイスできなくなってきましたが。。。

>ただ、その場合、テーブルが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
;

chukenkenkou · Answer

テーブルの特性により、殆ど削除が発生しないものもあれば、頻繁に更新や削除を行うものもあります。
高性能を追求する上で、トラフィックの高い時間帯では、「削除フラグを立てる」（削除でなく、更新する）といった手法を採る場合もあります。

「削除フラグ」といった方法を採る場合、擬似削除したデータを、「いつまで残しておくか」、「いつ消すか」といったことを考慮する必要があります。これは、通常の操作時は、検索条件で「削除フラグが立っていないデータだけ扱う」ということでいいですが、バックアップ・リストア等を高速に行いたい場合などは、擬似削除したデータの分がオーバーヘッド要因になることがあるからです。


>外部キー制約のON DELETE CASCADEが利用できない

通常の操作では、ON DELETE CASCADEでなく、ON UPDATE CASCADEを利用することになります。メンテ日などで子テーブルのデータを実際に削除する場合、参照整合性に頼るより、参照整合性を働かないようにして、子テーブルのデータを一括して削除した方が早いこともあります。

>・参照先が削除された（delete_flag = 1）データであってもPRIMARY KEYを
>参照できるので、外部キー制約違反にならない。

「削除フラグ」といった方法を採るなら、親テーブルの操作にしても子テーブルの操作にしても、データを絞り込むキーに加え、「削除フラグが立っていない」という条件が必須になります。そうしないと、擬似削除したデータも操作することになります。
また、参照整合性使う場合、親の削除時には、子の削除も必須ですから、主キーと外部キーは、「削除フラグ」を含めた構成にする必要があります。
子だけ削除という場合を考えると、子テーブルには、さらに「子テーブルの削除」を示す「削除フラグ」が必要になり、子テーブルの操作時に検索条件で指定する必要があります。

kitchenkabu · Answer

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

yambejp · Answer

個人的には削除というもの自体ほとんど使うことはありません。
削除フラグを利用するのは作業履歴が必要なもの。
例えば修正作業であってものそのアカクロ処理というのをするのが普通。
管理・設計者が必要があって削除するならともなく、使用者がなにかする
作業については削除（＝なかったことにする）ということはあまり
考えられないとおもいます。

もちろんＳＱＬの使い方は人それぞれなので、とっておく価値のないデータ
が頻繁に発生するようなシステムであれば、修正や削除を自由になさっても
問題はないと思いますが・・・

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

#3です。

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

テーブルの特性により、殆ど削除が発生しないものもあれば、頻繁に更新や削除を行うものもあります。

No1さんのような目的が主だと思いますが、

個人的には削除というもの自体ほとんど使うことはありません。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング