serial は、自動採番列を作成する為に使いますが、その際列名でユニークインデックスが作成されています。しかし、bigint(20) unsigned NOT NULL AUTO_INCREMENT で作成した場合は何も作成されないので、削除しても動作します。 ストーリー 掲示板テーブルを作成しましたが、主キーを自動採番列としているので、一つの掲示板でしか使用する事が出来ないため、掲示板(スレッド)を区別する為の ID として no 列を追加する事が目的です。 現状確認 show create table board で、MySQL に登録されている create table 文を取得します。この時、UNIQUE KEY `row_no` (`row_no`) が定義されている事が解ります。 列追加 alter table `board` add `no` VARCHAR(20) after `row_no` で、元の主キーである row_no の後ろに no 列を追加します。この時、既にデータが投入されている場合は、no の中は null が設定されます。 主キー削除 主キーを作り変える為に、いったん現在の主キーを alter table board drop primary key で削除します。そして、show create table board で定義の変化を確認します。 主キー作成 既にデータが投入されている場合は、no 列が null の為、主キーが作成できません。なので、全ての列のデータに何かデータをセットします( update board set no = '0' ) その後、alter table board add primary key(`row_no`,`no`) で主キーを作成します。 serial で作成されたユニークキーの削除 特に実行する必要はありませんが、それほど必要なインデックスでは無いので削除する場合は、show index from board でインデックス名を確認して、alter table board drop index row_no で削除します。 データの復帰 既に新しいフォーマットで作成されたデータがある場合は、insert into board select * from board2 という SQL でデータを投入できます。 ※ board2 が保存されていた新しいフォーマットのデータ
-- 最初に作成したテーブルのSQL CREATE TABLE `board` ( `row_no` serial, `body` varchar(1000), `subject` varchar(200), `from` varchar(20), `pdate` datetime, `cdate` datetime, primary key(`row_no`) ); show create table board; CREATE TABLE `board` ( `row_no` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `body` varchar(1000) DEFAULT NULL, `subject` varchar(200) DEFAULT NULL, `from` varchar(20) DEFAULT NULL, `pdate` datetime DEFAULT NULL, `cdate` datetime DEFAULT NULL, PRIMARY KEY (`row_no`), UNIQUE KEY `row_no` (`row_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 主キーに掲示板IDを追加する為に、no 列を row_no の後に追加 alter table `board` add `no` VARCHAR(20) after `row_no`; -- 主キーの削除 alter table board drop primary key; show create table board; CREATE TABLE `board` ( `row_no` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `no` varchar(20) DEFAULT NULL, `body` varchar(1000) DEFAULT NULL, `subject` varchar(200) DEFAULT NULL, `from` varchar(20) DEFAULT NULL, `pdate` datetime DEFAULT NULL, `cdate` datetime DEFAULT NULL, UNIQUE KEY `row_no` (`row_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --※ すでにデータがある場合( no にセットするのは適当な文字列 ) update board set no = '0'; -- 主キー作成 alter table board add primary key(`row_no`,`no`); show create table board; CREATE TABLE `board` ( `row_no` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `no` varchar(20) NOT NULL DEFAULT '', `body` varchar(1000) DEFAULT NULL, `subject` varchar(200) DEFAULT NULL, `from` varchar(20) DEFAULT NULL, `pdate` datetime DEFAULT NULL, `cdate` datetime DEFAULT NULL, PRIMARY KEY (`row_no`,`no`), UNIQUE KEY `row_no` (`row_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- インデックスの確認 show index from board; -- インデックスの削除( ここは内容確認の為 ) alter table board drop index row_no; CREATE TABLE `board` ( `row_no` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `no` varchar(20) NOT NULL DEFAULT '', `body` varchar(1000) DEFAULT NULL, `subject` varchar(200) DEFAULT NULL, `from` varchar(20) DEFAULT NULL, `pdate` datetime DEFAULT NULL, `cdate` datetime DEFAULT NULL, PRIMARY KEY (`row_no`,`no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 -- 保存していたデータの復帰 insert into board select * from board2;
タグ:SQL