CREATE文をさらに使いこなそう：SQL実践講座（11）

[篠原光太郎，＠IT]

　今回は、前回（第10回：CREATE文でテーブルを作成する）に引き続きCREATE文の詳細と、定義した内容を変更する「ALTER」文を使ってテーブル定義の変更を実施してみましょう。

　前回、「CardInfo」という名前の新しいテーブルを作成した後、定義した内容を「SQL Server Enterprise Manager」で確認しました。このとき、列名（カラム名）、データ型、データ長をカラムごとに指定してテーブルの作成を行いました。今回はこの画面を使用しながら、テーブル定義のほかのパラメータを見ていきましょう。

画面1　前回作成したテーブルCardInfoをSQL Server Enterprise Managerで確認したところ（画面をクリックすると拡大表示します）

NULLを許容

　まず、「NULLを許容」です。NULLの扱いについては第8回（サブクエリーの応用「相関サブクエリー」）で解説しましたが、NULLはデータではない特殊な値である、という点が特徴でしたね。RDBMSでは、カラムごとにあらかじめNULLを保存可能か否か指定することができます。デフォルトでは、「NULLを許容」する設定になっていることが、この画面から分かりますね。NULL値を許容しない設定にするには、この画面でチェックを外せばOKです。変更を反映させるには、画面左上のディスクアイコンをクリックし、変更を実施します。

【例1】

　CardInfoテーブルでは、データの登録時に「CardID」と「CustomerID」は必須ですので、「NULLを許容」フラグを「OFF」にします

　前回使用したCREATE文で、NULLを許容するか否かを指定するためには、各カラムの定義の後ろに「NOT NULL」キーワードを指定します。例1をCREATE文で記述すると、次のようになります。

【例2】

CREATE TABLE CardInfo (
  CardID nchar(6) NOT NULL,
  CustomerID nchar(5) NOT NULL,
  IssueDate datetime,
  ExpireDate datetime,
  EmployeeID int
)

規定値

　次は規定値です。規定値は、データ登録時に値が指定されなかった場合に、デフォルトの値としてセットされる値です。

【例3】

　CardInfoテーブルでは、発行日は「1753年1月1日」、更新期限は「2999年12月31日」をデフォルトとして登録するため、「規定値」欄にそれぞれの日付を入力します。画面上には（　）が表示されていますが、入力の必要はありません

画面2　規定値に1753年1月1日と2999年12月31日を入力する（画面をクリックすると拡大表示します）

　CREATE文で規定値をセットするためには、各カラム定義の後に「DEFAULT」キーワードに続いて規定値を記述します。例3をCREATE文で記述すると、次のとおりになります。

【例4】

CREATE TABLE CardInfo (
  CardID nchar(6) NOT NULL,
  CustomerID nchar(5) NOT NULL,
  IssueDate datetime DEFAULT ‘1753/1/1’,
  ExpireDate datetime DEFAULT ‘2999/12/31’,
  EmployeeID int
)

IDENTITY

　次は「IDENTITY」です。これは、データを登録するときに自動的に番号を生成し、保存する機能を有効にするための設定です。伝票番号など、1つずつ番号が増分されていくデータを持つ必要がある場合には、IDENTITYを指定してSQL Serverに自動的にセットさせることができます。

【例5】

（1）CardInfoテーブルに、発行IDを管理する「IssueID」というカラムを新たに追加し、データ型を「int」「NULLを許容」チェックボックスをOFFにします

（2）IDENTITY列はNULLを許容しないので、「NULLを許容」をONにしたままでは、「IDENTITY」チェックボックスをONにはできません。IDENTITYチェックボックスをONにして、右にある「IDENTITYシード」欄に「1000000000」を入力します。この値が初期値になります

（3）「IDENTITYインクリメント」欄に「1」を入力します。これらの設定により、1000000000から、データが登録されるたびに1つずつカウントアップし、カラムに保存される列を作成できます

画面3　IDENTITYにIssueIDを追加し、初期値をIDENTITYシード欄に入力する。次に増加させたい値をIDENTITYインクリメント欄に入力する（画面をクリックすると拡大表示します）

　CREATE文でIDENTITY列の設定をするためには、次の例6のように、各カラム定義の後に「IDENTITY」キーワードに続いて初期値と増分を指定します。

【例6】

CREATE TABLE CardInfo (
  CardID nchar(6) NOT NULL,
  CustomerID nchar(5) NOT NULL,
  IssueDate datetime DEFAULT '1753/1/1',
  ExpireDate datetime DEFAULT '2999/12/31',
  EmployeeID int,
  IssueID int IDENTITY (1000000000, 1) NOT NULL 
)

SQL文の生成

　前回、GUI上から変更したCardInfoテーブルの定義を、テキストファイルにCREATE文として出力してみましょう。

（1）CardInfoのデザイン画面のウィンドウタイトル下にあるアイコンの一番右をクリックします

（2）「SQLスクリプト生成」画面が表示されるので、右下の「スクリプト化されるオブジェクト」一覧に「CardInfo」テーブルが表示されていることを確認し、「OK」ボタンをクリックします

画面4　スクリプト化されるオブジェクトの一覧にCardInfoテーブルが表示されているのを確認してOKボタンをクリックする（画面をクリックすると拡大表示します）

（3）ファイル保存ダイアログボックスが表示されるので、ファイル名を入力し保存します

　保存したファイルをテキストエディタで開くと、次のSQL文が保存されたことが確認できます。

【リスト2】

if exists (select * from sysobjects where id =
object_id(N'[dbo].[CardInfo]') and
  OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[CardInfo]
GO
CREATE TABLE [dbo].[CardInfo] (
  [CardID] [nchar] (6) NOT NULL ,
  [CustomerID] [nchar] (5) NOT NULL ,
  [IssueDate] [datetime] NULL ,
  [ExpireDate] [datetime] NULL ,
  [EmployeeID] [int] NULL ,
  [IssueID] [int] IDENTITY (1000000000, 1) NOT NULL 
  ) ON [PRIMARY]
GO

　 CREATE文の前の「if exist ?」の部分は、「もしCardInfoテーブルが存在していたら、データベース上から削除する」という意味のSQL文です。ここでは詳しく説明しませんが、3行目に出てくる「DROP」文だけは覚えておきましょう。

　DROP文は、CREATE文で作成した各種のオブジェクトを、データベース上から削除します。データの削除ではなく、オブジェクト自体を削除します。

　テーブルを削除する場合は、上記のSQL文にもあるとおり、

　DROP TABLE テーブル名

とします。保存されたSQLでは、データベースのオーナー（dbo）の指定がされていますが、これはオプションです。

定義の変更

　SQL Server Enterprise Managerからテーブルの定義を変更しましたが、SQL文で定義の変更を行う場合はどうすればよいでしょうか？ ここで登場するのがALTER文です。

　ALTER文は、CREATE文で作成された各種のオブジェクトの定義情報を変更します。例えば、テーブルのカラム追加、各種制約の追加などが可能です。次の例は、CardInfoテーブルに「Status」というカラムを追加しています。

【例7】

ALTER TABLE CardInfo
  ADD Status nchar(6)

　既存のカラムを削除したい場合は、DROP文を使用します。次の例は、CardInfoテーブルからStatusフィールドを削除します。

【例8】

ALTER TABLE CardInfo
  DROP Column Status

今回のまとめ

今回は、CREATE文の詳細とDROP文、ALTER文について解説しました。次回は、テーブルへのデータ登録を行う「INSERT」文について解説します。

「次回」へ

「SQL実践講座」バックナンバー

• SQL Serverで「デッドロック」を回避する
• トランザクションの一貫性を保証するロック
• トランザクションを用いて注文登録をする
• トランザクションでデータの不整合を防ぐ
• テーブルで複数の処理を実行させるトリガー
• ユーザー定義関数を作成するストアドファンクション
• ストアドプロシージャによる繰り返し処理
• 条件分岐のあるストアドプロシージャ
• ストアドプロシージャの作成
• システム・ストアドプロシージャを用いたロールの詳細設定
• ロールを利用したグループ単位での権限設定
• SQL Serverのオブジェクトに権限を設定する
• Enterprise Managerによるビューの作成
• 作成したSELECT文をDBに登録する「ビュー」
• データの更新と主キーの重要性
• テーブル中のデータ識別に必要な主キーを定義する
• データの登録を行うINSERT文
• CREATE文をさらに使いこなそう
• CREATE文でテーブルを作成する
• SELECT文を統合する「UNION」
• サブクエリーの応用「相関サブクエリー」
• SELECT文の結果を抽出条件に使う
• テーブル結合のバリエーションを増やす
• テーブル結合の仕組みを理解する
• 異なるテーブル同士を結合する「JOIN」句
• 集計を行う「GROUP BY」句
• SELECT文で並べ替えを行うには？
• SQLの基礎 「SELECT」文を覚えよう