How should my INSERT INTO statement for GENERATED BY DEFAULT AS IDENTITY be?

Question

I'm trying to use GENERATED BY DEFAULT AS IDENTITY key for my record id's for my tables because a user needs to register themself and the user shouldn't be able to choose their own record id. So I decided to use GENERATED BY DEFAULT AS IDENTITY but I don't know how to write my INSERT statements.

This is my user table:

CREATE TABLE USER
(
   ID_USER INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
   USERNAME VARCHAR(20) UNIQUE NOT NULL,
   FORENAME VARCHAR(20) NOT NULL,
   SURNAME VARCHAR(20) NOT NULL,
   PASSWORD VARCHAR(10) NOT NULL,
   USER_TYPE INT NOT NULL,
   PRIMARY KEY(ID_USER),
   FOREIGN KEY (USER_TYPE) REFERENCES USER_TYPES(ID_TYPE)
);

and users will be allowed to register themselves.

This is what im using for my database

It's ok to be new! Thank you for editing! :) Can you post some of your Java code that is interacting with your database as well? I'm trying to understand your comment below where you said you are using the UI to do the INSERT statements, but it seems like you want the ID from the INSERT to use in another INSERT elsewhere. — Ashley, May 07 '19 at 15:45

Ashley · Answer 1 · 2019-05-07T15:18:11.937

2

When a table has a column GENERATED BY DEFAULT AS IDENTITY it means that you can insert with a value in that column if you want to but you don't have to. So then in your insert, you could instead write

INSERT INTO ARTIST (ORIGIN,ARTIST_NAME) VALUES ('USA','Nirvana');

For reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/apsg/src/tpc/db2z_identitycols.html

Edit after comment:

In the case where you need to retrieve the ID, @Mathias was correct that this is a duplicate question. A possible solution taken from this answer would be:

PreparedStatement result = cnx.prepareStatement(
    "INSERT INTO ARTIST (ORIGIN,ARTIST_NAME) VALUES ('USA','Nirvana')",
    RETURN_GENERATED_KEYS);
int updated = result.executeUpdate();
if (updated == 1) {
    ResultSet generatedKeys = result.getGeneratedKeys();
    if (generatedKeys.next()) {
        int key = generatedKeys.getInt(1);
    }
}

where key has the ID that you need for your next query.

edited May 07 '19 at 15:18

answered May 07 '19 at 14:39

Ashley

897
1
5
17

but i use this id as a foreign key in a song table?? INSERT INTO SONG (ID_SONG, SONG_CODE, SONG_TITLE,GENRE,AVAILABILITY,ID_ALBUM,ID_ARTIST) VALUES (‘1’,’S1’,’Come As You Are’,’Rock’,’Available’,’1’,’1’); – pofuduktavsancik May 07 '19 at 15:11
@pofuduktavsancik: then you need to obtain the generated value after inserting the song. Check out `PreparedStatement.getGeneratedKeys()` – May 07 '19 at 15:15
The link to the DB2 Z/OS manual is not helpful as this question is for [HSQLDB](http://hsqldb.org/) – May 07 '19 at 15:16
@a_horse_with_no_name INSERT INTO ALBUM (RELEASED_COUNTRY,RELEASE_YEAR,ALBUM_NAME) VALUES (‘USA’,’2000’,’Nirvana Deluxe’); my HSQLDB statement should be like that and I need to generated key statement in java code,right? – pofuduktavsancik May 07 '19 at 15:17
@Ashley but im using HSQLDB's Driver Manager UI to INSERT statements – pofuduktavsancik May 07 '19 at 15:22
@pofuduktavsancik I'm confused - you are using the UI to run the insert statements but you want the ID of the resulting row to use in your Java code? Please update your original question with more detail! – Ashley May 07 '19 at 15:29
@Ashley i updated, sorry im so new here and im so confused too :( – pofuduktavsancik May 07 '19 at 15:41
1

There is no need to check the update count. If the insert succeeded you **know** there is one row, and otherwise an exception is thrown. – Mark Rotteveel May 07 '19 at 16:16

score 1 · Answer 2 · answered May 07 '19 at 21:42

The question is slightly different from the one already answered and needs a different answer.

The OP states: user shouldn't be able to choose their own record id. In that case the column definition should be ID_USER INT NOT NULL GENERATED ALWAYS AS IDENTITY to disallow any user-supplied value.

The table name shouldn't be USER as this is a reserved word. Try USERS instead.

The insert statement shouldn't insert into the ID_USER column. Similar to the example in the other answer, it should list the columns that are being inserted. An example below:

 INSERT INTO USERS (USERNAME, FORENAME, SURNAME, PASSWORD, USER_TYPE)
   VALUES ('JohnSmith','John', 'Smith', 'apasswrdx67', 3)

The OP wants to insert the generated value into another table using the GUI DatabaseManager. This is done by using the IDENTITY() function immediatly after inserting that row. For example,

 INSERT INTO SOMETABLE (X, Y, Z) VALUES (IDENTITY(), 'some value', 'other value')

How should my INSERT INTO statement for GENERATED BY DEFAULT AS IDENTITY be?

2 Answers2