テーブルを生成するSQL文、create tableについてまとめました。
以下、MySQL前提のサンプルコードになります。データベースとして、MySQLのサンプルデータベースEmployeesを使っています。
create table の使い方
CREATE TABLE employees ( emp_no INT(11) NOT NULL, birth_date DATE NOT NULL, first_name VARCHAR(14) NOT NULL, last_name VARCHAR(16) NOT NULL, gender ENUM('M', 'F') NOT NULL, hire_date DATE NOT NULL, PRIMARY KEY( emp_no ) ) engine=innodb DEFAULT charset=utf8;
カラム名、INT型、日付型などのデータ型、NULLを許可するか、デフォルト値、プライマリーキーとなるカラムを指定します。
指定可能なオプションには、他にもユニークキー指定やAUTO_INCREMENTなどがあります。
データベース領域のタイプ、文字セットを指定します。
primaryキーを後から追加設定する
PRIMARYキーは、create table文に含めることも出来ますが、Alter table文でテーブル作成後に追加設定が可能です。
ALTER TABLE `employees` ADD PRIMARY KEY (`emp_no`);
インデックスキーを一時的にdropしてデータを大量に高速に削除するような運用をおこなう場合は、インデックスのみ設定しなおすためにalter tableで記述したSQLファイルを用意しておくとよいでしょう。
foreignキーを後から追加設定する
foreignキーもPRIMARYキー同様、create table文に含めることが可能です。
しかし、後からインデックスのみ再設定することを想定して、alter table文でSQLファイルを用意しておくのが良いでしょう。
ALTER TABLE `dept_emp` ADD CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE, ADD CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE;
default値を後から設定する
ALTER TABLE `salaries` ALTER `salary` `salary` SET DEFAULT '0'
対象カラムの情報をまるごと再設定することも可能です。
ALTER TABLE `salaries` CHANGE `salary` `salary` INT(11) NOT NULL DEFAULT '0' COMMENT '給与';
default値以外に、カラム名、データ型、NULLの許可、カラムコメントの変更が可能です。
selectの結果から、新しいテーブルを作成
select文の結果を使って、新しいテーブルを作成できます。
単純に元のテーブルを複製する場合は、以下のSQLを実行します。
CREATE TABLE employees2 AS SELECT * FROM employees
employeesテーブルを複製して、employees2テーブルを作成します。
なお、データ構造のみ複製し、データをコピーしたくない場合は以下のSQLを実行します。
CREATE TABLE employees2 AS SELECT * FROM employees WHERE 1=2
WHERE 1=2という条件は常に偽となるため、データはコピーされません。
create table実行時のエラー already exists
create table実行時に、以下のエラーが出力されることがあります。
#1050 - テーブル名 already exists
create tableに「IF NOT EXISTS」をつけることで、テーブルが存在しないときのみテーブルを作成するようになり、エラーを抑制できます。
CREATE TABLE IF NOT EXISTS employees ( emp_no INT(11) NOT NULL, birth_date DATE NOT NULL, first_name VARCHAR(14) NOT NULL, last_name VARCHAR(16) NOT NULL, gender ENUM('M', 'F') NOT NULL, hire_date DATE NOT NULL, PRIMARY KEY( emp_no ) )
主要DBMSのcreate table関連の差異
Oracleはデフォルト値削除ができない
Oracleでは、テーブルのカラムのデフォルト値を削除するDDLがありません。
デフォルト値にNULLを設定することで、「デフォルト値を設定していない状態」と同じ振る舞いをさせることができます。
MySQLは、テーブル構造を生成するSQL出力可能
mysqlでは、既存データベースからテーブル構造を生成するSQLを出力することが可能です。
mysqldump --no-data データベース名 > tablecreate.sql
create table、create algorithm(ビューのクリエイト）、プライマリキーやインデックス、制約（Foreignキー）の生成が含まれます。
・
・
・
--
-- テーブルの構造 `employees`
--
CREATE TABLE IF NOT EXISTS `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
・
・
・
SQL Serverのselect～into文は独自仕様
SQL Serverでは、selectで抽出したデータでtable createする場合の構文が、MySQLやOracleとは異なります。
SELECT * FROM employees INTO employees2
create tableのまとめ
- create table でテーブルを作成できる
- テーブル作成後にPRIMARYキー制約やForeignキー制約、Default値などの変更が可能
- DBMSにより、create tableの仕様差異あり
