Re: deferring/disabling unique index

Basically, swapping values of columns involved in a unique index causes
the problem.

Example:

I wrote a synchronization script that syncs data between multiple
databases. It retrieves primary key information from the system tables,
joins remote tables and updates corresponding values.

Suppose I have a table:

Employee (FirstName, LastName, id)
PrimaryKey: id
UniqueIndex: FirstName, LastName

Suppose on each database instance this table contains two records:
Jane Doe 1
Jane Smith 2

Now, suppose we swap the last names between the two emplyees on one
instance, so we end up with:
Jane Smith 1
Jane Doe 2

Now, I want to propagate this data to another database instance and run
this query:

UPDATE Employee1
SET LastName=e2.LastName
FROM Employee2 e2
WHERE Employee1.id = e2.id;

In the above query Employee1 is the Employee table from the first DB
instance and Employee2 - from the second DB instance.

The query will throw an error saying that it UniqueIndex is violated
when assigning last name Doe to employee with id 1.

Thanks.

Oleg

-----Original Message-----
From: Joshua D. Drake [mailto:jd(at)commandprompt(dot)com]
Sent: Friday, January 09, 2004 10:37 AM
To: Oleg Lebedev
Cc: Bruce Momjian; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] deferring/disabling unique index

>So, does it mean that the only way to disable the index is to drop and
>recreate it? What about setting indisunique to false temporarily?
>
>
>
I am just curious... why would you want to defer a unique constraint?

Sincerely,

Joshua Drake

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd(at)commandprompt(dot)com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

*************************************

This e-mail may contain privileged or confidential material intended for the named recipient only.
If you are not the named recipient, delete this message and all attachments.
Unauthorized reviewing, copying, printing, disclosing, or otherwise using information in this e-mail is prohibited.
We reserve the right to monitor e-mail sent through our network.

*************************************