Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Posts
    24

    Arrow Unanswered: How to turn off GENERATED ALWAYS AS IDENTITY?

    Hi All -
    I am having problem using GENERATED ALWAYS AS IDENTITY. Have a look at the this.

    Cust_key Cust_Name Location
    --------- ---------- ---------

    CREATE TABLE DEV.STUDENT
    (
    STUDENT_ID INTEGER NOT NULL GENERATED ALWAYS AS
    IDENTITY (START WITH 1, INCREMENT BY 1, NO
    CACHE ) ,
    NAME CHAR(10),
    DEPT CHAR(4),
    GRADE CHAR(2),
    PRIMARY KEY (STUDENT_ID)
    ) DATA CAPTURE NONE IN USERSPACE1

    Some INSERTs goes on here....

    INSERT INTO DEV.STUDENT(NAME,DEPT,GRADE) VALUES('John','ENGG','AA')
    ........
    ............
    ..................

    And the result set looks like....

    STUDENT_ID NAME DEPT GRADE
    --------------- --------- ------- ---------
    1 John ENGG AA
    2 Nancy PROD AB
    3 Kamal ENGG BA
    4 Jia APPS AA
    5 Ahmed STMP BA
    6 Sunga ENGG AB
    7 Lee RAND BB
    8 Amanda ENGG BB

    DELETE FROM DEV.STUDENT WHERE GRADE = 'BB'

    And NOW the result set looks like....

    STUDENT_ID NAME DEPT GRADE

    ----------- ---------- ---- -----

    1 John ENGG AA

    2 Nancy PROD AB

    3 Kamal ENGG BA

    4 Jia APPS AA

    5 Ahmed STMP BA

    6 Sunga ENGG AB


    How do I reassign the deleted student_id's (7 and 8) to new students?
    Thanks in advance
    LACA

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "How do I reassign the deleted student_id's (7 and 8) to new students?"

    don't


    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Jan 2002
    Location
    Manila, Philippines
    Posts
    71

    what if you try to ...

    use GENERATED BY DEFAULT AS IDENTITY instead ?

    You however need to check for the deleted STUDENT_ID (7 and 8) then include these value on your Insert ... INSERT INTO DEV.STUDENT(STUDENT_ID,...) VALUES(7,...).

    You also need to make sure that all inserts with Student_Id value will only be using a deleted Student_Id else you'll have problem when column generates an already existing Student_Id (from a record which was manually inserted)

    HTH,
    Oliver

Posting Permissions

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