Q&A
もう少し具体的に細くできますか？とりあえず、category_idをdefalt値セットして追加した後にcategory_idに外部キー制約をつけたいけどできないという事でしょうか？外部キーに参照するテーブルの情報と実際に弾かれたクエリも掲載お願いします
上記の通りですが、
alter table articles add category_id int NOT NULL default 0 after user_id;
とすると、default値が設定できるのですが、外部キー制約のコマンドが通りません。
default値があるとできないとエラーを吐かれたのですが、
既存のテーブルにあるフィールド群に値を手動で挿入するためのクエリとはどんな方法があるのでしょうか。
追記：
users
articlecategories
articles
というテーブルがあり、
articlecategories には
id user_id category_name ～～ created modifiedなどの情報があります。
articlesには
id user_id category_id ～～ などの情報があります。
既存の記事の中で追加でカテゴリ機能を作っていこうと考えています。
エラーをはかれたので調べてみたところ
「参照先カラムにデフォルト値をセットしている」場合はエラーが出るとの記事を見かけました。
alter table articles add foreign key (category_id) references articlecategories(id) on update cascade on DELETE CASCADE;
です。
↑細くじゃなくて補足です
回答1件
0
ベストアンサー
外部キー制約のチェックが入り、category_idはデフォルトで0？かな？がはいっていて参照先の外部キーがどうなっているかわからないですが、articlecategoriesのidとの整合生にチェックが入り、エラーがでているので
category_idにarticlecategories.idに実際に存在する値を入れてから外部キー制約のSQLを実行するか、もしくは
SET FOREIGN_KEY_CHECKS=0; alter table articles add foreign key (category_id) references articlecategories(id) on update cascade on DELETE CASCADE; SET FOREIGN_KEY_CHECKS=1;
とりあえず、チェック飛ばして外部キー制約のコマンド通してからデータがちゃんと紐づくように値入れていかれては？
###追記
とりあえず、foreign keyのSQLが通るようには回答したつもりですが、よくよく質問文を読むと
タイトルが
MySQLで追加したカラムに値を挿入したいがdefaultは使いたくない
質問文に
既存のテーブルにあるフィールド群に値を手動で挿入するためのクエリとはどんな方法があるのでしょうか。
とありますが、しばらく考えましたが、質問の意図がわかりません。
どのカラムに何を入れたいクエリを想定されてますか？
###追記
SET FOREIGN_KEY_CHECKS=0; alter table articles add foreign key (category_id) references articlecategories(id) on update cascade on DELETE CASCADE; SET FOREIGN_KEY_CHECKS=1;
で外部キー制約を追加した後、articlesのcategory_idにはarticlecategoriesのidに存在するキーを入れる。
そうしておけばarticlecategoriesからユーザーがフィールドを削除したらそれに対応するarticlesのフィールドも削除されます。
2016/11/08 13:14と2016/11/08 13:19への回答追記
実際にどんなデータが入っているかわかりませんので、あくまでこちらの想像で簡易化して書きますが
articlecategoris.idがにたとえば今1,2,3,4,5が登録されてるとします。
その時
articlesにcategory_idというカラムを追加するのは
alter table articles add category_id int NOT NULL default 0 after user_id;
でできるが、その後articlesに対して外部キー制約を付与しようと
alter table articles add foreign key (category_id) references articlecategories(id) on update cascade on DELETE CASCADE;
を実行するとエラーがでた。
これを回避するのは上記にも書きましたが、articles.category_idの値をarticlecategoris.idに存在する値1,2,3,4,5のどれかが入っていいとチェックが入りSQLを成功できない。
なので方法として先に1,2,3,4,5の値を入れてから外部キー制約を実行するか、SQL実行時外部キーの整合生のチェックが入らないように
SET FOREIGN_KEY_CHECKS=0;
で外部キーチェックを無効にしてarticles.category_idを0のまま外部キー制約を付与するの２つを回答しました。
もちろんその後外部キーチェックをしてもらわないといけないので
SET FOREIGN_KEY_CHECKS=1;
で戻しておく。
そしてここからですが、もしSET FOREIGN_KEY_CHECKS=0の方法で追加した場合、データ的には
articles.category_idとarticlecategoris.idの整合生がおかしいままなのでarticles.category_idに1,2,3,4,5の中からデータを入れないといけない。
ここまでOKですか？
もし今後articlesにフィールドを追加する場合もarticles.category_idにはarticlecategoris.idの中に存在する値を指定しないと
defaultの0は入れる事ができないので、無理です。
なのでこの解決方法はarticlesにフィールド追加する際にはarticles.category_idを未指定（defaultの値が使われる）でのinsertは行わない。
という方法か、
defaultが0になっているのをdefaultを（確実に存在する値）1などにしてエラーが出ないようにする。
こちら側でarticlecategories.id が1に「カテゴリなし」的な感じにしたフィールドをこちら側で1個挿入しておくべきでしょうか。
テーブルの名前からブログのカテゴリーのような感じなのかな？と思いますので、その方法はあだと思います。
articles.category_idのdefault値を1に変更、articlecategoris.idの1はカテゴリなしというようなフィールドにしておく
articles.category_idを未指定でinsertした記事はカテゴリーなしとして登録される。
という流れになるので良いと思います。
投稿2016/11/07 16:20
編集2016/11/08 05:04総合スコア1689
既存にフィールドが挿入されているarticlesテーブルがあります。
そこにカテゴリ機能を追加したいと考えています。
今までのものはarticlesテーブル内にcategory_id カラムを作り、0「デフォルト値」にして、あとはarticlecategoriesテーブルのidを参照したcategory_idを付け加えようと考えています。
usersが削除された時に、articlesテーブルが削除されるのは確認できるのですが、
今度は、今後、articlecategoriesがユーザによって削除された時にでもarticlesテーブルのフィールドを削除したいと考えています。
この後もう一個質問されているようですが、そちらも合わせてみてやっと意味がわかりました。回答は追記しましたが、一つの問題・課題に対する質問は一つの質問で完結するようにしないと回答が得にくいと思います。
申し訳御座いません。問題が切り分けするべきだと思っていましたが、同じにするべきですね。
ちょっと再び問題が生じてきました。
既存のものを0にしてやると、category_id の値が0の場合にarticlecategoris.idが1から始まるので挿入できない的なエラーが出てきました。
こちら側でarticlecategories.id が1に「カテゴリなし」的な感じにしたフィールドをこちら側で1個挿入しておくべきでしょうか。
整理してみます。
category_idの値をdefault句を使い0にすると通るのですが、そうなると外部キー制約が使えません。
なにもなしにすると外部キー制約は通るのですが、ブラウザからの挿入時にcategory_idが0の場合は挿入できなくなってしまいます。
追記しました、「こちら側でarticlecategories.id が1に「カテゴリなし」的な感じにしたフィールドをこちら側で1個挿入しておくべきでしょうか」これが一番の解決方法でしょうね
ありがとうございます。
はやり外部キー制約はすごく手ごわいです。
articlecategoriesテーブルはユーザが自身で追加していったカテゴリをまとめていくテーブルです。
articlecategories にusers.idに対して外部キー制約に登録して、
insert into articlecategories('user_id', 'category_name') values('','カテゴリなし');
にすると、エラーがでます。
articlecategoriesのuser_idがusers.idに紐づいているのですが、空だとだめなようです。
やはり、カテゴリを削除した場合に、そのカテゴリidに属するarticleテーブルのフィールドを削除する的なクエリを別途投げるべきでしょうか。
> insert into articlecategories('user_id', 'category_name') values('','カテゴリなし');
articlecategories.user_idはusers.idに外部キー制約があるんですよね？
それであれば
insert into articlecategories('user_id', 'category_name') values('','カテゴリなし');
のようにuser_idを空文字は通らないのでは？
insert into articlecategories('user_id', 'category_name') values('','カテゴリなし');
のuser_idがなぜ空文字でないといけないのですか？ユーザーIDを入れてinsertすれば問題のない話だと思うのですが、そんあたりの値の整合生がとれていたらわざわざ削除のクエリを投げなくてもカテゴリ削除すれば紐付いているレコードも消えるはずです。
複数ユーザがいるので、全員がそれぞれのカテゴリなしを登録している状態です。
なので、一人のuser_idを登録できないのです。
やはり、articles.category_idのarticlecategories.idへの外部キー制約はなしにしたいと思いますがだめでしょうか。
カテゴリを削除された場合のarticlesの記事削除は、別途クエリをなげていく方法をとりたいと思います。
delete from articles where category_id = 2;
的な感じでよろしいでしょうか。
> やはり、articles.category_idのarticlecategories.idへの外部キー制約はなしにしたいと思いますがだめでしょうか。
外部制約で複数のテーブルが絡み合うとややこしくなるので良いと思います。
> delete from articles where category_id = 2;
それで問題ないです。
articlesのcategory_idはdefaut 0（またはNULLでも良いですが）のまま
そしてさきほどのarticlecategoriesテーブルの'カテゴリなし'というレコードはなしにして、articlesのarticles.category_idが0をカテゴリ無しとして扱うというので良いと思います。
サーバエラーが発生してましたね。
ありがとうございます。
色々と勉強になりました。
あなたの回答
tips
プレビュー
