Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.java.programmer > #38076
| From | Arne Vajhøj <arne@vajhoej.dk> |
|---|---|
| Newsgroups | comp.lang.java.programmer |
| Subject | Re: programming...? |
| Date | 2018-02-21 21:35 -0500 |
| Organization | Aioe.org NNTP Server |
| Message-ID | <p6la8m$1ete$1@gioia.aioe.org> (permalink) |
| References | (7 earlier) <p6k449$8ma$1@news.albasani.net> <369426df-6367-4040-9799-dcf91f2bb1ba@googlegroups.com> <p6kalc$psr$1@news.albasani.net> <0cf6b78f-a709-4325-93f0-3012aedff160@googlegroups.com> <p6kneq$tce$1@news.albasani.net> |
On 2/21/2018 4:14 PM, Kiwi User wrote:
> On Wed, 21 Feb 2018 10:43:41 -0800, Eric Douglas wrote:
>
>> Interesting, both open source, I'll have to check them out. Usually I
>> see businesses advertise for MS SQL Server or Oracle SQL, both paid
>> software.
>>
> My opinion about PostgreSQL sequences vs MySQL/MariaDB autoincrement is
> purely personal prejudice:
>
> - PostgreSQL: Each time you read a sequence you get the next
> sequential value.
>
> - MySQL: Each time you store a row with an autoincrementing field that
> field is set to the next sequential value.
>
> It's obvious that each sequence generates its own set of values because
> you can have many of them in a schema, but I don't know/can't remember if
> there is a separate autoincrementing source for each autoincrementing int
> in the schema or whether they all feed off an common source.
It is per field/table (since there can only be one such field
in a table then per field and per table is the same).
> Both approaches work well for generating soft DB keys, which by
> definition must be unique within a table, if you're using them as (part
> of) a prime key. Reasons for doing that range from the need to store
> entities that have no guaranteed unique natural key to data compression,
> which can be a valid approach if the natural key is rather long, e.g. the
> name of a classical music work which can be up to 60 characters long.
>
> My preference is for clarity: its obvious where the PK value comes from
> if there's (pseudocode) statements like this:
>
> newDBK = sequence.value
> insert (newDBK, musicTitle) in titleTable
> for all parts of work
> insert (newDBK, partno, movementTitle) in partWorkTable.
Yes.
> while to use an autoincrementing int you have to do this:
>
> insert (musicTitle in titleTable)
> newDBK = select max(dbk) from titleTable
> for all parts of work
> insert (newDBK, partno, movementTitle) in partWorkTable.
That is not how you do it. This is a very bad way of doing it.
At a low transaction isolation level this logic is not
concurrency safe.
And a high transaction isolation level impact performance.
The correct way is:
insert (musicTitle in titleTable)
newDBK = function_last_autogenerated_key_on_connection()
for all parts of work
insert (newDBK, partno, movementTitle) in partWorkTable.
MySQL has the function as LAST_INSERT_ID()
MS SQLServer has the function as SCOPE_IDENTiTY()
IBM DB2 has the function IDENTITY_VAL_LOCAL()
JDBC can do it database independent via Statement.getGeneratedKeys()
Arne
Back to comp.lang.java.programmer | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread
programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-20 07:38 -0800
Re: programming...? Jon <jon101011@yahoo.com> - 2018-02-20 17:07 +0100
Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-20 08:19 -0800
Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-20 11:45 -0500
Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-20 12:39 -0800
Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-20 13:25 -0800
Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-20 14:00 -0800
Re: programming...? Daniele Futtorovic <da.futt.news@laposte.net> - 2018-02-21 00:31 +0100
Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-20 19:24 -0500
Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-20 19:21 -0500
Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-20 11:41 -0500
Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-20 19:37 +0000
Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-20 12:10 -0800
Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-20 22:00 +0000
Re: programming...? Robert Spanjaard <spamtrap@arumes.com> - 2018-02-20 21:09 +0100
Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-20 12:16 -0800
Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-20 22:09 +0000
Re: programming...? Robert Spanjaard <spamtrap@arumes.com> - 2018-02-21 01:16 +0100
Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-21 00:47 +0000
Re: programming...? Robert Spanjaard <spamtrap@arumes.com> - 2018-02-21 12:29 +0100
Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-21 10:42 -0500
Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-21 15:44 +0000
Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-21 11:03 -0500
Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-21 08:38 -0800
Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-21 11:54 -0500
Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-21 10:40 -0800
Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-21 14:35 -0500
Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-21 11:48 -0800
Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-21 08:29 -0800
Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-21 11:47 -0500
Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-21 17:35 +0000
Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-21 12:47 -0500
Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-21 10:45 -0800
Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-21 14:40 -0500
Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-21 21:23 +0000
Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-21 21:39 -0500
Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-22 09:06 +0000
Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-24 20:29 -0500
Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-25 02:12 +0000
Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-26 08:19 -0800
Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-03-01 13:17 -0500
Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-21 10:43 -0800
Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-21 21:14 +0000
Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-21 21:35 -0500
Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-22 09:10 +0000
Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-22 06:23 -0800
Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-22 19:32 -0500
csiph-web