I am migrating data from an older GIS system into an SDE system with PostgreSQL as the database engine. The current system generates a unique tag for several layers, I want to re-create this behavior using sequences in the PostgreSQL database. I have it working in a single user environment, but will it work in a versioned environment? Right now the field is populated by using Postgres' nextval() function as the field's default value. When the tables are versioned do those defaults go with them?
I made some tests on a similar topic using Oracle, and it was not a real success. I played with the base table and the A table, adding an auto-generated unique key. It is important to stress that the uniqueness was enforced by a constraint.
I faced two issues (from the top of my head, it's a bit old): One that could be overcome, that made new records created in ArcMap have a Null value instead of the default sequence value, and saving this record would fail.
The second issue was more perverse, as it would occurred when duplicating a record. The current value would also be duplicated, creating a unique constraint error.
The only way I found at that time to have an independent sequence with a unique key was to make use of Oracle hidden column with an identity column. This new column would not be visible in ArcMap and was ignored when creating a new record. Of course ESRI said they didn't support it and therefore didn't recommend using it.