Ok say I have a table with two columns. Entry_id and name. Entry_id is a ROWID NOT NULL. Essentially I just want it to increment every time something new is put in. How do i do this in the PreparedStatement. I won't know the correct Entry_id bc it doesn't matter. It should just increment everytime. So it would be nice if i could just insert the name into the table and entry_id increments automatically. Any idea how to do this?
3 Answers
A ROWID is a physical address for a row in a table. It does not make sense to use a ROWID as a key-- a ROWID will change over time if a table is moved from one tablespace to another, if you do an export and import, if row movement occurs, etc. And it does not make sense to increment a ROWID since the result would quite likely be invalid either in the sense that it would no longer be the physical address of an actual row or that it would not longer be a valid physical address.
If you want an auto-incrementing primary key in Oracle, you would declare the column as a NUMBER, not a ROWID. You would then create a sequence object
CREATE SEQUENCE entry_id_seq
START WITH 1
INCREMENT BY 1
CACHE 100;
and reference the NEXTVAL of that sequence in your INSERT statement
INSERT INTO entry( entry_id, name )
VALUES( entry_id_seq.nextval, :1 );
Of course, you could create a before-insert trigger to populate the primary key from the sequence
CREATE OR REPLACE TRIGGER get_entry_id
BEFORE INSERT ON entry
FOR EACH ROW
IS
BEGIN
SELECT entry_id_seq.nextval
INTO :new.entry_id
FROM dual;
END;
Your INSERT statement could then omit the ENTRY_ID column, letting the trigger automatically populate it.
INSERT INTO entry( name )
VALUES( :1 );
Comments
If you are happy with a database dependent way of doing this, then the usual approach is to use an oracle sequence.
After you create the sequence, which lives in the database your code would be along the lines of
p = conn.prepareStatement("insert into mytable (entry_id, name) values (mysequence.next_val,?)");
p.setString(1,"My Name");
p.executeUpdate();
I used the keywords "oracle" and "autoincrement". Found this: http://situsnya.wordpress.com/2008/09/02/how-to-create-auto-increment-columns-in-oracle/
By using the trigger in conjunction with the sequence, you don't need to include oracle-specific constructs into your insert, you simply leave the entry_id's value out of the explicit list of values in the insert.
