[Bucardo-general] private key vs. unique index

[Joshua Tolley]

On Fri, Apr 15, 2011 at 11:56:48AM -0400, Marc Farnum Rendino wrote:
> So it's up to me how to add them - is it possible (not being a pg
> expert yet) to define the column such that the unique key is
> automatically added (and not null) as a row is created, regardless of
> how the row is created (or by whom)?

Defining columns as the SERIAL (or, if you expect more than 2 billion of them,
BIGSERIAL) pseudo-datatype is a common method:

    ALTER TABLE foo ADD bar SERIAL UNIQUE NOT NULL;

The SERIAL pseudo-types create a column with INTEGER or BIGINTEGER type along
with a sequence object, and then set the column's default value to that
sequence, which should suffice if you have well-behaved users. This doesn't
prevent users from trying to set the column to NULL and getting a not-null
constraint violation error in response, or from setting it to some value that
already exists and getting a uniqueness constraint violation. Perhaps most
irritating, it doesn't prevent them from setting it to some value that the
sequence will run into in the future, so someone else gets the uniqueness
constraint violation. Preventing that sort of thing would probably require a
trigger that explicitly set the column's value, and whether or not it's worth
worrying about presumably depends on your particular situation.

-- 
Josh Tolley   josh at endpoint.com   801-987-0252
End Point Corporation  http://www.endpoint.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 197 bytes
Desc: Digital signature
Url : https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20110415/8e0bd44d/attachment-0001.bin