SQL INSERT - auto increment the surrogate key on platform tables?

Question

When I run a simple ITEM query to add a item called 'computer' how should I get the next SYS_ITEM_ID surrogate value?

If I run this more than once:

INSERT INTO ITEM (SYS_ITEM_ID, VC_ID, ENT_NAME, TYPE_NAME, SYS_ENT_ID, ITEM_NAME) VALUES (0, 1101, 'FdpeUserEnterprise', 'Standard Item', '10025', 'computer');

I will get:

SQL Error: ORA-00001: unique constraint (WKS_TSMITH_FDPE_MAIN.ITEM_PK) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

Do I need to query the next value to insert for SYS_ITEM_ID? What is the best way to handle this?

yyildiz · Accepted Answer

Platform has sequences for model tables. The one for ITEM table is ITEM_SEQ. You can use this sequence to get new long unique sys_item_id just like below:

INSERT INTO ITEM (SYS_ITEM_ID, VC_ID, ENT_NAME, TYPE_NAME, SYS_ENT_ID, ITEM_NAME) VALUES (ITEM_SEQ.NEXTVAL, 1101, 'FdpeUserEnterprise', 'Standard Item', '10000', 'computer...');

[Solved] SQL INSERT - auto increment the surrogate key on platform tables?