SQL Server - テーブルの作成

提供：MochiuWiki : SUSE, EC, PCB
ナビゲーションに移動 検索に移動

概要



カラムの追加・削除

既存のテーブルの定義(テーブルの構造)を変更するには、ALTER TABLE文を使用する。

ALTER TABLE文を使用すれば、テーブル名やカラム名の変更および定義を変更することができる。

以下のようなマスタテーブルを定義および作成する。

 CREATE TABLE T_Sample
 (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    password VARCHAR(20) NOT NULL,
 );


マスタテーブル
カラム名 データ型 主キー
id INT ○
name VARCHAR(50)
password VARCHAR(50)


カラムの追加

テーブルにカラムを追加する場合、ALTER TABLE <テーブル名> ADD文を使用する。
ALTER TABLE <テーブル名> ADD文では、DBMSによっては、COLUMNの記述を省略することができる。

※注意
SQL Serverでは、COLUMNを記述することができない。

 ALTER TABLE <テーブル名> ADD [COLUMN] <追加するカラム名> <追加するカラムのデータ型> <追加するカラムの制約>
 -- または
 ALTER TABLE <テーブル名> ADD <追加するカラム名> <追加するカラムのデータ型> <追加するカラムの制約>


以下の例では、マスタテーブルにカラムを1つ追加している。
追加するカラム名をtel、データ型はVARCHAR(20)、カラムの制約には値が重複しないようにUNIQUE制約を指定している。

 ALTER TABLE T_Sample ADD [COLUMN] tel VARCHAR(20) UNIQUE


カラムの削除

カラムを削除する場合、ALTER TABLE <テーブル名> DROP文を使用する。
ALTER TABLE <テーブル名> DROP文では、DBMSによっては、COLUMNの記述を省略することができる。

※注意
SQL Serverでは、COLUMNを記述することができない。

 ALTER TABLE <テーブル名> DROP [COLUMN] <削除するカラム名>


カラムの追加の補足

既存のテーブルにおいて、ALTER TABLE文で追加したカラム列は、最も最後になるのが原則である。
DBMSによっては、カラムを追加する時に任意の位置を指定できるものもある。

カラムを追加する位置を指定する場合は、AFTERキーワードを使用して、追加する位置の前にあるカラムを指定する。

 ALTER TABLE <テーブル名> ADD <追加するカラム名> <データ型> <カラムの制約> AFTER <追加する位置の前にあるカラム名>


例えば、マスタテーブルT_Sampleにカラムtelを追加する時、カラムnameとカラムpasswordの間に追加する場合は、以下のように記述する。

 ALTER TABLE T_Sample ADD tel VARCHAR(20) UNIQUE AFTER name



自動インクリメント

自動インクリメントとは、指定したカラムにデータが追加される時、データベースが一意の値を自動的に付与する機能のことである。
カラムに挿入したデータに、連番を自動で付加する場合に便利である。

自動インクリメントの設定

自動インクリメントは、整数か浮動小数点のデータ型のカラムのみ設定できる。
また、主キーまたはユニークキーのカラムのみ指定できる。

※注意
自動インクリメントと主キーは、テーブルの1つのカラムにのみ指定できる。
一般的には、自動インクリメントと主キーを併用して、連番を付ける。

自動インクリメントを設定するには、以下のように記述する。

 CREATE TABLE <テーブル名>
 (
    -- ...略
    <カラム名> <データ型> AUTO_INCREMENT
    -- ...略
 );


以下の例では、T_Sampleテーブルを作成して、idカラムに自動インクリメントを設定している。

 CREATE TABLE T_Sample
 (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(10),
    PRIMARY KEY (id)
 );
 -- または
 create table T_Sample
 (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10) NOT NULL
 );


次に、レコードを1つ追加する。

 INSERT INTO T_Sample (name) VALUE ('山田太郎');


SELECT文でテーブルを確認すると、カラムidに値を指定しなくても自動で"1"が追加される。

+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | 山田太郎       　  |
+----+--------------------+


自動インクリメントの初期値を設定する =

自動インクリメントは、基本的に"1"から連番が付けられる。
しかし、1以外の値から連番を付けたい場合もある。

その時、自動インクリメントの値を指定するには、以下のように記述する。

 CREATE TABLE <テーブル名>
 (
    <カラム名> <データ型> PRIMARY KEY AUTO_INCREMENT
 ) AUTO_INCREMENT = <値>;
<br>
==== 自動インクリメントの値を変更する ====
自動インクリメントの値を変更するには、以下のように記述する。<br>
 <syntaxhighlight lang="sql">
 ALTER TABLE <テーブル名> AUTO_INCREMENT = <値>;


以下の例では、テーブルT_Sampleのカラムidの自動インクリメントの初期値を50に変更している。

 ALTER TABLE T_Sample AUTO_INCREMENT = 50;


SELECT文でレコードを確認すると、以下のように表示される。

+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | 山田太郎        　 |
| 50 | 鈴木一郎           |
+----+--------------------+


自動インクリメントのカラムを変更する

例えば、任意のカラムに自動インクリメントを設定している時、別カラムに自動インクリメントを再設定したい場合がある。
この時、自動インクリメントを行うカラムを変更する手順を、以下に記載する。

まず、以下のようなテーブルを作成する。

 CREATE TABLE T_Sample
 (
    id INT NOT NULL AUTO_INCREMENT,
    new_id INT NOT NULL,
    name VARCHAR(10) NOT NULL,
    PRIMARY KEY (id)
 );


次に、CHANGEコマンドまたはMODIFY COLUMNを使用して、カラムidの自動インクリメントおよび主キーを解除する。

 -- 自動インクリメントの解除
 ALTER TABLE T_Sample CHANGE id id INT NOT NULL;
 
 -- 主キーの解除
 ALTER TABLE T_Sample DROP PRIMARY KEY;


最後に、カラムnew_idに主キーおよび自動インクリメントを設定する。

 -- 主キーの登録
 ALTER TABLE T_Sample ADD PRIMARY KEY (new_id);
 
 -- 自動インクリメントの登録
 ALTER TABLE T_Sample CHANGE new_id new_id INT AUTO_INCREMENT;
 -- または
 ALTER TABLE T_Sample MODIFY COLUMN new_id INT AUTO_INCREMENT NOT NULL;


テーブルのカラムを確認すると、以下のように表示される。

 SHOW COLUMNS FROM T_Sample;



+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int         | NO   |     | NULL    |                |
| new_id | int         | NO   | PRI | NULL    | auto_increment |
| name   | varchar(10) | NO   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+


自動インクリメントを追加する

自動インクリメントを新規登録する場合、上記の変更と同様、主キーと自動インクリメントを登録する。

 -- 主キーの登録
 ALTER TABLE <テーブル名> ADD PRIMARY KEY (<カラム名>);
 
 -- 自動インクリメントの登録
 ALTER TABLE <テーブル名> CHANGE <カラム名> <カラム名> <データ型> AUTO_INCREMENT;
 -- または
 ALTER TABLE <テーブル名> MODIFY COLUMN <カラム名> <データ型> AUTO_INCREMENT NOT NULL;


自動インクリメントを削除する

自動インクリメントを削除する場合、上記の変更方法と同様、主キーと自動インクリメントを解除する。

 -- 自動インクリメントの解除
 ALTER TABLE <テーブル名> CHANGE <カラム名> <カラム名> <データ型> NOT NULL;
 -- または
 ALTER TABLE <テーブル名> MODIFY COLUMN <カラム名> <データ型> NOT NULL;
 
 -- 主キーの解除
 ALTER TABLE <テーブル名> DROP PRIMARY KEY;



「http://mochiuwiki.e2.valueserver.jp/index.php?title=SQL_Server_-_テーブルの作成&oldid=12041」から取得