{"id":54,"date":"2008-03-25T19:36:32","date_gmt":"2008-03-25T23:36:32","guid":{"rendered":"http:\/\/www.dev-notes.com\/blog\/2008\/03\/25\/create-an-auto-increment-field-in-oracle\/"},"modified":"2008-03-25T19:36:32","modified_gmt":"2008-03-25T23:36:32","slug":"create-an-auto-increment-field-in-oracle","status":"publish","type":"post","link":"https:\/\/www.dev-notes.com\/blog\/2008\/03\/25\/create-an-auto-increment-field-in-oracle\/","title":{"rendered":"Create an auto-increment field in Oracle"},"content":{"rendered":"<p>If we do not already have a table to work with, we will create a sample table for this purpose.<\/p>\n<pre class=\"code\">\ncreate table schema.test_table (\n\trow_id\t\tnumber(5),\n\tname\t\tvarchar2(50),\n\tstatus\t\tvarchar2(1),\n\tentry_date\tdate\n);\n<\/pre>\n<p>Let us say we want to make the &#8220;row_id&#8221; field an &#8220;auto-increment&#8221; field.  To do so, we need a sequence object first.  The code below gives us an sequence that starts off at 1 and capped at 99999.<\/p>\n<pre class=\"code\">\ncreate sequence schema.seq_test_table_row_id\nminvalue 1\nmaxvalue 99999\nstart with 1\nincrement by 1\nnocache;\n<\/pre>\n<p>All we have left is the actual &#8220;auto-increment&#8221; part.  To do this, we build a very simple trigger on the table.<\/p>\n<pre class=\"code\">\ncreate trigger schema.trg_test_table_row_id\nbefore insert on schema.test_table\nfor each row\nbegin\n\tif (:NEW.row_id) is null then\n\t\tselect schema.seq_test_table_row_id.nextval into :NEW.row_id from dual;\n\tend if;\nend;\n<\/pre>\n<p>Now, when you insert into the table schema.test_table, if you wish to use the sequence object as an auto-incrementer, just do not enter the row_id field.  Two examples of using this auto-incrementer below:<\/p>\n<pre class=\"code\">\ninsert into schema.test_table (name, status, entry_date) values('John Doe','1',sysdate);\ninsert into schema.test_table values(null,'John Doe','1',sysdate);\n<\/pre>\n<p>What if we want to override this trigger and use a number of our choice for the row_id field?  No problem, just insert the value as you would normally.  Note the trigger has an if-clause in it; the trigger will only have an effect (ie. auto-increment from the sequence) if the incoming row_id field is not populated.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>One of the little convenient things that MySQL has is that, when defining a table, a key field can be made to auto-increment, so that we do not have to worry about populating that field.  Oracle takes a different approach with sequences being their own objects.  With this script, we can emulate this feature in Oracle while still enjoy having an independent sequence object.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-54","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/54","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/comments?post=54"}],"version-history":[{"count":0,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/54\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/media?parent=54"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/categories?post=54"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/tags?post=54"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}