Primary key values as a result of a Sum

[B Ortet]

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

[Tim Holloway]

"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]

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

[Campbell Ritchie]

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

[B Ortet]

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]

If you separate the two parts by fullstops, there won't be any such collisions.

[B Ortet]

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]

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

int valor = daoContaBancaria.getNumeroMaximoContaAPrazo(numeroContaCorrente);
		if(valor == 0)
			numeroContaMax = numeroContaCorrente * 1000  + 1;
		else {
			numeroContaMax = valor + 1;
		}
		contaAPrazo.setNumeroConta(numeroContaMax);

[Dave Tolls]

I take it no other threads/users are going to access the database?

[B Ortet]

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]

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]

int valor = daoContaBancaria.getNumeroMaximoContaAPrazo(numeroContaCorrente);

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]

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