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