Managing "Row ID" for Table Inserts

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
Sun Jan 01 08:03:26 CST 2006

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" <JoeReggae@JoeReggae.org> wrote in message
news:11rdevups8pri8c@corp.supernews.com...
> 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.
>
>

Joe
Sun Jan 01 08:54:39 CST 2006

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
Mon Jan 02 08:31:23 CST 2006

On Sun, 1 Jan 2006 05:54:39 -0900, "Joe Reggae"
<JoeReggae@JoeReggae.org> wrote:

>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
Mon Jan 02 10:27:38 CST 2006

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.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn.com www.cindywinegarden.com


"Joe Reggae" <JoeReggae@JoeReggae.org> wrote in message
news:11rdevups8pri8c@corp.supernews.com...
> ....I pull the "max" ID value from a table, add 1 to it, and then use that
> number as the new "row ID" ...

Joe
Mon Jan 02 12:56:22 CST 2006

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

brianharris
Tue Jan 03 10:16:02 CST 2006

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.



"Joe Reggae" wrote:

> 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.
>
>
>