第８章 変更（補足）
1.表の変更
作成済みの表に対し、さまざまな変更を行うことができる。
表の変更には、ALTER TABLE
を使用する。
（ALTER TABLEシステム権限が必要）
■新しく列を追加する
《構文》
ALTER TABLE 表名
ADD ( 列名 列のデータ型 [列制約構文] [,列名 列のデータ型 [列制約構文]]);
|
例）、emp表にmail_address列を追加する
ALTER TABLE emp
ADD (mail_address VARCHAR2(50));
|
■列を削除する（Oracle8iから）
《構文》
ALTER TABLE 表名
DROP COLUMN 列名 ;
|
※削除する対象の列にNULL値以外のデータが入っていてはいけない!
例）、emp表のmail_address列を削除する
ALTER TABLE emp
DROP COLUMN mail_address;
|
■表名の変更
《構文》
例）、emp表をemployee表に改名する
※表名の変更はALTER TABLEではなく、RENAME TOで行う!
※列名の変更はできない。
（ただし、Oracle8iから列の削除ができるようになったため、列を削除してから別名で列を作成する）
※RENAME TOコマンドは、表名にスキーマ名をつけることはできない。よって、このコマンドを実行するには、かならずその表の所有者でなければならない。
2.列の変更
■列の定義を変更する
《構文》
ALTER TABLE 表名
MODIFY ( 列名 列のデータ型 [NOT NULL] [,列名 列のデータ型 [NOT NULL]]);
|
《変更できるもの》
- 1.列のサイズ
- 作成時に指定したデータのサイズを変更する。変更する列に１件でもデータが格納されている場合、「精度（桁数）」「位取り（小数点以下の桁数）」「文字幅」を大きくすることはできるが、小さくすることはできない。
- 2.NOT NULL制約
- NOT NULL制約が定義されていない列に対し、NOT NULL制約を追加できる。ただし、NOT NULL制約をかける列にNULL値があったら、制約を追加することはできない。
また、他の制約はこの構文では変更できない（８章３を参照）。
- 3.DEFAULT値
- DEFAULT値を追加、変更できる。変更後から追加されるデータに対して適応される。
例）、dept表のdname列を長さ20に変更する
ALTER TABLE dept
MODITY (dname VARCHAR2(20));
|
例）、dept表のdname列にNOT NULL制約を追加する
ALTER TABLE dept
MODITY (dname [CONSTRAINT 制約名] NOT NULL);
|
例）、emp表のsal列にDEFAULT値として０を設定する
ALTER TABLE emp
MODITY (sal DEFAULT 0);
|
3.制約の変更
■制約を追加（削除）する
《構文（追加）》
ALTER TABLE 表名
ADD CONSTRAINT 制約名 制約 ;
|
《構文（削除）》
ALTER TABLE 表名
DROP CONSTRAINT 制約名;
|
- 制約を追加する列のデータが、追加する制約を満たしていなければ制約を追加することはできない。
- 制約を削除するには、あらかじめ制約名を調べておく必要がある。
NOT NULL制約はこの構文では追加できない。ただし削除はこの構文で行う。
例）、dept表のdname列にUNIQUE整合性制約を追加する
ALTER TABLE dept
ADD CONSTRAINT unuque_dept_dname UNIQUE(dname);
|
■制約を無効／有効にする
制約は、追加・削除だけでなく、一時的に無効／有効にすることができる。一度、無効にした制約は、有効にするコマンドを実行するまで無効のままである。
この機能を利用し、制約を一時的に削除し、作り直すという手間が省ける。
《構文（無効にする）》
ALTER TABLE 表名
DISABLE CONSTRAINT 制約名 ;
|
《構文（有効にする）》
ALTER TABLE 表名
ENABLE CONSTRAINT 制約名 ;
|
- 追加した制約は有効になっている。
- 無効にした制約は、データ・ディクショナリには残ったままである。
（STATUS列を調べることにより、有効／無効が分かる）
- 無効にした制約を有効にする際、制約に違反するデータがあれば、制約は有効にならない。
4.既存表から新しい表を作成
既にある表を元に、別の表を作成することができる。
（Accessのテーブル作成クエリー）
■構文
CREATE TABLE 表名 AS
SELECT 列名 [,列名 ,・・・] FROM 元表名
WHERE 条件;
|
- AS以降に記述されたSELECT文で検索されるデータを元に、新しい表が作成される。
- 列の名前と行データ、NOT NULL制約のみがコピーされ、DEFAULT指定や他の制約はコピーされない。
- DEFAULT TABLESPACEの指定を省略した場合、コマンドを実行したユーザのデフォルト表領域に作成される。
例）、emp表からdeptnoが10であるデータをすべて選択し、emp10テーブルを作成する
CREATE TABLE emp10 AS
SELECT * FROM emp
WHERE deptno = 10;
|
まとめ・補足
■変更のガイドライン
- 1.列の幅（精度、位取り、文字幅）を小さくする
- 変更対象となる列のすべての行にNULLのみが含まれているか、または、表にデータが１件も含まれていない場合に可能。
- 2.データ型の変更
- 変更対象となる列のすべての行にNULLのみが含まれているか、または、表にデータが１件も含まれていない場合に可能。
ただし、CHARからVARCHAR2もしくはVARCHAR2からCHARへの変更は、NULL値以外が含まれていても、サイズを変更しなければ可能。
- 3.デフォルト値の変更
- 常に可能。変更後に挿入されるデータのみが影響を受ける。
■DROP UNUSED COLUMNSオプション
Oracle8iからの機能。表の列に対して未使用マークを付けた列をまとめて削除する。未使用マークの付いた列のうち、一部の列のみを削除することはできない。
■DROP COLUMN
Oracle8iからの機能。列の削除を行う。（Oracle8までは列の削除はできなかったため、表を削除して作り直す、またはCREATE TABLE 〜 AS SELECT 〜で不要の列を除いたテーブルを作成し、前の表を削除後、改名していた）。
ただし、削除対象となる列のすべての行にNULLのみが含まれているか、または、表にデータが１件も含まれていない場合に可能。
練習問題
問題１
NOT NULL制約を追加するコマンドを答えよ。
問題２
ALTER TABLEコマンドでできることの説明のうち、正しいものはどれか答えよ。
- ALTER TABLE emp RENAME TO employee;
このSQL文で、emp表をemployee表に改名できる。
- ALTER TABLE emp MODITY (ename VARCHAR2(30));
このSQL文で、ename列の幅を小さくすることは常に可能。
- ALTER TABLE emp ADD (new_col NUMBER);
このSQLで、新しいnew_col列を追加できる。
- ALTER TABLE emp DROP UNUSED COLUMNS;
このSQLで、未使用マークの付いた列のうち、特定の列だけを削除できる。
- ALTER TABLE emp DROP COLUMN ename;
このSQLで、ename列を削除することはできない。
問題３
下記テーブルに対して、表定義の変更を行う。
EMP表の定義
| 列名 | NULL? | 型 |
|---|
| EMPNO | NOT NULL | NUMBER(4) |
| ENAME |
| VARCHAR2(10) |
| JOB |
| VARCHAR2(9) |
| MGR |
| NUMBER(4) |
| HIREDATE |
| DATE |
| SAL |
| NUMBER(7,2) |
| COMM |
| NUMBER(7,2) |
| DEPTNO | NOT NULL | NUMBER(2) |
EMP表のデータ
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
|---|
| 7698 | BLACK | MANAGER | 7839 | 1981-05-01 | 2850 |
| 30 |
| 7782 | CLEAK | MANAGER | 7839 | 1981-06-09 | 2450 |
| 10 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 |
|
| 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 |
| 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 |
| 30 |
以下のＳＱＬを実行した際、エラーになる文はどれか答えよ。また、理由を説明せよ。
- ALTER TABLE emp MODITY (sal NUMBER(9));
- ALTER TABLE emp MODITY (sal NUMBER(10));
- ALTER TABLE emp MODITY (sal NUMBER(8,1));
- ALTER TABLE emp MODITY (sal NUMBER(8,2) NOT NULL);
- ALTER TABLE emp MODITY (sal NUMBER(8,2) DEFAULT 600 NOT NULL);
- ALTER TABLE emp MODITY (comm NUMBER(4));
- ALTER TABLE emp MODITY (comm CHAR(6));
- ALTER TABLE emp MODITY (project VARCHAR2(30) NOT NULL);
- ALTER TABLE emp MODITY (project VARCHAR2(30) DEFAULT 'NONE' NOT NULL);
問題４
制約について、正しくないものを選べ。
- ON DELETE CASCADEオプションを設定しても、子表から参照される親表内の行は削除できない。
- 制約のON/OFFは切り替えられる。
- NOT NULL制約は、ADD CONSTRAINT構文によって、表作成後も設定できる。