MySQLのALTER TABLEのメモ

(2017-04-15)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.7 ALTER TABLE 構文

CREATE TABLE t0 (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    c1 VARCHAR(30),
    c2 VARCHAR(30)
);
CREATE TABLE t2 (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
);
ALTER TABLE t0 RENAME t1;
ALTER TABLE t1
  ADD COLUMN t2_id BIGINT UNSIGNED AFTER id,
  ADD COLUMN c3 INTEGER NOT NULL AFTER t2_id,
  MODIFY COLUMN c1 VARCHAR(30) NOT NULL,
  DROP COLUMN c2,
  ADD INDEX (c3),
  ADD FOREIGN KEY (t2_id) REFERENCES t2(id) ON UPDATE CASCADE ON DELETE CASCADE
;
mysql> SHOW CREATE TABLE t1 \G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `t2_id` bigint(20) unsigned DEFAULT NULL,
  `c3` int(11) NOT NULL,
  `c1` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `c3` (`c3`),
  KEY `t2_id` (`t2_id`),
  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`t2_id`) REFERENCES `t2` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ALTER TABLE t1
  DROP INDEX c3,
  DROP FOREIGN KEY t1_ibfk_1,
  DROP INDEX t2_id
;
mysql> SHOW CREATE TABLE t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `t2_id` bigint(20) unsigned DEFAULT NULL,
  `c3` int(11) NOT NULL,
  `c1` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)