Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004
    Posts
    10

    Unanswered: generated by default as identity

    I have installed a DB2 v. 7.2

    After that I run a sql script ( text file ) to insert some test data.
    In the script I declare the pk myself.

    When I after that run my app, the pk constraint fails because the DB tryies to insert existing pk.

    Can I tell the DB to figure out what the next pk should be or somethin like that

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    If you use this, DB2 genereates the value of the PK for you. Don't supply a value for ID yourself.
    Code:
    CREATE TABLE xxxx (
    	ID	INTEGER		NOT NULL	GENERATED ALWAYS AS IDENTITY,
    	field2 CHAR(10),
    	CONSTRAINT PK_xxx PRIMARY KEY (ID)
    );
    
    INSERT INTO xxx (field2) VALUES('A');
    INSERT INTO xxx (field2) VALUES('Z');
    INSERT INTO xxx (field2) VALUES('B');
    INSERT INTO xxx (field2) VALUES('C');
    
    SELECT * FROM xxx order by field2;
    
    ID	fileld2
    1	A
    3	B
    4	C
    2	Z
    The ID was not supplied, but generated by DB2.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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