Обсуждение: Merging item codes using referential integrity

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.

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.

On Mon, Mar 28, 2005 at 09:41:03AM -0600, Thomas F.O'Connell wrote:

> 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.

I think he's trying to exploit ON UPDATE CASCADE so he can execute
a single UPDATE on the referenced table instead of having to write
multiple UPDATE statements, one for each referencing table.  The
problem is that he's trying to change the referenced value from 2
to 1 but 1 already exists, so he gets a duplicate key error.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

> I think he's trying to exploit ON UPDATE CASCADE so he can execute
> a single UPDATE on the referenced table instead of having to write
> multiple UPDATE statements, one for each referencing table.  The
> problem is that he's trying to change the referenced value from 2
> to 1 but 1 already exists, so he gets a duplicate key error.

Yes, this is exactly want I want.
I want to use the referential integrity information to merge two different
foreign keys into single key.

It seems that ON UPDATE CASCADE feature does'nt work in this case since it
produces duplicate primary keys for a while.
So I'm researching the following method:

Input:

Master table name $master and two its primary key values  $value1 and
$value2

Output:

1. All $value2 field values in child tables are update to $value1
2. $value2 record is deleted from $master table

Algorithm:

SELECT
  childtablename,
  childfieldname
FROM pg_referentialinfo
WHERE pg_referentialinfo.mastertable=$master
INTO CURSOR childs;

BEGIN TRANSACTION;
SCAN FOR ALL childs RECORDS;
  UPDATE  (childs.childtablename)  set (childs.childfieldname)=$value2
       WHERE EVAL(childs.childfieldname)=$value1;
ENDSCAN;

SELECT
  primarykeyfieldname
FROM pg_tables
WHERE pg_tables.tablename=$master
INTO CURSOR mfield;

DELETE FROM $master WHERE EVAL(mfield.primarykeyfieldname)=$value2;
COMMIT;


Questions:

1. Is this algorithm best way to do it ?
2. Are there any sample postgres procedure for this?
3. Shoul I implement SCAN FOR ALL childs RECORDS in client or is it possible
to write this in stored procedure ?
4. What is the format of the two selects which select data from postgres
system tables ?
5. What are actual names for tables pg_referentialinfo and pg_tables  used
?

Andrus.