If you declare parent.code to be a primary key, you're asserting that
you want it to be unique across all rows in parent. Thus, you will only
ever (be able to) have a single row with a value of 1.
If you do this:
INSERT INTO parent VALUES ('1');
INSERT INTO parent VALUES ('2');
UPDATE parent SET code='1' WHERE code='2';
then the UPDATE will clearly fail because you are trying to create an
additional record with a value of 1 when there already exists a row
with a value of 1 in the column that has been established as a primary
key.
I've only been explaining general database theory and the rules of SQL
in response to your posts because I'm still having a difficult time
understanding what you're trying to accomplish.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source — Open Your i™
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Mar 25, 2005, at 1:23 PM, Andrus Moor wrote:
> I have item table and many child tables where the items are used.
>
> I want to merge two item codes into single item in all tables.
> It is not nice to write a lot of separate UPDATE statements for each
> table.
> So I want to utilize REFERENCES clause for merging.
>
> I tried the following code but got duplicate key error in UPDATE
> statement.
>
> Any idea how to impement this?
>
> CREATE TABLE parent ( code CHAR(10) PRIMARY KEY );
>
> CREATE TABLE orders ( anyfield CHAR(10) REFERENCES parent ON UPDATE
> CASCADE );
> CREATE TABLE invoices ( anyotherfield CHAR(10) REFERENCES parent ON
> UPDATE
> CASCADE );
> -- ... a lot of more child tables with different table and field names
> but -- always with same REFERENCES clause.
>
> INSERT INTO parent VALUES ('1');
> INSERT INTO parent VALUES ('2');
> INSERT INTO orders VALUES ('1');
> INSERT INTO invoices VALUES ('1');
> INSERT INTO orders VALUES ('2');
> INSERT INTO invoices VALUES ('2');
>
> BEGIN;
> -- Direct Postgres to update all child tables. This causes error.
> UPDATE parent SET code='1' WHERE code='2';
> -- Remove duplicate row
> CREATE TABLE parent AS
> SELECT * FROM parent
> GROUP BY CODE ;
> COMMIT;
>
> Andrus.