発番列仕様

[ GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY 
[ ( START WITH 定整数
[ ,INCREMENT BY 定整数] ) ]  ]  ]

識別�列属性

SMALLINTã€?INTã€?BIGINTåž‹ã?®åˆ—ã?«è­˜åˆ¥å­?属性ã?Œã?‚ã‚‹ã?¨ã€?Derbyã?¯è‡ªå‹•çš„ã?«åˆ—ã?®å€¤ã?«ã€?増加ã?™ã‚‹æ•´æ•°å€¤ã‚’ã?‚ã?¦ã?¾ã?™ã€‚挿入文ã?§åˆ—ã?®å€¤ã?ŒæŒ‡å®šã?•れã?ªã?„å ´å?ˆã€?識別å­?列属性ã?¯æ—¢å®šå€¤ã?¨å?Œæ§˜ã?«æŒ¯ã‚‹èˆžã?„ã€?Derbyã?¯åˆ—ã?«è‡ªå‹•çš„ã?«å€¤ã‚’与ã?ˆã?¾ã?™ã€‚ ã?—ã?‹ã?—ã?ªã?Œã‚‰ã€?ã??ã?®å€¤ã?¯å®šæ•°ã?§ã?¯ã?‚りã?¾ã?›ã‚“。Derbyã?¯è¡Œã?«æŒ¿å…¥ã?™ã‚‹æ™‚ã€?自動的ã?«æ—¢å®šå€¤ã?¸åŠ ç®—ã?—ã?¦ã‚†ã??ã?¾ã?™ã€‚

IDENTITY���キーワード�列�データ型�次���れ�整数��る場����指定�能��。
  • SMALLINT
  • INT
  • BIGINT
Derby��二種類�識別�列��り��。����GENERATED ALWAYS��も�一��GENERATED BY DEFAULT��。
GENERATED ALWAYS
GENERATED ALWAYSã?®è­˜åˆ¥å­?列ã?¯åˆ—ã?®æ—¢å®šå€¤ã‚’ã€?挿入ã?Œè¡Œã‚?れるã?¨ã??ã?”ã?¨ã?«å¢—加ã?•ã?›ã?¦ã€?ã??ã?®å¢—加ã?•れã?Ÿå€¤ã‚’列ã?«è¨˜éŒ²ã?—ã?¾ã?™ã€‚ä»–ã?®æ—¢å®šå€¤ã?¨ã?¯é?•ã?„ã€?GENERATED ALWAYSã?®è­˜åˆ¥å­?列ã?«å€¤ã‚’直接挿入ã?—ã?Ÿã‚Šã€?ã??ã?®å€¤ã‚’変更ã?—ã?Ÿã‚Šã?™ã‚‹ã?“ã?¨ã?¯ã?§ã??ã?¾ã?›ã‚“。ã??ã?®ä»£ã‚?りã?«è­˜åˆ¥å­?列ã?®å€¤ã?«ã?¯DEFAULTã?¨ã?„ã?†ã‚­ãƒ¼ãƒ¯ãƒ¼ãƒ‰ã‚’与ã?ˆã‚‹ã?‹ã€?識別å­?列を挿入ã?™ã‚‹åˆ—ã?®ä¸€è¦§ã?‹ã‚‰ã?¯ã?šã?™ã?‹ã?—ã?¾ã?™ã€‚ 例を挙ã?’ã?¾ã?™ã€‚
create table greetings
	(i int generated always as identity, ch char(50));
insert into greetings values (DEFAULT, 'hello');
insert into greetings(ch) values ('bonjour');
GENERATED ALWAYS�識別�列�自動的�発番�れ�値�一���。��識別�列を作���も�列�索引�作られ��ん。
GENERATED BY DEFAULT

GENERATED BY DEFAULTã?®è­˜åˆ¥å­?列ã?¯ã€?挿入時ã?«åˆ—ã?¸æ˜Žç¤ºçš„ã?ªå€¤ã?Œä¸Žã?ˆã‚‰ã‚Œã?ªã?‹ã?£ã?Ÿå ´å?ˆã?®ã?¿ã€?既定値を増加ã?•ã?›ã?¦ä½¿ã?„ã?¾ã?™ã€‚GENERATED ALWAYSã?®åˆ—ã?¨ã?¯é?•ã?„ã€?挿入文ã?«ã?¦æ˜Žç¤ºçš„ã?ªå€¤ã‚’既定値ã?®å¤‰ã‚?りã?«ä¸Žã?ˆã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚

生æˆ?ã?•れã?Ÿæ—¢å®šå€¤ã‚’使ã?†ã?Ÿã‚?ã?«ã?¯ã€?挿入時ã?«DEFAULTã?¨ã?„ã?†ã‚­ãƒ¼ãƒ¯ãƒ¼ãƒ‰ã‚’使ã?†ã?‹ã€?識別å­?列を挿入列リストã?‹ã‚‰å¤–ã?—ã?¾ã?™ã€‚挿入ã?™ã‚‹å€¤ã‚’指定ã?™ã‚‹ã?«ã?¯ã€?ã??ã?®å€¤ã‚’挿入文ã?«å?«ã‚?ã?¾ã?™ã€‚例を挙ã?’ã?¾ã?™ã€‚
create table greetings
	(i int generated by default as identity, ch char(50));
-- "1"を指定�る:
insert into greetings values (1, 'hi');
-- 生��れ�既定値を使�
insert into greetings values (DEFAULT, 'salut');
-- 生��れ�既定値を使�
insert into greetings(ch) values ('bonjour'); 
GENERATED ALWAYS列ã?¨ã?¯ç•°ã?ªã‚Šã€?GENERATED BY DEFAULTã?®åˆ—ã?§ã?¯ä¸€æ„?性ã?¯ä¿?éšœã?•れã?¾ã?›ã‚“。ã?—ã?Ÿã?Œã?£ã?¦ã€?上記ã?®ä¾‹ã?§ã?¯hiã?Šã‚ˆã?³salutã?®è¡Œã?¯ã?©ã?¡ã‚‰ã‚‚å?Œã?˜"1"ã?¨ã?„ã?†è­˜åˆ¥å€¤ã‚’å?–りã?¾ã?™ã€‚ã?ªã?œã?ªã‚‰ã€?生æˆ?ã?•れã?Ÿåˆ—ã?®å€¤ã?¯"1"ã?‹ã‚‰å§‹ã?¾ã‚Šã€?ユーザã?ŒæŒ‡å®šã?—ã?Ÿå€¤ã‚‚"1"ã? ã?£ã?Ÿã?‹ã‚‰ã?§ã?™ã€‚é‡?複をé?¿ã?‘ã‚‹ã?Ÿã‚?ã?«ã€?情報ã?®ãƒ­ãƒ¼ãƒ‰ã‚„インãƒ?ートを行ã?†ã?¨ã??ã?«ãƒ†ãƒ¼ãƒ–ルを作æˆ?ã?—ã?¦ã€?システムã?Œè‡ªå‹•生æˆ?ã?™ã‚‹ã?Ÿã‚?ã?®START WITHã?®å€¤ã‚’与ã?ˆã?¦ã??ã? ã?•ã?„。ã?“ã?®ã‚ˆã?†ã?ªçжæ³?ã‚’ãƒ?ェックã?—ã?¦ã€?èµ·ã??ã?ªã?„よã?†ã?«ã?™ã‚‹ã?Ÿã‚?ã?«ã€?主キーや一æ„?性制約をGENERATED BY DEFAULTã?®è­˜åˆ¥å­?列ã?«ä½¿ã?†ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚

既定ã?§ã?¯è­˜åˆ¥å­?列ã?®åˆ?期値ã?¯1ã?§ã?™ã€‚ã?•らã?«å¢—分値ã?¯1ã?§ã?™ã€‚ 列を定義ã?™ã‚‹ã?¨ã??ã€?åˆ?期値ã?¨å¢—分値ã?®ä¸¡æ–¹ã?«ã€?START WITHã?¨INCREMENT BYã?¨ã?„ã?†ã‚­ãƒ¼ãƒ¯ãƒ¼ãƒ‰ã‚’使ã?£ã?¦ã€?明示的ã?ªå€¤ã‚’指定ã?™ã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚ã?•らã?«å¢—分値ã?«è² ã?®å€¤ã‚’与ã?ˆã‚‹ã?¨ã€?挿入毎ã?«Derbyã?¯å€¤ã‚’減ã?˜ã?¦ã‚†ã??ã?¾ã?™ã€‚ ã‚‚ã?—値ã?Œæ­£ã?®å€¤ã?§ã?‚れã?°ã€?挿入毎ã?«Derbyã?¯å€¤ã‚’増加ã?•ã?›ã?¦ã‚†ã??ã?¾ã?™ã€‚ 値ã?«0を与ã?ˆã‚‹ã?¨ä¾‹å¤–ã?Œç™ºç”Ÿã?—ã?¾ã?™ã€‚

識別å­?列ã?«ã?¦å?¯èƒ½ã?ªæœ€å¤§å€¤ã?¨æœ€å°?値ã?¯ã€?列ã?®ãƒ‡ãƒ¼ã‚¿åž‹ã?«ã‚ˆã‚Šã?¾ã?™ã€‚データ型ã?§æŽ¡ã‚‹ã?“ã?¨ã?®ã?§ã??る範囲外ã?®å€¤ã‚’挿入ã?—よã?†ã?¨ã?™ã‚‹ã?¨ã€?例外ã?Œç™ºç”Ÿã?—ã?¾ã?™ã€‚
Table 1. 発番列仕様���列��る����能�最大値�最�値
データ型 最大値 最�値
SMALLINT 32767 (java.lang.Short.MAX_VALUE) -32768 (java.lang.Short.MIN_VALUE)
INT 2147483647 (java.lang.Integer.MAX_VALUE) -2147483648 (java.lang.Integer.MIN_VALUE)
BIGINT 9223372036854775807 (java.lang.Long.MAX_VALUE) -9223372036854775808 (java.lang.Long.MIN_VALUE)

識別å­?列ã?«ã?¦è‡ªå‹•çš„ã?«ç”Ÿæˆ?ã?•れã?Ÿå€¤ã?¯ä¸€æ„?ã?§ã?™ã€‚主キー制約や一æ„?性制約を列ã?«å®šç¾©ã?™ã‚‹ã?“ã?¨ã?§ã€?一æ„?性をä¿?éšœã?™ã‚‹äº‹ã?Œã?§ã??ã?¾ã?™ã€‚識別å­?列を定義ã?—ã?¦ã‚‚ã€?列ã?®ç´¢å¼•ã?¯ä½œã‚‰ã‚Œã?¾ã?›ã‚“ 。

IDENTITY_VAL_LOCAL関数ã?¯ã€?é?žæ±ºå®šæ€§ã?®é–¢æ•°ã?§æœ€å¾Œã?«è­˜åˆ¥å­?列ã?«ä¸Žã?ˆã‚‰ã‚Œã?Ÿå€¤ã‚’è¿”ã?—ã?¾ã?™ã€‚詳細ã?«ã?¤ã?„ã?¦ã?¯ã€?IDENTITY_VAL_LOCAL関数をå?‚ç…§ã?—ã?¦ã??ã? ã?•ã?„。

Note: スキーマ・表・列ã?®å??å‰?ã?¯ãƒ‡ãƒªãƒŸãƒˆã?•れã?Ÿè­˜åˆ¥å­?を使ã‚?ã?ªã?„é™?りã€?常ã?«ã‚·ã‚¹ãƒ†ãƒ è¡¨ã?«ã?¦å¤§æ–‡å­—ã?§è¨˜éŒ²ã?•れã?¾ã?™ã€‚

Derbyã?¯åˆ—ã?«å¯¾ã?—ã?¦æœ€å¾Œã?«ç™ºç•ªã?•れã?Ÿå€¤ã‚’キャッシュã?«æ®‹ã?—ã?¦ã?„ã?¾ã?™ã€‚ã?¾ã?Ÿã€?ディスク上ã?§ã?¯ã€?SYS.SYSCOLUMNSã?¨ã?„ã?†ã‚·ã‚¹ãƒ†ãƒ è¡¨ã?®AUTOINCREMENTVALUEã?¨ã?„ã?†åˆ—ã?«ã€?次ã?®å€¤ã‚’記録ã?—ã?¦ã?„ã?¾ã?™ã€‚ トランザクションをロールãƒ?ックã?—ã?¦ã‚‚ã?“ã?®å€¤ã?¯å?–り消ã?•れã?¾ã?›ã‚“。ã?—ã?Ÿã?Œã?£ã?¦ã€?トランザクションã?Œãƒ­ãƒ¼ãƒ«ãƒ?ックã?•れるã?¨ã€?識別å­?列ã?«æŒ¿å…¥ã?•れる自動生æˆ?値ã?«ã?¯ã€?空ã??ã?Œç™ºç”Ÿã?™ã‚‹ã?“ã?¨ã?¨ã?ªã‚Šã?¾ã?™ã€‚ Derby ã?¯ã?“ã?®ã‚ˆã?†ã?«å‹•作ã?™ã‚‹ã?“ã?¨ã?§ã€?SYS.SYSCOLUMNSã?®è¡Œã‚’トランザクションã?®æœŸé–“中ロックã?—ç¶šã?‘ã‚‹ã?“ã?¨ã‚’回é?¿ã?—ã?¦ã€?高ã?„平行性をä¿?ã?£ã?¦ã?„ã?¾ã?™ã€‚

トリガã?•れã?ŸSQLæ–‡ã?«ã?¦è­˜åˆ¥å­?列ã?«å€¤ã?ŒæŒ¿å…¥ã?•れã?Ÿå ´å?ˆã€?ã??ã?®SQLæ–‡ã?§æŒ¿å…¥ã?•れã?Ÿå€¤ã?¯ã€?トリガã?®ã‚³ãƒ¼ãƒ‰å†…ã?§ã?®ã?¿ConnectionInfoã?‹ã‚‰å?–å¾—å?¯èƒ½ã?§ã?™ã€‚ ã?¾ã?Ÿã€?トリガã?®ã‚³ãƒ¼ãƒ‰ã?¯ãƒˆãƒªã‚¬ã‚’ã?²ã?„ã?Ÿæ–‡ã?ŒæŒ¿å…¥ã?—ã?Ÿå€¤ã‚’å?–å¾—ã?™ã‚‹ã?“ã?¨ã‚‚ã?§ã??ã?¾ã?™ã€‚ ã?—ã?‹ã?—ã€?トリガをã?²ã?„ã?Ÿæ–‡ã?¯ã€?トリガã?•れã?ŸSQLæ–‡ã?Œè­˜åˆ¥å­?列ã?«æŒ¿å…¥ã?—ã?Ÿå€¤ã‚’知るã?“ã?¨ã?Œã?§ã??ã?¾ã?›ã‚“。 ã?•らã?«ãƒˆãƒªã‚¬ã?¯å…¥ã‚Œå­?(å†?帰)ã?®å ´å?ˆã‚‚å?Œã?˜ã‚ˆã?†ã?ªã?“ã?¨ã?¨ã?ªã‚Šã?¾ã?™ã€‚ SQLæ–‡ã?«ã‚ˆã‚ŠT1ã?¨ã?„ã?†ãƒˆãƒªã‚¬ã?Œç™ºç?«ã?•れã€?次ã?«T1ã?®å®Ÿè¡Œã?—ã?ŸSQLã?«ã‚ˆã‚ŠT2ã?¨ã?„ã?†ãƒˆãƒªã‚¬ã?Œç™ºç?«ã?•れã?Ÿã?¨ã?—ã?¾ã?™ã€‚ã?“ã?“ã?§T1ã‚‚T2も表ã?«è­˜åˆ¥å­?列ã?«å€¤ã?Œã?‚る行を挿入ã?—ã?Ÿå ´å?ˆã€?T1ã?¯T2ã?ŒæŒ¿å…¥ã?—ã?Ÿå€¤ã‚’見るã?“ã?¨ã?¯ã?§ã??ã?¾ã?›ã‚“ã?Œã€?T2ã?¯T1ã?ŒæŒ¿å…¥ã?—ã?Ÿå€¤ã‚’見るã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚ã??れã?žã‚Œã?®å…¥ã‚Œå­?ã?«ã?ªã?£ã?Ÿãƒˆãƒªã‚¬ã?¯è‡ªåˆ†è‡ªèº«ã?¨ã€?å†?帰的ã?ªãƒˆãƒªã‚¬ã‚’é–‹å§‹ã?—ã?ŸSQLã‚’å?«ã‚?ã?¦ã€?入れå­?ã?®ãƒ¬ãƒ™ãƒ«ã?Œå‰?ã?®å‡¦ç?†ã?«ã?¦ç”Ÿæˆ?ã?•れã?Ÿå€¤ã‚’見るã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚ ã?ªã?Šãƒˆãƒªã‚¬ã?®å†?帰ã?¯16ã?¾ã?§å?¯èƒ½ã?§ã?™ã€‚

例

create table greetings
  (i int generated by default as identity (START WITH 2, INCREMENT BY 1),
  ch char(50));
-- 1を指定�る。
insert into greetings values (1, 'hi');
-- 生��れ�既定値を使�
insert into greetings values (DEFAULT, 'salut');
-- 生��れ�既定値を使�
insert into greetings(ch) values ('bonjour'); 
Related reference
列定義