Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: problem with IDENTITY

    Hi,

    I have written some code using IDENTITY as

    CREATE TABLE M_D_S(ROWID INT GENERATED ALWAYS AS IDENTITY (START WITH 1,INCREMENT BY 1),SERVER_NAME VARCHAR(100) not null, DB_NAME VARGRAPHIC(100) not null,
    TABLE_NAME VARCHAR(128) not null,COLUMN_NAME VARCHAR(128) not null, COLUMN_ID INTEGER not null,S_COLUMN_TYPE_ID INTEGER not null,
    S_COLUMN_TYPE VARCHAR(200) not null,DATA VARCHAR(4000));

    And I can insert values into this table too.
    Problem is when ever deleted records from this M_D_S table & again inserting new set of records My ROWID is not showing values from 1, it is showing from the next iteration.
    For example If i have inserted 8000 records into that M_D_S & the starting value of my ROWID is 1. I have deleted those 8000 records & again inserted 9000 records now My ROWID is 8001,
    what I expect is on each & every deletion & insert My ROWID need to start from 1 itself. how does it possible, should I need to add anything to my table creation or should i use SEQUENCE for this.

    thanks in advance

  2. #2
    Join Date
    Jul 2014
    Posts
    294
    After issuing

    ALTER TABLE M_D_S ALTER ROWID RESTART WITH 1';

    I can't find the value to be 1 on my ROW_ID.

  3. #3
    Join Date
    Jul 2014
    Posts
    294
    Resolved..

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Just a quick tip. Stay away from generated always. Use generated by default. You will save yourself a ton of headaches.
    Dave

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •