I have created a table in oracle database which I named
USERS and I have created a column
USER_ID with type
NUMBER which i set as primary key.
How can I modify the column
USER_ID to make it auto increment by 1 ?
Any help please ?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
If you insist on using IDENTITY, it is not be possible - you cannot modify non-identity column to identity. However, Oracle lets you use sequence.nextval as default, so you may get similar functionality :
CREATE SEQUENCE SEQ_USER START WITH 1000; --assuming max(USERS.user_id) =999 ALTER TABLE USERS MODIFY (USER_ID DEFAULT SEQ_USER.NEXTVAL);
It will work as
GENERATED BY DEFAULT AS IDENTITY.
That is not possible.
Use identity_clause to modify the properties of an identity column. You cannot specify this clause on a column that is not an identity column.