No question at this time
DBA Top 10
1 A. Kavsek 12200
2 M. Cadot 7000
3 P. Wisse 5600
4 B. Vroman 5400
5 J. PĂ©ran 1900
6 . Lauri 1500
7 T. P 500
7 R. Pattyn 500
9 T. Boles 400
10 A. Chavan 300
10 J. Schnackenberg 300
10 D. Johnson 300
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48454
Total active users1499
Act. users last 24h5
Act. users last hour0
Registered user hits last week216
Registered user hits last month950
Go up

Negative numbers in primary key column
Next thread: Cleaning archived redo logs from standby site using RMAN
Prev thread: Oracle12c Upgrade from Oracle11g 11.2.0.4

Message Score Author Date
What can be the negatives of using a negative numb...... Nishant Kariya Nov 04, 2019, 09:10
Hi Nishant Kariya, Oracle just needs a unique n...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Philip Wisse Nov 04, 2019, 09:40
>>> <i> What can be the negatives of using a neg...... Score: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 Pts Michel Cadot Nov 04, 2019, 09:46
Same was mine answer. I agree with you completely....... Nishant Kariya Nov 04, 2019, 10:07
I am curious to understand that then why people do...... Nishant Kariya Nov 04, 2019, 10:10
Hello Nishant, using the "MAX + 1" is an awful ...... Score: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 Pts Bruno Vroman Nov 04, 2019, 13:00
((oops, of course a typo... do NOT read <I>If a ...... Bruno Vroman Nov 04, 2019, 13:03

Follow up by mail Click here


Subject: Negative numbers in primary key column
Author: Nishant Kariya, India
Date: Nov 04, 2019, 09:10, 15 days ago
Oracle info: 12c
Message: What can be the negatives of using a negative number in a primary key number column.

There is one old application that populates data to target tables via ETL and uses MAX+1 to generate surrogate id's in key column.

Now some another process is developed and need to append data in same target table and both process can run in parallel. This was also create surrogate key's in same column.

So if both process will use MAX+1 then there is a possible chance of clash.

Somehow we need to setup a dependency on both processes so that both do not initiate at same time.

Then someone suggested to use negative numbers as key in 2nd process, which could prevent clash. I don't see this as a good practise and would like to understand all negatives of it.



Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Negative numbers in primary key column
Author: Philip Wisse, Netherlands
Date: Nov 04, 2019, 09:40, 15 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message: Hi Nishant Kariya,

Oracle just needs a unique number.
There are no drawbacks against using negative numbers.
I think using negative numbers is a good idea!

HTH, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Negative numbers in primary key column
Author: Michel Cadot, France
Date: Nov 04, 2019, 09:46, 15 days ago
Score:   Score: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 Pts
Message:
>>> What can be the negatives of using a negative number in a primary key number column

None... if the application code does not rely on a positive key number.

>>> There is one old application that populates data to target tables via ETL and uses MAX+1 to generate surrogate id's in key column.

This is a very bad thing, bad coding... will most likely lead to application error and data corruption.

>>> So if both process will use MAX+1 then there is a possible chance of clash.

Most likely unless all applications use an exclusive table lock before handling data, that is logically run in a single user/session environment.

>>> Somehow we need to setup a dependency on both processes so that both do not initiate at same time.

The table exclusive lock I mentioned.

>>> Then someone suggested to use negative numbers as key in 2nd process, which could prevent clash. I don't see this as a good practise and would like to understand all negatives of it.

And if a third application comes which kind of number will you use? An imaginary number? ;)

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Negative numbers in primary key column
Author: Nishant Kariya, India
Date: Nov 04, 2019, 10:07, 15 days ago
Message: Same was mine answer. I agree with you completely.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Negative numbers in primary key column
Author: Nishant Kariya, India
Date: Nov 04, 2019, 10:10, 15 days ago
Message: I am curious to understand that then why people don't start their surrogate ids from -1......

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Negative numbers in primary key column
Author: Bruno Vroman, Belgium
Date: Nov 04, 2019, 13:00, 15 days ago
Score:   Score: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 Pts
Message: Hello Nishant,

using the "MAX + 1" is an awful idea that should be abandoned (forget about "running in //" with such an idea!)

The best would be to use a sequence (and the IDs can even be generated automatically in the table definition)

If you need to make a distinction for the rows generated by process A and the ones coming from process B, then you might use various sequences, with a starting point high enough to avoid problems with existing data.
Then what about for example:
process A uses seqA that is: start with 12345, step 2
process B uses seqB that is: start with 12346, step 2
and for IDs >= 12345 you will even know easily if a row comes from A or from B.
But if the distinction is not mandatory, using a single sequence used by both processes would be perfect... start with 12345, step 1.

If the idea "we have to know the originating process" is important for you, then just a bit generalization, to take care of maybe N potential processes inserting data...
If we imagine that we might have between 2 and 10 processes, we might use:
process A: start with 123451, step 10
process B: start with 123452, step 10
process C: start with 123453, step 10
(I imagine that today we have 3 processes to distinguish)
If a fourth process is coming, it will just have to use a sequence starting at 123454 with a step of 4.
And so on, perfect as long as you have less than 10 processes.
Would you like more, then we just have to use a step of 100...
And when we see an id, for example 2345672, we know, thanks to the final "2", that this comes from process 2.

One more time: a unique sequence shared by all processes is probably better, and investigate the "generated as identity" features, you might be surprised of the facility...

(for info: many many years ago I had to use something like this (multiple sequences) because we were working with N "national databases" generating their own IDs and the data had to be consolidated in a "common database" (consolidation done via an external application) where we didn't want collisions. So we had for example:
all IDs ending with 01 coming from Germany, all IDs ending with 02 coming from Belgium, 03 for UK, ..., 09 France, 10 Danmark, 11 Norway, 12 Italy and so on... between 15 and 20 countries)

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Negative numbers in primary key column
Author: Bruno Vroman, Belgium
Date: Nov 04, 2019, 13:03, 15 days ago
Message: ((oops, of course a typo...
do NOT read
If a fourth process is coming, it will just have to use a sequence starting at 123454 with a step of 4.
but read
If a fourth process is coming, it will just have to use a sequence starting at 123454 with a step of 10.
))
Bruno
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here