備忘録的プログラミングリファレンス

テーブル内容の変更

　テーブル内容の変更には ALTER TABLE コマンドを使います。テーブル自体の変更やテーブルに属するカラムの変更も ALTER TABLE コマンドを使います。

ALTER コマンド
ALTER TABLE table_name
	ALTER COLUMN column_name TYPE column_type;

　ALTER は変更を意味するコマンドです。
　上記の例では、ALTER TABLEはテーブルの内容を変更することを示しています。次のALTER COLUMN .. TYPE ..はテーブルに属するカラムのデータタイプを変更することを示しています。

　ALTER TABLE によって変更できるものには、テーブル自体の名称の変更とカラムごとの名称やデータタイプの変更やカラムの削除や追加、制約の追加や削除などがあります。

　テーブルの詳細や制約については以下のコマンドで確認することができます。

MySQL
SHOW CREATE TABLE table_name;
PostgreSQL
> \d table_name

テーブル自体の変更

テーブル名称を変更

　テーブルの名称を変更するには以下のようにします。

MySQL
ALTER TABLE tbl_name
    RENAME [TO | AS] new_tbl_name
PostgreSQL
ALTER TABLE name
    RENAME TO new_nam;

　他にも変更できるテーブルの要素があるのですがその方法はデータベースによって異なるため、ここでは名称だけ取り上げます。その他については各データベースの仕様を参照してください。

　以下はテーブルの名称を変更する簡単な作成例です。

TABLE の RENAME
ALTER TABLE name
    RENAME TO new_name;

　テーブル名 name を new_name に変更します。

カラムの変更

　テーブルのカラムの変更、追加、削除は ALTER TABLE table_name の後に ALTER、ADD、DROP といったコマンドを続けます。

カラム名を変更

MySQL
ALTER TABLE tbl_name
	RENAME COLUMN old_col_name TO new_col_name;
PostgreSQL
ALTER TABLE [ ONLY ] name [ * ]
	RENAME [ COLUMN ] column TO new_column;

　以下はカラム名を変更する簡単な例です。

カラム名の変更
ALTER TABLE table_name
    RENAME COLUMN old_col_name TO new_col_name;

　カラム名 old_col_name を new_col_name に変更します。

データタイプを変更

MySQL
ALTER TABLE tbl_name
	MODIFY [COLUMN] col_name column_definition
		[FIRST | AFTER col_name]

　PostgreSQL ではカラムのデータタイプを変更できません。一旦該当するカラムを削除の後に改めてカラムを追加する方法があります。 その際にデータが消滅しますので十分に注意してください。

　以下はカラムのデータタイプを変更する簡単な例です。

カラムのデータイプの変更
ALTER TABLE table_name
    MODIFY col_name text;

　カラム col_name を text 型に変更します。

　カラムのデータタイプの変更について詳しくは列のデータタイプを変更　ALTER TABLE、ALTER COLUMN、TYPEを参照してください。

カラムの追加

MySQL
ALTER TABLE tbl_name
	ADD [COLUMN] (col_name column_definition,...)

または

ALTER TABLE tbl_name
	ADD [COLUMN] col_name column_definition
	[FIRST | AFTER col_name]
PostgreSQL
ALTER TABLE [ ONLY ] name [ * ]
    ADD [ COLUMN ] column type [ column_constraint [ ... ] ]

　以下はカラムのデータタイプを変更する簡単な例です。

カラムの変更
ALTER TABLE table_name
    MODIFY col_name VARCHAR(20);

　カラム col_name を VARCHAR(20) で追加します。

　カラムの追加について詳しくはテーブルに列を追加　ALTER TABLE、ADD COLUMNを参照してください。

カラムを削除

MySQL
ALTER TABLE tbl_name
	DROP [COLUMN] col_name
PostgreSQL
ALTER TABLE [ ONLY ] name [ * ]
    DROP [ COLUMN ] column [ RESTRICT | CASCADE ]

　以下はカラムを削除する簡単な例です。

カラムの削除
ALTER TABLE table_name
    DROP col_name;

　カラム col_name を削除します。

　カラムの削除について詳しくはテーブルから列を削除　ALTER TABLE、DROP COLUMNを参照してください。

デフォルトの設定を変更

MySQL
ALTER TABLE tbl_name
	ALTER [COLUMN] col_name {
		SET DEFAULT {literal | (expr)}
		| SET {VISIBLE | INVISIBLE}
		| DROP DEFAULT
	}
PostgreSQL
ALTER TABLE [ ONLY ] name [ * ]
    ALTER [ COLUMN ] column { SET DEFAULT expression | DROP DEFAULT }

　以下は DEFAULT の設定を変更する簡単な例です。

DEFAULT の設定
ALTER TABLE table_name
    ALTER col_name SET DEFAULT NULL;

　カラム col_name にデフォルト値として NULL を設定します。

DEFAULT の削除
ALTER TABLE table_name
    ALTER col_name DROP DEFAULT;

　カラム col_name のデフォルト設定を削除します。

NOT NULL 制約を変更

　MySQL で NOT NULL 制約を設定したり削除するにはカラムの定義を NOT NULL の有無によって定義し直します。
　NOT NULL とは NULL、何も値がないことを拒否します。

MySQL
ALTER TABLE tbl_name
	MODIFY [COLUMN] col_name column_definition

　例えば NOT NULL 制約を設定するには以下のように NOT NULL が有る状態で定義し直します。

ALTER TABLE example_table
	MODIFY COLUMN exam_column text NOT NULL;

　NOT NULL 制約を削除するには以下のように NOT NULL が無い状態で定義し直します。

ALTER TABLE example_table
	MODIFY COLUMN exam_column text;
PostgreSQL
ALTER TABLE [ ONLY ] name [ * ]
    ALTER [ COLUMN ] column { SET | DROP } NOT NULL

　以下は PostgreSQL での簡単な例です。

NOT NULL の設定
ALTER TABLE table_name
    ALTER col_name SET NOT NULL;

　カラム col_name に NOT NULL を設定します。

NOT NULL の削除
ALTER TABLE table_name
    ALTER col_name DROP NOT NULL;

　カラム col_name の NOT NULL 設定を削除します。

PRIMARY KEY 制約の追加・削除

PRIMARY KEY 制約の追加

MySQL
ALTER TABLE tbl_name
	ADD [CONSTRAINT [symbol]] PRIMARY KEY
		[index_type] (key_part,...)
		[index_option] ...
PostgreSQL
ALTER TABLE [ ONLY ] name [ * ]
	ADD [ CONSTRAINT constraint_name ] PRIMARY KEY ( column_name [, ... ] )
	[ INCLUDE ( column_name [, ... ] ) ][ WITH ( storage_parameter [= value] [, ... ] ) ][ USING INDEX TABLESPACE tablespace_name ]
	[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
	[ NOT VALID ]

　以下は PRIMARY KEY 制約と追加する簡単な例です。

PRIMARY KEY 制約の追加
ALTER TABLE table_name
    ADD PRIMARY KEY ( column_name );

　PRIMARY KEY 制約を追加したいカラム名（ column_name ）を指定します。PRIMARY KEY は複数のカラムに設定することができません。

PRIMARY KEY 制約の削除

MySQL
ALTER TABLE tbl_name
	 DROP PRIMARY KEY
PostgreSQL
ALTER TABLE [ ONLY ] name [ * ]
	DROP PRIMARY KEY
または
ALTER TABLE [ ONLY ] name [ * ]
	DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]

　PRIMARY KEY の変更はできません。一度削除して追加する必要があります。

　以下は PRIMARY KEY 制約と削除する簡単な例です。

PRIMARY KEY の削除
ALTER TABLE table_name
    DROP PRIMARY KEY;

　テーブル table_name の DROP PRIMARY KEY を削除します。

FOREIGN KEY 制約の追加・削除

FOREIGN KEY 制約の追加

MySQL
ALTER TABLE tbl_name
	ADD [CONSTRAINT [symbol]] FOREIGN KEY
		[index_name] (col_name,...) REFERENCES tbl_name (col_name,...)
		[ON DELETE reference_option]
		[ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
PostgreSQL
ALTER TABLE [ ONLY ] name [ * ]
	ADD [CONSTRAINT constraint_name] FOREIGN KEY (column) REFERENCES ref_table_name (ref_column)
	[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
	[ NOT VALID ]

　以下は FOREIGN KEY を追加する簡単な例です。

FOREIGN KEY の追加
ALTER TABLE table_name
    ADD FOREIGN KEY ( col_name ) REFERENCES ref_table_name (ref_column);

　カラム col_name に ref_table_name テーブルの ref_column カラムとの外部キーを設定します。

FOREIGN KEY 制約の削除

MySQL
ALTER TABLE tbl_name
	 DROP FOREIGN KEY fk_symbol
PostgreSQL
ALTER TABLE [ ONLY ] name [ * ]
	DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]

　FOREIGN KEY の変更はできません。一度削除して追加する必要があります。

　PostgreSQL で FOREIGN KEY を削除するには、FOREIGN KEY を作成したときに付けられた constraint_name を指定します。

　以下は FOREIGN KEY を削除する簡単な例です。

FOREIGN KEY の削除
ALTER TABLE table_name
    DROP FOREIGN KEY fk_symbol;

　FOREIGN KEY fk_symbol を削除します。fk_symbol は CONSTRAINT 名または制約名です。

　FOREIGN KEY 制約の CONSTRAINT 名または制約名は、MySQL ではSHOW CREATE TABLE table_name;で、PostgreSQL では\d table_nameで確認できます。

UNIQUE 制約の追加・削除

　UNIQUE 制約は記録するカラムデータに同じものがないようにするための制約です。
　複数のカラムを組み合わせることができます。

UNIQUE 制約の追加

MySQL
ALTER TABLE tbl_name
	ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
	[index_name] [index_type] (key_part,...)
	[index_option] ..
PostgreSQL
ALTER TABLE [ ONLY ] name [ * ]
	ADD UNIQUE USING INDEX index_name [ NOT VALID ]

　以下は UNIQUE 制約の追加の簡単な例です。

UNIQUE 制約の追加
ALTER TABLE table_name
    ADD UNIQUE ( col_name );

　カラム col_name に UNIQUE 制約を設定します。
　複数のカラムの組み合わせに同じデータがないようにすることもできます。

複数の UNIQUE 制約の追加
ALTER TABLE table_name
    ADD UNIQUE ( col_name_a, col_name_b );

UNIQUE 制約の削除

MySQL
ALTER TABLE tbl_name
	 DROP {INDEX | KEY} index_name
PostgreSQL
ALTER TABLE [ ONLY ] name [ * ]
	DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]

　以下は UNIQUE 制約の削除の簡単な例です。

UNIQUE 制約の削除
ALTER TABLE table_name
    DROP uk_symbol;

　uk_symbol は UNIQUE 制約を表す CONSTRAINT 名または制約名です。

　UNIQUE 制約の CONSTRAINT 名または制約名は、MySQL ではSHOW CREATE TABLE table_name;で、PostgreSQL では\d table_nameで確認できます。

　UNIQUE KEY の変更はできません。一度削除して追加する必要があります。

CHECK 制約の追加・削除

CHECK 制約の追加

MySQL
ALTER TABLE tbl_name
	ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
PostgreSQL
ALTER TABLE [ ONLY ] name [ * ]
	ADD [ CONSTRAINT constraint_name ] CHECK ( expression ) [ NO INHERIT ] [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
	[ NOT VALID ]

　以下は CHECK 制約の追加の簡単な例です。

UNIQUE 制約の追加
ALTER TABLE table_name
    ADD CHECK ( col_name >= 100 );

　カラム col_name の値が 100 以上であるように制約を追加しています。

CHECK 制約の変更

MySQL
ALTER TABLE tbl_name
	ALTER CHECK symbol [NOT] ENFORCED

　MySQL では CHECK 制約を機能させないようにすることができるようです。
　PostgreSQL では変更はできません。一度削除して追加することはできます。

CHECK 制約の削除

MySQL
ALTER TABLE tbl_name
	 DROP CHECK symbol
PostgreSQL
ALTER TABLE [ ONLY ] name [ * ]
	DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]

　以下は CHECK 制約の削除の簡単な例です。

UNIQUE 制約の追加
ALTER TABLE table_name
    DROP CHECK chck_symbol;

　chck_symbol は CHECK 制約を表す CONSTRAINT 名または制約名です。

　UNIQUE 制約の CONSTRAINT 名または制約名は、MySQL ではSHOW CREATE TABLE table_name;で、PostgreSQL では\d table_nameで確認できます。