{"id":145,"date":"2010-10-02T11:27:19","date_gmt":"2010-10-02T09:27:19","guid":{"rendered":"http:\/\/lwibs01.gm.fh-koeln.de\/blogs\/sqlexpert\/?p=145"},"modified":"2010-10-08T10:53:56","modified_gmt":"2010-10-08T08:53:56","slug":"fallstricke-kapitel-10","status":"publish","type":"post","link":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/2010\/10\/02\/fallstricke-kapitel-10\/","title":{"rendered":"Fallstricke Kapitel 10"},"content":{"rendered":"<ul>\n<li>Create Sequence: The default &#8222;START WITH 1 INCREMENT BY 1&#8220; is the reason why the first given number has the value 2<\/li>\n<\/ul>\n<p class=\"lead\"><code><br \/>\n-- Which value has NEXTVAL if an insert statement fails?<\/code><\/p>\n<p><code>CREATE TABLE SEQ_TEST (<br \/>\nid NUMBER NOT NULL,<br \/>\ndescription VARCHAR2(25) NOT NULL<br \/>\n);<\/code><\/p>\n<p><code>CREATE SEQUENCE SEQ_TEST_ID;<br \/>\nSELECT SEQUENCE_NAME, INCREMENT_BY, LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_TEST_ID';<br \/>\n-- SEQUENCE_NAME \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0INCREMENT_BY \u00a0 \u00a0LAST_NUMBER<br \/>\n-- ------------------------------ --------------- ---------------<br \/>\n-- SEQ_TEST_ID \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a01 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1<\/code><\/p>\n<p>&#8212; Second INSERT statement without description will violate NOT NULL constraint.<br \/>\n<code>INSERT INTO SEQ_TEST (id, description) VALUES (SEQ_TEST_ID.NEXTVAL, 'first row');<br \/>\nINSERT INTO SEQ_TEST (id) VALUES (SEQ_TEST_ID.NEXTVAL);<br \/>\nINSERT INTO SEQ_TEST (id, description) VALUES (SEQ_TEST_ID.NEXTVAL, 'third row');<br \/>\nCOMMIT;<\/code><\/p>\n<p><code> <\/code><\/p>\n<p><code>SELECT id, description FROM SEQ_TEST;<br \/>\n-- ID \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0DESCRIPTION<br \/>\n-- --------------- -------------------------<br \/>\n-- 2 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 first row<br \/>\n-- 4 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 third row<\/code><\/p>\n<p>Ein CURRVAL einer SEQUENCE kann erst benutzt werden, wenn vorher ein NEXTVALUE benutzt wurde.<\/p>\n<p>Und es gibt auch kein CREATE OR REPLACE SEQUENCE, sollte mal jemand danach fragen.<\/p>\n<p>Ein MAXVALUE kann auch negative sein, wenn MINVALUE kleiner als MAXVALUE ist und zus\u00e4tzlich CYCLE angegeben ist.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Create Sequence: The default &#8222;START WITH 1 INCREMENT BY 1&#8220; is the reason why the first given number has the value 2 &#8212; Which value has NEXTVAL if an insert statement fails? CREATE TABLE SEQ_TEST ( id NUMBER NOT NULL, description VARCHAR2(25) NOT NULL ); CREATE SEQUENCE SEQ_TEST_ID; SELECT SEQUENCE_NAME, INCREMENT_BY, LAST_NUMBER FROM USER_SEQUENCES WHERE&#8230;  <a href=\"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/2010\/10\/02\/fallstricke-kapitel-10\/\" class=\"more-link\" title=\"Read Fallstricke Kapitel 10\"><?php _e(\"Read more &raquo;\",\"wpbootstrap\"); ?><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[253],"tags":[321],"class_list":["post-145","post","type-post","status-publish","format-standard","hentry","category-10-creating-schema-objects","tag-create-sequence"],"acf":[],"_links":{"self":[{"href":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/wp-json\/wp\/v2\/posts\/145","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/wp-json\/wp\/v2\/comments?post=145"}],"version-history":[{"count":15,"href":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/wp-json\/wp\/v2\/posts\/145\/revisions"}],"predecessor-version":[{"id":175,"href":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/wp-json\/wp\/v2\/posts\/145\/revisions\/175"}],"wp:attachment":[{"href":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/wp-json\/wp\/v2\/media?parent=145"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/wp-json\/wp\/v2\/categories?post=145"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.gm.fh-koeln.de\/sqlexpert\/wp-json\/wp\/v2\/tags?post=145"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}