"Matthias Meyerh?fer" <matthias.meyerhoefer@dpd.de>

If t2 has got its own serial field, u will always run into this problem.

why the second table t2 got to be a serial field. Why can't you declare
it as an integer field with both PKY as well as FKY to t1.

The simple answer is that you can't.

Ok...

First, why do you have a serial column in t2?
Are you ever going to insert in to t2 without first inserting in t1?
And is all the data being inserted in to t2 going to first come from t1?
And how is it that you have data in t2 that is not in t1?

A serial field has a wrap around after 4 billion or so records. (Or
something like that number if not larger, so that you wont get an
automatic duplicate record.)

The only way you can have an error is if you abend (abort/core dump) in
the middle of a process. Or you're adding data from other sources other
than t1.

Since your problem does not indicate that you are adding records
directly in to T2 without the data first being in T1, you don't need a
serial column. Just an unsigned int and a unique index, which will
prohibit duplicates.

Are the copy to table T2 and delete from T1 supposed to be an atomic
sequence? Meaning that you want them to occur within the same
transaction? This would imply that you only want to store the row within
one table.

An example of this would be in a store's point of sale machine. You
enter the daily transactions in to one table, then at end of day you
reconcile the data, then after the reconcile process, you move the sales
data to a historical table and delete from the  day's total.  (Not a
great example, but it would explain the insert, then delete....)

The simple solution is that when you attempt the insert in to t2, you
trap the return code and upon failure, you delete from t2 and re-insert
the record.

Does that help?

Matthias Meyerhöfer wrote:

Best way to do this is to not try.

I'd suggest getting on 9.4 and use the sequence generator instead.

M.P.

Hello,

thank you so much for your help. I see that there is no chance to solve the
problem on database site. So the source code must be changed.

thank you and best regards
Matthias