質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.47%

  • MySQL

    7140questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

MySQLで追加したカラムに値を挿入したいがdefaultは使いたくない

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,850

yajin

score 67

上記の通りですが、

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;

です。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • hiim

    2016/11/08 00:56

    もう少し具体的に細くできますか?とりあえず、category_idをdefalt値セットして追加した後にcategory_idに外部キー制約をつけたいけどできないという事でしょうか?外部キーに参照するテーブルの情報と実際に弾かれたクエリも掲載お願いします

    キャンセル

  • hiim

    2016/11/08 00:57

    ↑細くじゃなくて補足です

    キャンセル

回答 1

checkベストアンサー

+1

外部キー制約のチェックが入り、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のまま外部キー制約を付与するの2つを回答しました。
もちろんその後外部キーチェックをしてもらわないといけないので
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/08 01:34

    既存にフィールドが挿入されているarticlesテーブルがあります。
    そこにカテゴリ機能を追加したいと考えています。

    今までのものはarticlesテーブル内にcategory_id カラムを作り、0「デフォルト値」にして、あとはarticlecategoriesテーブルのidを参照したcategory_idを付け加えようと考えています。

    usersが削除された時に、articlesテーブルが削除されるのは確認できるのですが、
    今度は、今後、articlecategoriesがユーザによって削除された時にでもarticlesテーブルのフィールドを削除したいと考えています。

    キャンセル

  • 2016/11/08 01:56

    この後もう一個質問されているようですが、そちらも合わせてみてやっと意味がわかりました。回答は追記しましたが、一つの問題・課題に対する質問は一つの質問で完結するようにしないと回答が得にくいと思います。

    キャンセル

  • 2016/11/08 13:14

    申し訳御座いません。問題が切り分けするべきだと思っていましたが、同じにするべきですね。

    ちょっと再び問題が生じてきました。
    既存のものを0にしてやると、category_id の値が0の場合にarticlecategoris.idが1から始まるので挿入できない的なエラーが出てきました。

    こちら側でarticlecategories.id が1に「カテゴリなし」的な感じにしたフィールドをこちら側で1個挿入しておくべきでしょうか。

    キャンセル

  • 2016/11/08 13:19

    整理してみます。

    category_idの値をdefault句を使い0にすると通るのですが、そうなると外部キー制約が使えません。

    なにもなしにすると外部キー制約は通るのですが、ブラウザからの挿入時にcategory_idが0の場合は挿入できなくなってしまいます。

    キャンセル

  • 2016/11/08 14:05

    追記しました、「こちら側でarticlecategories.id が1に「カテゴリなし」的な感じにしたフィールドをこちら側で1個挿入しておくべきでしょうか」これが一番の解決方法でしょうね

    キャンセル

  • 2016/11/08 15:13

    ありがとうございます。

    はやり外部キー制約はすごく手ごわいです。
    articlecategoriesテーブルはユーザが自身で追加していったカテゴリをまとめていくテーブルです。
    articlecategories にusers.idに対して外部キー制約に登録して、
    insert into articlecategories('user_id', 'category_name') values('','カテゴリなし');
    にすると、エラーがでます。
    articlecategoriesのuser_idがusers.idに紐づいているのですが、空だとだめなようです。

    やはり、カテゴリを削除した場合に、そのカテゴリidに属するarticleテーブルのフィールドを削除する的なクエリを別途投げるべきでしょうか。

    キャンセル

  • 2016/11/08 15:20

    > insert into articlecategories('user_id', 'category_name') values('','カテゴリなし');

    articlecategories.user_idはusers.idに外部キー制約があるんですよね?
    それであれば
    insert into articlecategories('user_id', 'category_name') values('','カテゴリなし');
    のようにuser_idを空文字は通らないのでは?



    キャンセル

  • 2016/11/08 15:26

    insert into articlecategories('user_id', 'category_name') values('','カテゴリなし');
    のuser_idがなぜ空文字でないといけないのですか?ユーザーIDを入れてinsertすれば問題のない話だと思うのですが、そんあたりの値の整合生がとれていたらわざわざ削除のクエリを投げなくてもカテゴリ削除すれば紐付いているレコードも消えるはずです。

    キャンセル

  • 2016/11/08 15:32

    複数ユーザがいるので、全員がそれぞれのカテゴリなしを登録している状態です。
    なので、一人のuser_idを登録できないのです。

    キャンセル

  • 2016/11/08 15:32

    やはり、articles.category_idのarticlecategories.idへの外部キー制約はなしにしたいと思いますがだめでしょうか。
    カテゴリを削除された場合のarticlesの記事削除は、別途クエリをなげていく方法をとりたいと思います。

    delete from articles where category_id = 2;
    的な感じでよろしいでしょうか。

    キャンセル

  • 2016/11/08 16:00

    > やはり、articles.category_idのarticlecategories.idへの外部キー制約はなしにしたいと思いますがだめでしょうか。

    外部制約で複数のテーブルが絡み合うとややこしくなるので良いと思います。

    > delete from articles where category_id = 2;
    それで問題ないです。

    articlesのcategory_idはdefaut 0(またはNULLでも良いですが)のまま
    そしてさきほどのarticlecategoriesテーブルの'カテゴリなし'というレコードはなしにして、articlesのarticles.category_idが0をカテゴリ無しとして扱うというので良いと思います。

    キャンセル

  • 2016/11/08 16:15

    サーバエラーが発生してましたね。

    ありがとうございます。
    色々と勉強になりました。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.47%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る

  • MySQL

    7140questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。