Jul 30

12c IDENTITY columns

I live and work close to a Microsoft facility. As such, many of our current employees are former Microsoft employees that come from a SQL Server background. SQL Server lets you create a table with an IDENTITY column. Oracle 12c now lets you do the same. This should help those who are making the transition from SQL Server to Oracle. It also lets a company more easily port an application from SQL Server, or any other database that allows the IDENTITY column, to Oracle.

First I will create a table with the IDENTITY column and populate it with some rows of data.

SQL> create table test_tab (
2      id   NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,  
3      val  VARCHAR2(20));
Table created.
SQL> insert into test_tab (val) values ('my first row');
1 row created.
SQL> insert into test_tab (val) values ('my second row');
1 row created.
SQL> commit;
Commit complete.


Notice that I did not insert any values into the ID column. Now let’s query the table.


 


SQL> select * from test_tab;
ID VAL
---------- --------------------
1 my first row
2 my second row




As you can see, my ID values have been added as you might expect. In my table creation, I defined this IDENTITY column with:     GENERATED BY DEFAULT ON NULL


The BY DEFAULT clause means that Oracle will automatically assign the next value in the sequence if you omit it in your INSERT statement. If you include it, then Oracle will use your specified value. Consider this:


 


SQL> insert into test_tab values (4,'specified ID=4');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_tab;
        ID VAL
---------- --------------------
         1 my first row
         2 my second row
         4 specified ID=4




As you can see, because I explicitly stated ID=4 and Oracle let that value pass. What happens when I try to insert the next value, which should be 3?


SQL> insert into test_tab (val) values ('my row after ID=4');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_tab;
        ID VAL
---------- --------------------
         1 my first row
         2 my second row
         4 specified ID=4
         3 my row after ID=4




Pomegranates aid longevity, reduce heart disease and strokes, reverse the buildup of arterial plaque, and reduce blood sugar levels come across. viagra super active The company has successfully hindered many jailbrokeniOS users from free tadalafil  using certain iOS apps. These bacteria increase the resistance of online levitra no prescription  our intestines to these infections. One of these is poor circulation of the penis making the best price for tadalafil  nerves broad and the penis flexible to perform. 


The above worked as I expected. The next available ID value was used. But will the next insert use ‘4’ or ‘5’?






SQL>  insert into test_tab (val) values ('my fifth row');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_tab;
        ID VAL
---------- --------------------
         1 my first row
         2 my second row
         4 specified ID=4
         3 my row after ID=4
         4 my fifth row






Uh-oh! The duplicate value was allowed. I would have expected a Primary Key constraint to be created to enforce the concept of an “identity” value, but that does not happen. What constraints exist?






SQL> select constraint_name,constraint_type,table_name,search_condition from user_constraints;
CONSTRAINT_NAME                C TABLE_NAME
------------------------------ - ------------------------------
SEARCH_CONDITION
--------------------------------------------------------------------------------
SYS_C004978                    C TEST_TAB
"ID" IS NOT NULL






So the only constraint is a NOT NULL check constraint. Now let’s remove that last row and add a PK constraint.






SQL> delete from test_tab where val='my fifth row';
1 row deleted.
SQL> commit;
Commit complete.
SQL> alter table test_tab add constraint test_tab_pk primary key (id);
Table altered.








Now I’ll make sure I have some data to test with.






SQL> insert into test_tab (val) values ('after pk constraint');
1 row created.
SQL> insert into test_tab (id,val) values (6,'explicitly set id=6');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test_tab;
        ID VAL
---------- --------------------
         1 my first row
         2 my second row
         4 specified ID=4
         3 my row after ID=4
         5 after pk constraint
         6 explicitly set id=6
6 rows selected.






So I explicitly added ID=6. If this is like when I explicitly added ID=4, my next insert will attempt to use ID=6 and with the PK constraint in place, an exception will be thrown.






SQL> insert into test_tab (val) values ('after ID=6');
insert into test_tab (val) values ('after ID=6')
*
ERROR at line 1:
ORA-00001: unique constraint (PEASLAND.TEST_TAB_PK) violated






So the moral to the story is if you use ON DEFAULT, be prepared to handle identity value collisions. The default is ALWAYS instead of ON DEFAULT. With ALWAYS, Oracle will always use the sequence number generator. If you try to specify an id value, an exception will occur.






SQL> create table test_tab2(id number generated always as identity, val varchar2(20));
Table created.
SQL> insert into test_tab2(id,val) values (1,'first row');
insert into test_tab2(id,val) values (1,'first row')
                      *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column






The view *_TAB_COLUMNS can show you which columns in a table are IDENTITY columns.






SQL> select column_name,identity_column from user_tab_columns where table_name='TEST_TAB';
COLUMN_NAME     IDE
--------------- ---
ID              YES
VAL             NO






If you use the IDENTITY column in your tables, be careful to test to ensure you understand that it works correctly for your application. I was surprised that a PK or UNIQUE constraint was not automatically included which allowed me to add a duplicate value.







								

		
			





    

        

            

            	
            


            

                
bpeasland

                    
    
    	        
			        
    •