• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Bear Bibeault
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Jj Roberts
  • Carey Brown
Bartenders:
  • salvin francis
  • Frits Walraven
  • Piet Souris

Primary key values as a result of a Sum

 
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi guys, is it possible to have a primary key in a table, which value, is the sum of the foreign key + the value of the auto_increment??
 
Saloon Keeper
Posts: 22989
156
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"The" foreigh key, Bruno?

I've had tables with 3 or 4 foreign keys in them.

Technically, you can construct your primary key anyway you like, as long as it's unique. But a foreign key's value can change whereas a primary key's value cannot, so while you could use a foreign key's value (or any column(s) value(s)) to construct a primary key, once the record has been persisted to the database, that's going to be the one and only primary key value.
 
B Ortet
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For example:

CurrentAccount(
idCurrentAccount int primary key auto_increment;
);

SavingAccount(
idSavinAccount int primary key auto_increment,
idCurrentAccount int not null,
foreign key (idCurrentAccount)
references CurrentAccount(id)
);

What i trying to see is if I can make that, when a new entry in the SavingAccount, the primaryKey of the SavingAccount could be:
idCurrentAccount = 1
idSavingAccount = idCurrentAccount + auto_increment.
idSavinAccount = 1001

And when a idCurrentAccount = 2, register a SavingAccount, it would have the primary Key 2001.
And if he register another SavingAccount it would have the idSavingAccount = 2002.

Does it make sense??
 
Marshal
Posts: 71620
312
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes,, but I can see a problem. You are risking a collision of keys every 1001 new accounts (or worse).
 
B Ortet
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Campbell Ritchie wrote:Yes,, but I can see a problem. You are risking a collision of keys every 1001 new accounts (or worse).


where?
the idCurrentAccount will never repeat. Each of the new CurrentAccount will have a diferent value.
Imagine the idCurrentAccount = 1
All the dependent account will be:
1.001
1.002
1.003
...
1.999
1.1001
1.1002
(...)
2.001
2.002
2.003
...
2.999
2.1001
 
Campbell Ritchie
Marshal
Posts: 71620
312
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you separate the two parts by fullstops, there won't be any such collisions.
 
B Ortet
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
the full stop was just to separate the numbers visually.
But i think is not possible.

Only if i control the idSavingAccount in he application layer, with a static member in the class SavingAccount
 
B Ortet
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey guys, I managed to make it work with java code.
When im going to insert a new row in a database, i query for the last item and add the numbers

 
Rancher
Posts: 4775
50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I take it no other threads/users are going to access the database?
 
B Ortet
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, other users will acess the database, using and interface. When creating a new object, a method will query the database to get the last account number entered and increment uppon that to create the new object, ande then insert it to the database...
Only now I've seen the I posted the code in portuguese...
 
Dave Tolls
Rancher
Posts: 4775
50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Which means the above code won't work in a multi-threaded environment.
You can easily have two users hitting that code at the same time, resulting in the same number being created.
 
B Ortet
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator




This will query for a specific CheckingAccount, and will return the number of the last associated account related CheckingAccount number.
Unless the same account owner is trying to open an account in 2 diferent places, there should be no problem, i guess...

For instance: An CheckingAccount number 2. When the owner wants to create a SavingAccount related to that account number 2, before inserting the entry in the database,
the method will query to see the max value of all the accounst associated with 2, and return the value.
If and CheckingAccountNumerber = 3, wants to do the same in the same time,  the method will query for the accountNumber = 3, them return the value related to the accountNumber 3.
 
B Ortet
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well I belive I was wrong. The original ideia for this post was in the fact that i wanted to Link the table Customer with the CheckingAccount, which would resulted in a third table, Customer_CheckingAccount.
And the table CheckingAccount have a relationship of one to many with the TermAccount.
The problem appear when I tought that were possible to Link a Customer to the TermAccount without linking to the CheckingAccount.  Apparently it's not how it works in the banking world.
But it was a mistake of my part. It was a design problem of the database
 
Yes, my master! Here is the tiny ad you asked for:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic