Alter default identity value

{question}

Is it possible to alter the identity sequence after the data insertion or do I need to do it before the data insertion?

{question}

 

{answer}

Sequence floor subject can be altered according to the rules of using Sequences in a distributed NuoDB environment. The value itself may not be directly manipulated.

Check the following example:

SQL> CREATE TABLE contact_list (ID bigint generated always as identity primary key NOT NULL, Name String);
SQL> insert into contact_list (Name) values ('Lisa');
SQL> insert into contact_list (Name) values ('Moran');
SQL> insert into contact_list (Name) values ('Mark');
SQL> select * from contact_list; ID NAME
--- ----- 1 Lisa
2 Moran
3 Mark

Here is how you can determine the sequence name that was generated:

SQL> select * from system.sequences; 
SCHEMA SEQUENCENAME
------- ------------------------------ USER CONTACT_LIST$IDENTITY_SEQUENCE

 

Once you have the sequence name you can change the value by:

SQL> alter sequence USER.CONTACT_LIST$IDENTITY_SEQUENCE start with 1000;
SQL> insert into contact_list (Name) values ('Paul');
SQL> select * from contact_list; ID NAME
---- ----- 1 Lisa
2 Moran
3 Mark
1001 Paul

 

You can find more information and examples on how sequences work in NuoDB here.

 

{answer}

Have more questions? Submit a request

Comments