Hi all,

Is there a way to swap primary keys in two rows? I have a table like

CREATE TABLE MAILBOX (
MAILBOX_ID BIGINT NOT NULL,
OWNER BIGINT NOT NULL,
MAILBOX_TYPE VARCHAR(10),

PRIMARY KEY (MAILBOX_ID),
UNIQUE (OWNER, MAILBOX_TYPE)
)
;

Most of the time the MAILBOX_ID will be auto-assigned in an INSERT
trigger, but we now need a way to set the ID for some of the rows to a
specific (still unique) value. If the value doesn't yet exist, no
problem of course, but if it exists, I'd like to swap it with the
existing value, or set the existing value to a new ID from a
generator. How would I do this in an UPDATE trigger? Will other tables
that have a FK to MAILBOX_ID still CASCADE properly?

Thanks,
Emiliano