Lightbox Info Board : SQLの窓

MDB ( Access 2000 以降 )


create table [自動] (
	[社員コード] IDENTITY(100,10)
	,[氏名] VARCHAR(50)
	,primary key([社員コード])
)

IDENTITY　は COUNTER でも可
最初の 100 は初期値で、次の 10 が増分

以下は変更


alter table [自動] 
alter column [社員コード] IDENTITY(500,2)

insert 後、自動採番された値を取得するには、@@IDENTITY を使用します

insert into board (board_title) values( NULL );
select @@IDENTITY;

SQLServer


create table [自動] (
	[社員コード] int IDENTITY(100,10)
	,[氏名] VARCHAR(50)
	,primary key([社員コード])
)

MDB と違って、データ型の記述が必要
tinyint、smallint、int、bigint、decimal(p,0)、または numeric(p,0) 列に割り当てることができます

初期値を以下のようにして変更できます


DBCC CHECKIDENT ([自動], RESEED, 500)

次に作成されるデータは510 です

既存の列を IDENTITY には変更できないようなので、そのような目的の場合には
いくつかパターンがあります。

1) 既存の ID の内容を破棄し、ID 値を持つ列の定義位置が一番最後に変更される。


ALTER TABLE [テーブル名]  ADD [新しいID列名] INT IDENTITY;
ALTER TABLE [テーブル名] DROP [元のID列のインデックス名];
ALTER TABLE [テーブル名] DROP COLUMN [元のID列名];
sp_rename 'テーブル名.新しいID列', '元のID列名', 'COLUMN';

2) SQLServer 同士で http://winofsql.jp/VA003334/msde060812212122.htm を行う

3) 新しいテーブルを IDENTITY 付きで定義し、SET IDENTITY_INSERT を使用する

SET IDENTITY_INSERT [テーブル名] ON
insert 文で ID 値に明示的に値をセット
SET IDENTITY_INSERT [テーブル名] OFF

※ 同一セッションで実行する事。

insert 後、自動採番された値を取得するには、SCOPE_IDENTITY( )　関数を使用します

insert into board (board_title) values( NULL );
select SCOPE_IDENTITY( );

SCOPE_IDENTITY と @@IDENTITY は、現在のセッション内の任意のテーブルで生成された最後の ID 値を返します。
ただし、SCOPE_IDENTITY が返す値は、現在の有効範囲の中で挿入された値に限られます。
@@IDENTITY の場合は、特定の有効範囲に限定されません。

MySQL


create table `自動` (
	`社員コード` BIGINT NOT NULL AUTO_INCREMENT
	,`氏名` VARCHAR(50)
	,primary key(`社員コード`)
)

または

create table `自動` (
	`社員コード` BIGINT NOT NULL AUTO_INCREMENT UNIQUE
	,`氏名` VARCHAR(50)
)

●インデックス付きの AUTO_INCREMENT カラムに NULL（推奨）または 0 を挿入すると、
そのカラムには連続値の次の値が設定される。(それ以外の値では、その値が設定されるので注意)
●通常、これは value+1 になる（value はテーブルに現在格納されているそのカラムの最大値）。
●AUTO_INCREMENT は 1 から開始される
●4.1.0 以降では、`社員コード`　SERIAL と記述できる

既存の整数列を AUTO_INCREMENT 化


alter table `テーブル名`
   change column `列名`
   `列名` int unsigned not null auto_increment,add index (`列名`)

insert 後、自動採番された値を取得するには、LAST_INSERT_ID()　関数を使用します

insert into board (board_id,board_title) values( NULL, NULL );
select LAST_INSERT_ID();

※ LAST_INSERT_ID()　は、接続単位に最後の値を保持します。
※ 単一の INSERT 文を使用して複数のレコードを挿入した場合、LAST_INSERT_ID() は最初に挿入されたレコードの値を返します

PostgreSQL


create table "自動" (
	"社員コード" SERIAL
	,"氏名" VARCHAR(50)
	,primary key("社員コード")
)

次の値を挿入するには、デフォルト値を割り当てるよう指定してください。
これは、INSERT文の列リストからその列を除外するか、DEFAULTキーワードを使用することで行ないます

serial は、実際は sequence を作成します。
serial で作成された sequence はテーブルの削除と共に削除されます


create sequence "自動_社員コード_seq";
create table "自動" (
	"社員コード" integer DEFAULT nextval('自動_社員コード_seq') NOT NULL
	,"氏名" VARCHAR(50)
	,primary key("社員コード")
)

シーケンスの存在の確認は、pg_class で行えます ( relkind = 'S' )。

以下は増分の変更


alter sequence 自動_社員コード_seq
	increment 10

以下は開始番号の変更


alter sequence 自動_社員コード_seq
	restart 100

insert 後、自動採番された値を取得するには、lastval()　関数を使用します

insert into "自動" ("氏名") values(NULL);
select lastval();

※ 現在のセッションのnextvalで戻される最新の値を返します。
※ 現在のセッションでnextvalが未だ呼ばれていなければエラーになります。

Oracle


Oracle では テーブルと関連付けた自動番号生成はできません。

sequence を作成して、insert 文の values 句に シーケンス名.nextval として
使用する事になります