Thread: Duplicate values when using same sequence to generate columns across two different tables

Aditya Rastogi, 27.11.2013 10:00:
> I am using the same sequence to auto-generate values for columns in
> two different tables. If multiple parallel sessions are inserting
> values in either of the tables, is there a chance that there might
> occur entries with the same integer in the two tables or in one of
> the tables?

No, absolutely not.

Thomas Kellerer wrote
> Aditya Rastogi, 27.11.2013 10:00:
>> I am using the same sequence to auto-generate values for columns in
>> two different tables. If multiple parallel sessions are inserting
>> values in either of the tables, is there a chance that there might
>> occur entries with the same integer in the two tables or in one of
>> the tables?
>
> No, absolutely not.

While strictly true usually duplicates within a table are prevented via a
unique index on the serial column - but that doesn't work in this case -
though it will prevent duplicates in the same table.  The problem is that
even if you define a sequence and use it as a default it is possible for
someone to manually specify a value instead - ignoring, and skipping the use
of, the sequence.  That may or may not be a concern but it can happen and in
absence of a constraint preventing such you could end up with
cross-table-duplicates.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Duplicate-values-when-using-same-sequence-to-generate-columns-across-two-different-tables-tp5780509p5780603.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.