title: Toad for Oracle, Editing ID Column to be an auto-increment ID
body: <p>I work with <code>Toad for Oracle 12.1</code> for my database. I have a Table called <code>TBLEMPLOYEE</code> which already contain some data in it and having <code>Column Name</code> called <code>ID</code> whose data values are increasing from 1 to N.</p>
  
  <pre><code>ID  Name    Gender  DateOfBirth Type
  ------------------------------------
  1   Mark    Male    10/10/1982  1
  2   Mary    Female  11/11/1981  2
  3   Esther  Female  12/12/1984  2
  4   Matthew Male    9/9/1983    1
  5   John    Male    5/5/1985    1
  6   Luke    Male    6/6/1986    1
  </code></pre>
  
  <p>Now I want to change the <code>Column ID</code> such that it will have auto-incremented ID when I add a new data to the <code>Table</code>. </p>
  
  <p>I know that in Toad we can do it when we create a <code>New Table</code> with that behavior. For instance, using <code>Create Table</code> and in the newly created <code>Column</code>, we could set <code>Default / Virtual / Identity</code> settings as <code>Identity</code>:</p>
  
  <p><a href="https://i.stack.imgur.com/47wyp.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/47wyp.png" alt="enter image description here"></a></p>
  
  <p>And <code>Toad</code> will show a UI with bunch of settings to do that:</p>
  
  <p><a href="https://i.stack.imgur.com/fK0Dd.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fK0Dd.png" alt="enter image description here"></a></p>
  
  <p>And will be automatically translated to something like:</p>
  
  <pre><code>(START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9999999999999999999999999999 CACHE 20 NOCYCLE ORDER NOKEEP)
  </code></pre>
  
  <p>In the <code>Default / Virtual / Identity</code> settings. </p>
  
  <p>But I can't seem to do the same when I do <code>Alter Table</code> instead of <code>Create Table</code>. </p>
  
  <p><a href="https://i.stack.imgur.com/5KjzA.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5KjzA.png" alt="enter image description here"></a></p>
  
  <p>Why is that so? </p>
  
  <p>And since I already have some data in the <code>TBLEMPLOYEE</code>, I want to avoid creating a new table and re-inserting the data if possible.</p>
  
  <p>How can I do that?</p>
  
  <p>This is the current SQL script (if this may help):</p>
  
  <pre><code>ALTER TABLE MYSCHEMA.TBLEMPLOYEE
   DROP PRIMARY KEY CASCADE;
  
  DROP TABLE MYSCHEMA.TBLEMPLOYEE CASCADE CONSTRAINTS;
  
  CREATE TABLE MYSCHEMA.TBLEMPLOYEE
  (
    ID            NUMBER                          NOT NULL,
    NAME          VARCHAR2(80 BYTE)               NOT NULL,
    GENDER        VARCHAR2(6 BYTE),
    DATEOFBIRTH   DATE,
    EMPLOYEETYPE  INTEGER                         NOT NULL,
  )
  TABLESPACE USERS
  RESULT_CACHE (MODE DEFAULT)
  PCTUSED    0
  PCTFREE    10
  INITRANS   1
  MAXTRANS   255
  STORAGE    (
              INITIAL          64K
              NEXT             1M
              MAXSIZE          UNLIMITED
              MINEXTENTS       1
              MAXEXTENTS       UNLIMITED
              PCTINCREASE      0
              BUFFER_POOL      DEFAULT
              FLASH_CACHE      DEFAULT
              CELL_FLASH_CACHE DEFAULT
             )
  LOGGING 
  NOCOMPRESS 
  NOCACHE
  NOPARALLEL
  MONITORING;
  
  ALTER TABLE MYSCHEMA.TBLEMPLOYEE ADD (
    PRIMARY KEY
    (ID)
    USING INDEX
      TABLESPACE USERS
      PCTFREE    10
      INITRANS   2
      MAXTRANS   255
      STORAGE    (
                  INITIAL          64K
                  NEXT             1M
                  MAXSIZE          UNLIMITED
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 )
    ENABLE VALIDATE);
  </code></pre>