petitviolet blog

    外部キー貼り直し手順メモ

    2014-01-29

    QiitaMySQL

    user テーブルと article テーブルがあり、article テーブルには user_id という user.id を参照する外部キーを持っている。 そして、article.user_id に on (delete|update) cascade を付け加えたかった

    ###テーブルの構造および外部キーの名前を確認する

    mysql> show create table user \G
    *************************** 1. row ***************************
           Table: user
    Create Table: CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(30) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)
    
    ERROR:
    No query specified
    
    mysql> show create table article \G
    *************************** 1. row ***************************
           Table: article
    Create Table: CREATE TABLE `article` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `user_id` int(11) DEFAULT NULL,
      `content` text,
      PRIMARY KEY (`id`),
      KEY `user_id` (`user_id`),
      CONSTRAINT `article_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)
    
    ERROR:
    No query specified
    
    

    ###外部キーを削除する

    alter table article drop foreign key article_ibfk_1;
    -- alter table article drop constraint article_ibfk_1; ではだめだった
    

    ###外部キーを付ける 頂いたコメントから修正

    alter table article add constraint article_ibfk_1 foreign key (user_id) references user(id) on delete cascade on update cascade;
    -- alter table article add foreign key (user_id) references user(id) on delete cascade on update cascade;
    

    なお、ここで外部キーとして整合性のないデータが article テーブルに入っていると

    ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails …
    

    といったエラーが出るため、そういった不整合なデータは削除するなど何らかの対処をする必要がある

    ###結果

    mysql> show create table article \G;
    *************************** 1. row ***************************
           Table: article
    Create Table: CREATE TABLE `article` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `user_id` int(11) DEFAULT NULL,
      `content` text,
      PRIMARY KEY (`id`),
      KEY `user_id` (`user_id`),
      CONSTRAINT `article_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.01 sec)
    

    以上、メモでした。

    from: https://qiita.com/petitviolet/items/6e8d97b600721dcf7dc0