[ GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( START WITH 定整数 [ ,INCREMENT BY 定整数] ) ] ] ]
SMALLINTã€?INTã€?BIGINTåž‹ã?®åˆ—ã?«è˜åˆ¥å?属性ã?Œã?‚ã‚‹ã?¨ã€?Derbyã?¯è‡ªå‹•çš„ã?«åˆ—ã?®å€¤ã?«ã€?å¢—åŠ ã?™ã‚‹æ•´æ•°å€¤ã‚’ã?‚ã?¦ã?¾ã?™ã€‚挿入文ã?§åˆ—ã?®å€¤ã?ŒæŒ‡å®šã?•れã?ªã?„å ´å?ˆã€?è˜åˆ¥å?列属性ã?¯æ—¢å®šå€¤ã?¨å?Œæ§˜ã?«æŒ¯ã‚‹èˆžã?„ã€?Derbyã?¯åˆ—ã?«è‡ªå‹•çš„ã?«å€¤ã‚’与ã?ˆã?¾ã?™ã€‚ ã?—ã?‹ã?—ã?ªã?Œã‚‰ã€?ã??ã?®å€¤ã?¯å®šæ•°ã?§ã?¯ã?‚りã?¾ã?›ã‚“。Derbyã?¯è¡Œã?«æŒ¿å…¥ã?™ã‚‹æ™‚ã€?自動的ã?«æ—¢å®šå€¤ã?¸åŠ ç®—ã?—ã?¦ã‚†ã??ã?¾ã?™ã€‚
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 ALWAYSã?®åˆ—ã?¨ã?¯é?•ã?„ã€?挿入文ã?«ã?¦æ˜Žç¤ºçš„ã?ªå€¤ã‚’既定値ã?®å¤‰ã‚?りã?«ä¸Žã?ˆã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚
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を与ã?ˆã‚‹ã?¨ä¾‹å¤–ã?Œç™ºç”Ÿã?—ã?¾ã?™ã€‚
データ型 | 最大値 | 最�値 |
---|---|---|
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関数をå?‚ç…§ã?—ã?¦ã??ã? ã?•ã?„。
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');