ALTER TABLE

これまでは、テーブルの構造を変更する場合、いったん削除して再度作成していた。
その場合、当然データも全て消えてしまう。
既存のデータを残したままテーブルの構造を変更するには、ALTER TABLEを使用する。

・テーブル名の変更

ALTER TABLE 現テーブル名 RENAME TO 新テーブル名;

・列名の変更

ALTER TABLE テーブル名 RENAME 現列名 TO 新列名;

・列の追加

ALTER TABLE テーブル名 ADD [COLUMN} 列の定義;

・列の削除

ALTER TABLE テーブル名 DROP [COLUMN] 列名;

・テーブル制約の追加

ALTER TABLE テーブル名 ADD {制約 | CONSTRAINT 制約名 制約};

・テーブル制約の削除

ALTER TABLE テーブル名 DROP CONSTRAINT 制約名;

・NOT NULL制約の設定・削除

ALTER TABLE テーブル名 ALTER [COLUMN] 列名 {SET | DROP} NOT NULL;

・デフォルト値の設定・削除

ALTER TABLE テーブル名 ALTER [COLUMN] 列名 {SET DEFAULT デフォルト値 | DROP DEFAULT};

・列の型の変更

ALTER TABLE テーブル名 ALTER [COLUMN] 列名 TYPE 新しい型;

  1. empテーブルを再度作成・行追加。

    oddtbs=> \i emp_create042.sql
...
oddtbs=> \i emp_insert011.sql
...
oddtbs=> select * from emp;
  code  |   name   | gender | age |   tel
--------+----------+--------+-----+---------
 564001 | 鈴木義男 |      1 |  58 | 11-1111
 564002 | 斎藤洋子 |      2 |  44 | 22-2222
 564003 | 山田美樹 |      2 |  33 | 33-3333
 564004 | 田中秀明 |      1 |  35 | 44-4444
 564005 | 佐藤研一 |      1 |  30 | 55-5555
 564006 | 相原佳子 |      2 |  28 | 66-6666
 564007 | 小林律子 |      2 |  33 | 77-7777
 564008 | 吉田隆士 |      1 |  24 | 88-8888
(8 rows)

  2. ALTER TABLEの実行。

    oddtbs=> alter table emp add email text not null;
ERROR:  column "email" contains null values
※ 新しいemail列はNOT NULL制約つきだが、既存行の値がNULLとなってしまうためエラーとなる。

oddtbs=> alter table emp add email text;
ALTER TABLE
※ とりあえずNOT NULLなしでemail列を追加。

oddtbs=> select * from emp;
  code  |   name   | gender | age |   tel   | email
--------+----------+--------+-----+---------+-------
 564001 | 鈴木義男 |      1 |  58 | 11-1111 |
 564002 | 斎藤洋子 |      2 |  44 | 22-2222 |
 564003 | 山田美樹 |      2 |  33 | 33-3333 |
 564004 | 田中秀明 |      1 |  35 | 44-4444 |
 564005 | 佐藤研一 |      1 |  30 | 55-5555 |
 564006 | 相原佳子 |      2 |  28 | 66-6666 |
 564007 | 小林律子 |      2 |  33 | 77-7777 |
 564008 | 吉田隆士 |      1 |  24 | 88-8888 |
(8 rows)
※ email列は全行NULL。

  3. email列に値を設定。

    次のＳＱＬをファイルとして作成。（emp_update041.sql）

    

    oddtbs=> \i emp_update041.sql
...
oddtbs=> select * from emp;
  code  |   name   | gender | age |   tel   |       email
--------+----------+--------+-----+---------+-------------------
 564001 | 鈴木義男 |      1 |  58 | 11-1111 | suzuki@aaa.com
 564002 | 斎藤洋子 |      2 |  44 | 22-2222 | saito@bbb.com
 564003 | 山田美樹 |      2 |  33 | 33-3333 | yamada@ccc.com
 564004 | 田中秀明 |      1 |  35 | 44-4444 | tanaka@ddd.com
 564005 | 佐藤研一 |      1 |  30 | 55-5555 | satoh@eee.com
 564006 | 相原佳子 |      2 |  28 | 66-6666 | aihara@fff.com
 564007 | 小林律子 |      2 |  33 | 77-7777 | kobayashi@ggg.com
 564008 | 吉田隆士 |      1 |  24 | 88-8888 | yoshida@hhh.com
(8 rows)
※ email列が全行設定された。

    

    4. 

  4. 

    ALTER TABLEによってemail列にNOT NULL制約を設定し、tel列を削除。
    

    oddtbs=> alter table emp alter email set not null;
ALTER TABLE
oddtbs=> alter table emp drop tel;
ALTER TABLE
oddtbs=> select * from emp;
  code  |   name   | gender | age |       email
--------+----------+--------+-----+-------------------
 564001 | 鈴木義男 |      1 |  58 | suzuki@aaa.com
 564002 | 斎藤洋子 |      2 |  44 | saito@bbb.com
 564003 | 山田美樹 |      2 |  33 | yamada@ccc.com
 564004 | 田中秀明 |      1 |  35 | tanaka@ddd.com
 564005 | 佐藤研一 |      1 |  30 | satoh@eee.com
 564006 | 相原佳子 |      2 |  28 | aihara@fff.com
 564007 | 小林律子 |      2 |  33 | kobayashi@ggg.com
 564008 | 吉田隆士 |      1 |  24 | yoshida@hhh.com
(8 rows)

oddtbs=> insert into emp values(564009, 'aaa', 1, 40, null);
ERROR:  null value in column "email" violates not-null constraint
※ 新たに設定したNOT NULL制約により拒否された。

oddtbs=> alter table emp drop email;
ALTER TABLE
oddtbs=> select * from emp;
  code  |   name   | gender | age
--------+----------+--------+-----
 564001 | 鈴木義男 |      1 |  58
 564002 | 斎藤洋子 |      2 |  44
 564003 | 山田美樹 |      2 |  33
 564004 | 田中秀明 |      1 |  35
 564005 | 佐藤研一 |      1 |  30
 564006 | 相原佳子 |      2 |  28
 564007 | 小林律子 |      2 |  33
 564008 | 吉田隆士 |      1 |  24
(8 rows)

    

    5. 

  5. 

    salary（給料）列を追加し、さらにage（年齢）と関係した前回同様の制約を追加する。
    

    oddtbs=> alter table emp add salary integer;
ALTER TABLE
oddtbs=> select * from emp;
  code  |   name   | gender | age | salary
--------+----------+--------+-----+--------
 564001 | 鈴木義男 |      1 |  58 |
 564002 | 斎藤洋子 |      2 |  44 |
 564003 | 山田美樹 |      2 |  33 |
 564004 | 田中秀明 |      1 |  35 |
 564005 | 佐藤研一 |      1 |  30 |
 564006 | 相原佳子 |      2 |  28 |
 564007 | 小林律子 |      2 |  33 |
 564008 | 吉田隆士 |      1 |  24 |
(8 rows)

oddtbs=> alter table emp add check(salary between age*10000 and age*20000);
ALTER TABLE
oddtbs=> insert into emp values(564009, '岡本太郎', 1, 40, 380000);
ERROR:  new row for relation "emp" violates check constraint "emp_check"
※ CHECK制約により拒否された。

oddtbs=> insert into emp values(564009, '岡本太郎', 1, 40, 420000);
INSERT 0 1
※ 今度はＯＫ。

oddtbs=> select * from emp;
  code  |   name   | gender | age | salary
--------+----------+--------+-----+--------
 564001 | 鈴木義男 |      1 |  58 |
 564002 | 斎藤洋子 |      2 |  44 |
 564003 | 山田美樹 |      2 |  33 |
 564004 | 田中秀明 |      1 |  35 |
 564005 | 佐藤研一 |      1 |  30 |
 564006 | 相原佳子 |      2 |  28 |
 564007 | 小林律子 |      2 |  33 |
 564008 | 吉田隆士 |      1 |  24 |
 564009 | 岡本太郎 |      1 |  40 | 420000
(9 rows)

    

    6.