Managing "Row ID" for Table Inserts

[Joe Reggae]

Using c# and Visual Studio 2003, I've begun to wonder whether I'm adding
rows to Oracle tables correctly. The tables often have "row ID" columns and,
before adding a row, I pull the "max" ID value from a table, add 1 to it,
and then use that number as the new "row ID" (the base table then gets
updated, so the dataset and table are not long out of sync). I'm sure this
approach is wrong, since, among other issues, the "max" ID returned could be
wrong if, for instance, a recently-added row was deleted. I don't know,
however, how else to do this. Should I instead use stored procedures? How
can I add rows to my dataset tables and know what "row ID" the underlying
table will expect during the row insert (especially with other users
potentially hitting the database)? Thanks for advice.

 

[Earl]

You might want to ask this in an Oracle newsgroup. On one hand, it sounds
like "rowID" is an identity column, on the other hand, it sounds like it is
not an autoincrement identity. Not having any Oracle experience, I can only
say that users of SQL would likely use an identity auto increment and then
@@IDENTITY to retrieve the last added record (you wouldn't insert the rowID
as it would be created for you during the insert). My preference is for
stored procedures, but that is a religious topic that has been beat to death
(as has the concept of "natural" key vs "identity" or surrogate key).

 

[Joe Reggae]

Thanks for the suggestion. I should have pointed out in my post that, yes,
the Oracle "row ID" columns do auto-increment as a result of Oracle insert
triggers. Often the columns are a table's primary key and I therefore select
them into my local dataset. Once a row is added locally I haven't been able
to insert it back to Oracle without getting a constraint error. It occurs to
me now, though, that maybe all I need to do is leave out the "row ID" column
from the insert and the Oracle trigger will run fine. I'll try that.

 

[Otis Mukinfus]

Thanks for the suggestion. I should have pointed out in my post that, yes,
the Oracle "row ID" columns do auto-increment as a result of Oracle insert
triggers. Often the columns are a table's primary key and I therefore select
them into my local dataset. Once a row is added locally I haven't been able
to insert it back to Oracle without getting a constraint error. It occurs to
me now, though, that maybe all I need to do is leave out the "row ID" column
from the insert and the Oracle trigger will run fine. I'll try that.

Hi, Joe.

Yes, you will need to make sure you don't reinsert the row id. In
that respect, there is no difference between Oracle and SQL Server.
Both will throw a constarint violation error.

 

[Cindy Winegarden]

Hi Joe,

The others in this thread have told you how to work with an Identity type
column. Even if you wanted to assign your own primary keys, I'd like to
point out that getting the largest value, incrementing it and using that as
the new PK will never work in a multi-user context. Unless there's some way
to lock the table, it's too easy for someone else to insert a row at the
same time and attempt to use the same value you're using.

 

[Joe Reggae]

Right. I've gotten away with this because I have a small "user base" ...
just three people!

 

[Guest]

In oracle you do not want to place a value for the rowid. This is done
automatically when oracle inserts the record. every record has a unique row
id that is based on its location with in the database. So you NEVER want to
change its value. You may want to use it in updates and selects to make sure
that you are dealing with the correct row if you have multiple non unique
rows in your statement. Rowid is an internal item on each row.