運用方針として、まちがってDELETEしたりしないために、削除フラグというカラムを
設けたりする場合があると思います。
(しないですか?)
※少なくとも、2人の知人に聞いたところ、1. 削除フラグを利用したことがある、
2. 削除フラグは常に利用している、という回答でした。
そこで、いろいろ考えてみたところ、削除フラグという考え方は、
RDBの以下の機能とバッティングするのではないかと思いました。
・外部キー制約のON DELETE CASCADEが利用できない
・参照先が削除された(delete_flag = 1)データであってもPRIMARY KEYを
参照できるので、外部キー制約違反にならない。
よって、参照整合性の維持を、DBを操作する側(PHPとか)で行わなくては
ならず、かなり面倒です。
解決案や、みなさんの考えをお聞きしたく思います。
※削除フラグカラムもプライマリーキーに含めて、マルチカラムプライマリーキーに
して、参照元でも、マルチカラムで参照するという方法があるかもしれませんが、
どうもしっくりいきません。
No.4ベストアンサー
- 回答日時:
#3です。
>> 削除フラグを利用するのは作業履歴が必要なもの。
>作業履歴!確かに、言われてみればそうですね。。
>可能であれば、削除データは別テーブルに移して、そこで履歴も保存したい
>ところですが、同じテーブルで削除フラグを利用したほうが、手っ取り早い
>というのはあるのかもしれません。
「削除フラグ」を「間違って削除」、「作業履歴」にも活用という点で、気になったのでコメントします。
「削除フラグ」で「誤って削除」を確認&復活させたり、「作業履歴を確認」というのは、かなり簡易的な方法で限界があると思います。
RDBMSのログを調べたり、トリガで履歴を残すといった方法の方が、一般的ではないでしょうか。
度々、ご回答ありがとうございます。
>「削除フラグ」で「誤って削除」を確認&復活させたり、「作業履歴を確認」というのは、かなり簡易的な方法で限界があると思います。
確かにそうですよね。「とりあえず感」が否めません。
>RDBMSのログを調べたり、トリガで履歴を残すといった方法の方が、一般的ではないでしょうか。
ログという方法もありますね。万が一の時に、DBAに泣きつくとか。。
トリガ!そうですね。トリガなら、かなり理想に近いことが出来そうです。
ただ、その場合、テーブルが500個あれば、トリガも500個必要になってきますでしょうか?
何か、いい方法ありますでしょうか?
No.5
- 回答日時:
最初に提示すべき情報が小出しにされ、自分で調べたり試行錯誤をしている様子もなく、あまり気持ちよくアドバイスできなくなってきましたが。
。。>ただ、その場合、テーブルが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-fo …
> 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
- 回答日時:
テーブルの特性により、殆ど削除が発生しないものもあれば、頻繁に更新や削除を行うものもあります。
高性能を追求する上で、トラフィックの高い時間帯では、「削除フラグを立てる」(削除でなく、更新する)といった手法を採る場合もあります。
「削除フラグ」といった方法を採る場合、擬似削除したデータを、「いつまで残しておくか」、「いつ消すか」といったことを考慮する必要があります。これは、通常の操作時は、検索条件で「削除フラグが立っていないデータだけ扱う」ということでいいですが、バックアップ・リストア等を高速に行いたい場合などは、擬似削除したデータの分がオーバーヘッド要因になることがあるからです。
>外部キー制約の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.1
- 回答日時:
個人的には削除というもの自体ほとんど使うことはありません。
削除フラグを利用するのは作業履歴が必要なもの。
例えば修正作業であってものそのアカクロ処理というのをするのが普通。
管理・設計者が必要があって削除するならともなく、使用者がなにかする
作業については削除(=なかったことにする)ということはあまり
考えられないとおもいます。
もちろんSQLの使い方は人それぞれなので、とっておく価値のないデータ
が頻繁に発生するようなシステムであれば、修正や削除を自由になさっても
問題はないと思いますが・・・
ご回答ありがとうございます。
> 削除フラグを利用するのは作業履歴が必要なもの。
作業履歴!確かに、言われてみればそうですね。。
可能であれば、削除データは別テーブルに移して、そこで履歴も保存したい
ところですが、同じテーブルで削除フラグを利用したほうが、手っ取り早い
というのはあるのかもしれません。
ただ、可能であれば、削除フラグというものはなんとかしたいですね。
(個人で何か作る時は削除フラグは使いません。)
ご意見、ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 教えて!goo なんで同じ質問を繰り返す人の質問に「指摘」すると削除されるのですか? 5 2023/06/08 20:37
- Visual Basic(VBA) Excelのマクロコードについて教えてください 1 2022/03/27 12:02
- Windows 10 Windows 10の[アプリと機能]に表示されない旧バージョンのアプリを削除したい 1 2022/04/05 20:25
- Chrome(クローム) Cookieを削除 他サイトでの自動ログインなどの設定も削除されますか パソコン クロームです 1 2022/09/10 21:47
- その他(IT・Webサービス) グーグルドメインズの解約方法 1 2022/08/14 12:52
- Excel(エクセル) 名前と日付が一致する箇所にフラグを立てる関数が知りたいです 4 2022/08/11 02:24
- Gmail GmailではメールをDelキーで削除可能でしょうか? 1 2022/08/17 10:43
- Outlook(アウトルック) Outlookでメールをデリートキーで削除した場合における、メールの移動先フォルダを変更したい。 2 2022/10/31 00:53
- フリーソフト USB内のフィアルの復活ソフトは? 4 2023/03/25 08:54
- X(旧Twitter) Twitterで一度完全削除したアカウントで同じ人が同じアカウントで最近復活 1 2023/03/30 18:02
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・【大喜利】【投稿~12/2】 国民的アニメ『サザエさん』が打ち切りになった理由を教えてください
- ・ちょっと先の未来クイズ第5問
- ・【お題】ヒーローの謝罪会見
- ・これが怖いの自分だけ?というものありますか?
- ・スマホに会話を聞かれているな!?と思ったことありますか?
- ・それもChatGPT!?と驚いた使用方法を教えてください
- ・見学に行くとしたら【天国】と【地獄】どっち?
- ・【大喜利】【投稿~11/22】このサンタクロースは偽物だと気付いた理由とは?
- ・お風呂の温度、何℃にしてますか?
- ・とっておきの「まかない飯」を教えて下さい!
- ・2024年のうちにやっておきたいこと、ここで宣言しませんか?
- ・いけず言葉しりとり
- ・土曜の昼、学校帰りの昼メシの思い出
- ・忘れられない激○○料理
- ・あなたにとってのゴールデンタイムはいつですか?
- ・とっておきの「夜食」教えて下さい
- ・これまでで一番「情けなかったとき」はいつですか?
- ・プリン+醤油=ウニみたいな組み合わせメニューを教えて!
- ・タイムマシーンがあったら、過去と未来どちらに行く?
- ・遅刻の「言い訳」選手権
- ・好きな和訳タイトルを教えてください
- ・うちのカレーにはこれが入ってる!って食材ありますか?
- ・おすすめのモーニング・朝食メニューを教えて!
- ・「覚え間違い」を教えてください!
- ・とっておきの手土産を教えて
- ・「平成」を感じるもの
- ・秘密基地、どこに作った?
- ・カンパ〜イ!←最初の1杯目、なに頼む?
- ・この人頭いいなと思ったエピソード
- ・あなたの「必」の書き順を教えてください
- ・ギリギリ行けるお一人様のライン
- ・10代と話して驚いたこと
- ・大人になっても苦手な食べ物、ありますか?
- ・14歳の自分に衝撃の事実を告げてください
- ・家・車以外で、人生で一番奮発した買い物
- ・人生最悪の忘れ物
- ・あなたの習慣について教えてください!!
- ・都道府県穴埋めゲーム
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excel テーブル内の空白行の削除
-
トリガって、自分自身のテーブ...
-
マージレプリケーション
-
リレーションについて
-
Accessでデータシートに同じデ...
-
一つ前に戻るには…
-
SELECT 文の NULL列は?
-
L2SWはARPテーブルを持っている?
-
SQL、2つのテーブルで条件一致...
-
外部キーだけのテーブル(主キ...
-
テーブルに存在しない列をselec...
-
sqlplusで表示が変なので、出力...
-
会社の飲み会の幹事になり、座...
-
テーブル名をカラムとして取得...
-
SQL*LoaderでCSVから指定した列...
-
テーブルやカラムの物理名のネ...
-
mysqlのupdate構文についての質...
-
バッチファイルを使ったテーブ...
-
バイキングのトレーの形
-
Oracleのview、synonymをCOMPIL...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel テーブル内の空白行の削除
-
アクセスのクエリでレコード削...
-
トリガって、自分自身のテーブ...
-
HiRDBのPURGEとDROPの違い
-
アクセス2010について
-
ACCESSのマクロでテーブルのデ...
-
CSVファイルから一括して削除す...
-
Access2000の削除クエリで消し...
-
テーブルデータの削除による対応策
-
mysqlで間接クエリー実行させた...
-
Accessのテーブルをすべて削除
-
アクセス通貨型or数値型について
-
削除フラグってどうなんでしょう?
-
リレーションについて
-
ワークテーブルなのに自動で削...
-
列のDEFAULT設定を削除するコマ...
-
MySQLのストアドでファイルを削除
-
MySQLを勉強中ですが、作業の内...
-
レコードの削除で delete from ...
-
物理削除と論理削除、どっちが...
おすすめ情報