T-SQL - よく使う CREATE TABLE と ALTER TABLE のクエリーのまとめ

よく使う CREATE TABLE と ALTER TABLE 文

SQL Server で開発をしていると、クエリーでテーブルを作成したり変更したりする機会が出てくると思います。

ここでは、私も実際によく使う CREATE TABLE と ALTER TABLE 文をまとめておきたいと思います。

CREATE TABLE: 主キーを作成

CREATE TABLE 文でテーブルを作成する際に同時に主キーを作成したい時は、次のように主キーとなるカラムの定義につづけて PRIMARY KEY と追加することで作成できます。

CREATE TABLE dbo.Employee (
	EmployeeID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	FirstName NVARCHAR(50) NULL,
	LastName NVARCHAR(50) NULL,
	Gender CHAR(1) NULL,
	ActiveFlag BIT NOT NULL
);

この場合は、主キーの制約名は自動に生成されます。例: PK__Employee__7AD04FF1F39B44DE

CREATE TABLE: 主キーに名前をつけて作成

CREATE TABLE 文でテーブルを作成する際に、同時に主キーに制約名を指定して作成したい時は次のようにできます。

CREATE TABLE dbo.Employee (
	EmployeeID INT NOT NULL IDENTITY(1,1),
	FirstName NVARCHAR(50) NULL,
	LastName NVARCHAR(50) NULL,
	Gender CHAR(1) NULL,
	ActiveFlag BIT NOT NULL,
	CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID)
);

CONSTRAINT [主キー名] PRIMARY KEY ([主キーとなるカラム]) のように指定します。

CREATE TABLE: 複合の主キーを作成

CREATE TABLE 文でテーブルを作成する際に、複合主キー（複数のカラムを組み合わせた主キー）を指定したい時は次のようにできます。

CREATE TABLE dbo.Employee (
	DepartmentID INT NOT NULL,
	EmployeeSeqNo INT NOT NULL,
	FirstName NVARCHAR(50) NULL,
	LastName NVARCHAR(50) NULL,
	Gender CHAR(1) NULL,
	ActiveFlag BIT NOT NULL,
	CONSTRAINT PK_Employee PRIMARY KEY (DepartmentID, EmployeeSeqNo)
);

CONSTRAINT [主キー名] PRIMARY KEY ([主キーとなるカラム1], [主キーとなるカラム2], ..) のように指定します。

CREATE TABLE: DEFAULT 制約を作成

CREATE TABLE 文でテーブルを作成する際に同時に DEFAULT 制約を作成したい時は、次のように DEFAULT 制約を作成したいカラムの定義につづけて DEFAULT [デフォルト値] と追加することで作成できます。

CREATE TABLE dbo.Employee (
	EmployeeID INT NOT NULL IDENTITY(1,1),
	FirstName NVARCHAR(50) NULL,
	LastName NVARCHAR(50) NULL,
	Gender CHAR(1) NULL,
	ActiveFlag BIT NOT NULL DEFAULT 1,
	CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID)
);

この場合は、DEFAULT 制約の名前は自動に生成されます。例: DF__Employee__Active__55009F39

CREATE TABLE: DEFAULT 制約を名前をつけて作成

CREATE TABLE 文でテーブルを作成する際に、同時に DEFAULT 制約に名前を指定して作成したい時は次のようにできます。

CREATE TABLE dbo.Employee (
	EmployeeID INT NOT NULL IDENTITY(1,1),
	FirstName NVARCHAR(50) NULL,
	LastName NVARCHAR(50) NULL,
	Gender CHAR(1) NULL,
	ActiveFlag BIT NOT NULL CONSTRAINT DF_Employee_ActiveFlag DEFAULT 1,
	CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID)
);

DEFAULT 制約を作成したいカラムの定義につづけて、CONSTRAINT [DEFAULT 制約名] DEFAULT [デフォルト値] のように指定します。

CREATE TABLE: CHECK 制約を作成

CREATE TABLE 文でテーブルを作成する際に同時に CHECK 制約を作成したい時は、次のように CHECK 制約を作成したいカラムの定義につづけて CHECK ([チェック式]) と追加することで作成できます。

CREATE TABLE dbo.Employee (
	EmployeeID INT NOT NULL IDENTITY(1,1),
	FirstName NVARCHAR(50) NULL,
	LastName NVARCHAR(50) NULL,
	Gender CHAR(1) NULL CHECK (Gender IN ('F', 'M')),
	ActiveFlag BIT NOT NULL CONSTRAINT DF_Employee_ActiveFlag DEFAULT 1,
	CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID)
);

この場合は、DEFAULT 制約の名前は自動に生成されます。例: CK__Employee__Gender__5AB9788F

CREATE TABLE: CHECK 制約を名前をつけて作成

CREATE TABLE 文でテーブルを作成する際に、同時に CHECK 制約に名前を指定して作成したい時は次のようにできます。

CREATE TABLE dbo.Employee (
	EmployeeID INT NOT NULL IDENTITY(1,1),
	FirstName NVARCHAR(50) NULL,
	LastName NVARCHAR(50) NULL,
	Gender CHAR(1) NULL CONSTRAINT CK_Employee_Gender CHECK (Gender IN ('F', 'M')),
	ActiveFlag BIT NOT NULL CONSTRAINT DF_Employee_ActiveFlag DEFAULT 1,
	CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID)
);

CHECK 制約を作成したいカラムの定義につづけて、CONSTRAINT [CHECK 制約名] CHECK ([チェック式]) のように指定します。

CREATE TABLE: 外部キーを作成

CREATE TABLE 文でテーブルを作成する際に同時に 外部キー制約を作成したい時は、次のように 外部キー制約を作成したいカラムの定義につづけて REFERENCES [参照されるテーブル名]([参照されるカラム名]) と追加することで作成できます。

CREATE TABLE dbo.EmployeeAddress (
	EmployeeAddressID INT NOT NULL IDENTITY(1,1),
	EmployeeID INT NOT NULL REFERENCES dbo.Employee(EmployeeID),
	Address1 NVARCHAR(200) NULL,
	Address2 NVARCHAR(200) NULL,
	City NVARCHAR(100) NULL,
	CONSTRAINT PK_EmployeeAddress PRIMARY KEY (EmployeeAddressID)
);

この場合は、外部キーの制約名は自動に生成されます。例: FK__EmployeeA__Emplo__4D5F7D71

CREATE TABLE: 外部キーに名前をつけて作成

CREATE TABLE 文でテーブルを作成する際に、同時に 外部キー制約に名前を指定して作成したい時は次のようにできます。

CREATE TABLE dbo.EmployeeAddress (
	EmployeeAddressID INT NOT NULL IDENTITY(1,1),
	EmployeeID INT NOT NULL,
	Address1 NVARCHAR(200) NULL,
	Address2 NVARCHAR(200) NULL,
	City NVARCHAR(100) NULL,
	CONSTRAINT PK_EmployeeAddress PRIMARY KEY (EmployeeAddressID),
	CONSTRAINT FK_EmployeeAddress_Employee FOREIGN KEY (EmployeeID) REFERENCES dbo.Employee(EmployeeID)
);

CONSTRAINT [外部キー名] FOREIGN KEY ([外部キーを作成したいカラム]) REFERENCES [参照されるテーブル名]([参照されるカラム名]) のように指定します。

ALTER TABLE: カラムを追加

次のような Customer テーブルがあるとします。

CREATE TABLE dbo.Customer (
	CustomerID INT NOT NULL IDENTITY(1,1),
	FirstName NVARCHAR(50) NULL,
	LastName NVARCHAR(50) NULL,
	Age INT NULL,
	CONSTRAINT PK_Customer PRIMARY KEY (CustomerID)
);

この Customer テーブルに対して、ALTER TABLE 文でテーブルでシンプルにカラムを追加するには、次のようにできます。

ALTER TABLE dbo.Customer
ADD CustomerNumber NVARCHAR(50) NULL;

CustomerNumber という名前で、NVARCHAR(50) のデータ型の nullable のカラムを追加しています。

ALTER TABLE: カラムを追加すると同時に DEFAULT 制約の追加

ALTER TABLE 文でカラムを追加する際に同時に DEFAULT 制約を作成したい時は、次のように作成できます。

ALTER TABLE dbo.Customer
ADD ActiveFlag BIT NOT NULL CONSTRAINT DF_Customer_ActiveFlag DEFAULT 1;

カラムの定義につづけて、CONSTRAINT [DEFAULT 制約名] DEFAULT [デフォルト値] のように指定します。

ALTER TABLE: カラムを追加すると同時に CHECK 制約の追加

ALTER TABLE 文でカラムを追加する際に同時に CHECK 制約を作成したい時は、次のように作成できます。

ALTER TABLE dbo.Customer
ADD Gender CHAR(1) NULL CONSTRAINT CK_Customer_Gender CHECK (Gender IN ('F', 'M'));

カラムの定義につづけて、CONSTRAINT [CHECK 制約名] CHECK ([チェック式]) のように指定します。

ALTER TABLE: カラムを追加すると同時に外部キーを追加

次のような Department テーブルがあるとします。

CREATE TABLE dbo.Department (
	DepartmentID INT NOT NULL IDENTITY(1,1),
	DepartmentName NVARCHAR(200) NULL,
	CONSTRAINT PK_Department PRIMARY KEY (DepartmentID)
);

そして、Customer テーブルに DepartmentID カラムを追加すると同時に外部キーを作成するには次のようにできます。

ALTER TABLE dbo.Customer
ADD DepartmentID INT NULL CONSTRAINT FK_Customer_Department FOREIGN KEY (DepartmentID) REFERENCES dbo.Department(DepartmentID);

カラムの定義につづけて、CONSTRAINT [外部キー名] FOREIGN KEY ([外部キーを作成したいカラム]) REFERENCES [参照されるテーブル名]([参照されるカラム名]) のように指定します。

ALTER TABLE: カラムを NULL から NOT NULL に変更

ALTER TABLE 文でカラムを NULL から NOT NULL に変更したい時は次のようにできます。

ALTER TABLE dbo.Customer
ALTER COLUMN CustomerNumber NVARCHAR(50) NOT NULL;

先ほど CustomerNumber NVARCHAR(50) NULL で追加したカラムを ALTER TABLE - ALTER COLUMN で NOT NULL に変更しています。

CustomerNumber カラムに NULL データがあるとエラーになるのでご注意ください。

ALTER TABLE: カラムのデータ型を変更

ALTER TABLE 文でカラムのデータ型を変更したい時は次のようにできます。

ALTER TABLE dbo.Customer
ALTER COLUMN Age TINYINT NULL;

先ほど Age INT NULL のカラムを ALTER TABLE - ALTER COLUMN で TINYINT 型に変更しています。

ALTER TABLE: DEFAULT 制約を追加

次のような Student テーブルがあるとします。

CREATE TABLE dbo.Student (
	StudentID INT NOT NULL IDENTITY(1,1),
	FirstName NVARCHAR(50) NULL,
	LastName NVARCHAR(50) NULL,
	Age INT NULL,
	ActiveFlag BIT NOT NULL,
	Gender CHAR(1) NULL,
	CONSTRAINT PK_Student PRIMARY KEY (StudentID)
);

この Student テーブルに ALTER TABLE 文で、DEFAULT 制約を追加したい時は、次のように作成できます。

ALTER TABLE dbo.Student
ADD CONSTRAINT DF_Student_ActiveFlag DEFAULT 1 FOR ActiveFlag;

ALTER TABLE [テーブル名] ADD CONSTRAINT で [DEFAULT 制約名] DEFAULT [デフォルト値] FOR [DEFAULT 制約を追加するカラム名] のように指定します。

ALTER TABLE: CHECK 制約を追加

Student テーブルに ALTER TABLE 文で、CHECK 制約を追加したい時は、次のように作成できます。

ALTER TABLE dbo.Student
ADD CONSTRAINT CK_Student_Gender CHECK (Gender IN ('F', 'M'));

ALTER TABLE [テーブル名] ADD CONSTRAINT で [CHECK 制約名] CHECK ([チェック式]) のように指定します。

ALTER TABLE: 外部キーの追加

次のような Major テーブルがあるとします。

CREATE TABLE dbo.Major (
	MajorID INT NOT NULL IDENTITY(1,1),
	MajorName NVARCHAR(200) NULL
	CONSTRAINT PK_Major PRIMARY KEY (MajorID)
);

この Student テーブルに対して、ALTER TABLE 文でテーブルで MajorID カラムを追加しておきます。

ALTER TABLE dbo.Student
ADD MajorID INT NULL;

この状態から、ALTER TABLE 文で、外部キーを追加したい時は、次のように作成できます。

ALTER TABLE dbo.Student
ADD CONSTRAINT FK_Student_Major FOREIGN KEY (MajorID) REFERENCES dbo.Major(MajorID);

ALTER TABLE [テーブル名] ADD CONSTRAINT [外部キー名] FOREIGN KEY ([外部キーを作成したいカラム]) REFERENCES [参照されるテーブル名]([参照されるカラム名]) のように指定します。

ALTER TABLE: カラムの削除

Student テーブルから ALTER TABLE 文で、カラムを削除したい時は、次のようにできます。

ALTER TABLE dbo.Student
DROP COLUMN FirstName;

ALTER TABLE [テーブル名] DROP COLUMN [削除するカラム名] のように指定します。

ALTER TABLE: 制約（外部キー・DEFAULT・CHECK）を名前を指定して削除

外部キー、DEFAULT、CHECK などの制約がついているカラムを削除しようとすると、エラーになります。

例えば、Student テーブルから、先ほど外部キーを追加した MajorID カラムを削除しようとすると、次のようなエラーになります。

ALTER TABLE dbo.Student
DROP COLUMN MajorID;

Msg 5074, Level 16, State 1, Line 187
The object 'FK_Student_Major' is dependent on column 'MajorID'.
Msg 4922, Level 16, State 9, Line 187
ALTER TABLE DROP COLUMN MajorID failed because one or more objects access this column.

そんな時は先に制約を削除してから、カラムを削除します。

ALTER TABLE 文で、外部キー・DEFAULT・CHECK などの制約を削除したい時は、次のようにできます。

ALTER TABLE dbo.Student
DROP CONSTRAINT FK_Student_Major;

ALTER TABLE [テーブル名] DROP CONSTRAINT [削除する制約名] のように指定します。

これを実行した後で、再度次のスクリプトを実行すると、MajorID カラムを削除できます。

ALTER TABLE dbo.Student
DROP COLUMN MajorID;

以上、実際によく使う CREATE TABLE と ALTER TABLE 文をご紹介しました。