[T-SQL] テーブルを作成、変更、削除する

handcraft 6/14/2009 46651 4.62 SQL Server

本記事では、テーブルの作成、修正、削除方法とプライマリ、ユニーク、外部キー制約など制約の設定方法のサンプルを掲載します。

動作確認は SQL Server 2005 Developer Edition で行っています。

1. テーブル定義

テーブルの作成、修正、削除と列の修正、削除方法のサンプルを記載します。

1.1 テーブルの作成,修正、削除

テーブルの作成はCREATE TABLE、　変更はALTER TABLE, 削除は DROP TABLE 文を使用します。テーブル情報の取得にはsp_helpストアドプロシージャが使用できます。

USE [SampleDB]
GO
-- 簡単なテーブルの定義例
CREATE TABLE dbo.Product1
(
	ProductID int NOT NULL,
    Name nvarchar(64) NOT NULL,
    Price money
)
GO

-- テーブルを削除する
DROP TABLE dbo.Product1

-- テーブル情報を取得する
exec sp_help 'dbo.Product1'

サロゲートキー(IDENTITY列)列を持つテーブルを作成する場合は次のように記述します。IDENTITY列に割り当てられている最大の値を取得するには、DBCC CHECKIDENT を使用します。

-- サロゲートキー
CREATE TABLE dbo.SampleUser
(
  UserId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
  Name nvarchar(65) NOT NULL
)
-- IDENTITY列に割り当てられた最大の値を取得するには
-- DBCC CHECKIDENTを使用します。
DBCC CHECKIDENT('dbo.SampleUser', NORESEED)

1.2 列定義の修正、削除

列定義の修正はALTER TABLE ALTER COLUMN 文を使用します。列を削除する場合は、ALTER TABLE DROP COLUMN 文を使用します。列の追加、修正も下記サンプルALTER TABLE 文を使用して行えます。

列を追加する場合で、すでにテーブルのレコードが存在する場合は、NULL許可列かDEFAULT句が定義されている列のみ追加を行えます。

-- テーブルの列定義の変更
ALTER TABLE dbo.Product1
ALTER COLUMN ProductID nvarchar(10) NOT NULL
GO

-- テーブルに列を追加
-- テーブルにデータがある場合は、
-- NULL許可または、DEFAULT定義がされている
-- カラムのみ追加できる
ALTER TABLE dbo.Product1
ADD Tax float NULL

-- カラムを削除する
ALTER TABLE dbo.Product1
DROP COLUMN Tax

-- 計算列の追加
ALTER TABLE dbo.Product1
ADD CatalogPrice AS (Price*Tax)

2. 制約の設定

制約を定義する方法や修正する方法を掲載します。インデックスに関しては[T-SQL] インデックスを操作するを参照してください。

2.1 プライマリーキー制約

プライマリキー列を定義する方法を掲載します。プライマリキーはテーブル作成時や作成後に設定できます。

-- 簡単なテーブルの定義例
CREATE TABLE dbo.Product1
(
	ProductID int NOT NULL,
    Name nvarchar(64) NOT NULL,
    Price money
)
GO
-- キーを設定
ALTER TABLE dbo.Product1
ADD CONSTRAINT PK_Product1
PRIMARY KEY (ProductID)
GO

-- Primary Key 付きでテーブル定義
CREATE TABLE dbo.ProductCatalog
(
	CatalogID int NOT NULL PRIMARY KEY,
    CatalogName nvarchar(128) NOT NULL
)
GO

-- Primary Key (制約名指定)でテーブル作成
CREATE TABLE dbo.UserRoles
(
	UserId int NOT NULL,
    RoleId int NOT NULL,
    CONSTRAINT PK_UserRole PRIMARY KEY (UserId, RoleId)
)
GO

2.2 外部キー制約

外部キー制約は、テーブル作成時および、作成後、に制約を追加できます。制約はCONSTRAINT　FOREIGN KEY 文を指定して作成します。外部キー制約を一時的に有効、無効にする場合は、ALTAR TABLE NOCHECK/CHECK 文を使用します。

-- テーブル定義時に外部キー制約を設定する例
CREATE TABLE dbo.UserRoles
(
	UserId int NOT NULL,
    RoleId int NOT NULL,
    CONSTRAINT PK_UserRole PRIMARY KEY (UserId, RoleId),
    CONSTRAINT FK_UserRoles_SampleUser
    FOREIGN KEY (UserId)
    REFERENCES dbo.SampleUser(UserId)
)
GO

-- 作成したテーブルの列に外部キー制約を
-- 設定する例
DROP TABLE dbo.UserRoles
GO

CREATE TABLE dbo.UserRoles
(
	UserId int NOT NULL,
    RoleId int NOT NULL,
    CONSTRAINT PK_UserRole PRIMARY KEY (UserId, RoleId)
)
GO
-- 外部キー制約を追加する
ALTER TABLE dbo.UserRoles
ADD CONSTRAINT FK_UserRolesSampleUser
FOREIGN KEY (UserId)
REFERENCES dbo.SampleUser(UserId)

-- 外部キー制約を無効にする
ALTER TABLE dbo.TestTable
NOCHECK CONSTRAINT FK_UserRolesSampleUser

-- 外部キー制約を有効にする
ALTER TABLE dbo.TestTable
CHECK CONSTRAINT FK_UserRolesSampleUser

2.3 UNIQUE / CHECK / DEFAULT 制約

UNIQUE は列(列の組み合わせ)が一意であること、CHECKは列が特定の制約を満たしていること、DEFAULT はINSERT 時に値が指定されたなかった場合の既定値を定義する制約です。UNIQUE 制約が列に設定されている場合、その列がNULL となる行が1つだけ許容されます。

次のサンプルに、UNIQUE, CHECK, DEFAULT 制約をテーブル定義時に設定する方法と、テーブル作成後に設定するクエリ例を記載しています。サンプルにあるように、制約を削除する場合はDROP CONSTRAINT 文を使います。CHECK 制約の有効・無効なFOREIGN KEY 制約と同様にCHECK CONSTRAINT文で設定できます。

CREATE TABLE dbo.TestTable
(
  ID int IDENTITY(1,1)  PRIMARY KEY,
  FriendlyNumber int NOT NULL UNIQUE,
  FirstName nvarchar(100) NOT NULL,
  LastName  nvarchar(100) NULL,
  MiddleName nvarchar(100) NULL,
  CreatedOn DateTime NULL DEFAULT GETDATE(),
  Sex nchar(1) NOT NULL CHECK(Sex = 'M' OR Sex = 'F'),
  Old int NULL,
  CONSTRAINT UQ_FirstNameLastName
  UNIQUE (FirstName, LastName)
)
-- UNIQUE 制約を追加する
ALTER TABLE dbo.TestTable
ADD CONSTRAINT UQ_MiddleName
UNIQUE (MiddleName)
GO

-- 制約を削除する
ALTER TABLE dbo.TestTable
DROP CONSTRAINT UQ_MiddleName

-- DEFAULT制約を設定する
ALTER TABLE dbo.TestTable
ADD CONSTRAINT DF_MiddleName
DEFAULT 'middle'
FOR MiddleName

-- CHECK制約を設定する
-- WITH NOCHECK とすると、既存のレコードに対する
-- チェックは行われず制約が追加される
ALTER TABLE dbo.TestTable
WITH CHECK
ADD CONSTRAINT CK_Old
CHECK( 0 <= Old And 120 >= Old)

-- 制約を無効にする
ALTER TABLE dbo.TestTable
NOCHECK CONSTRAINT CK_Old

-- 制約を有効にする
ALTER TABLE dbo.TestTable
CHECK CONSTRAINT CK_Old

3. まとめ

今回の説明は以上です。誤り指摘点がありましたらご連絡ください。

// Net planet-es V2