You could:
INSERT INTO REGION VALUES (33, 'New Dar');
UPDATE DISTRICT SET region_id = 33 WHERE region_id = 99;
DELETE FROM REGION WHERE region_id = 99;
UPDATE REGION SET region_name = 'Dar es Salaam' WHERE region_id = 33;
Of course, if there is no uniqueness constraint on region_name then
you can just put the final region_name in the INSERT and you won't need
to do the final UPDATE.
This won't break any Foreign Keys.
(been to Dodoma and Dar, but not Tabora - yet).
Cheers,
Bob Edwards.
James Kitambara wrote:
Thank you !
But I think that there is a solution.
If it happens that you have the following data in your tables
REGION
--------------------------------------
region_id | region_name
--------------------------------------
11 | Dodoma
22 | Tabora
99 | Dar es Salaam THIS ROW WAS SUPPOSED TO BE: '33',
'Dar es Salaam'
DISTRICT
------------------------------------------------------------
dist_id | dist_name | region_id
------------------------------------------------------------
001 | Kongwa | 11
002 | Ilala | 99
003 | Temeke | 99
003 | Kinondoni | 99
For this UPDATE I wanted, when I change the region _id from '99' to '33'
of the last ROW in REGION table AUTOMATICALLY to change the last three
ROWS of the DISTRICT table which reference to '99', 'Dar es Salaam'.
If I do this, I will get the error message "You can not change region_id
other tables are reference to it.
HOW CAN DO THIS? (AUTOMATIC UPDATE OF FOREIGN KEY)
-----------------------------------ORGINAL
MESSAGE---------------------------------------
I think (one of) the point(s) of id fields is not to change them.
You can update the region_name field (eg a correct a misspelling),
but the id stays the same.
That way the district stays connected to the same region.
>>> James Kitambara <[EMAIL PROTECTED]> 2008-09-17 8:50 >>>
Hello Mambers of PGSQL-SQL,
I have two tables namely:
REGION (region_id, region_name)
DISTRICT (dist_id, dist_name, region_id (FK))
I would like to have the CREATE TABLE Command which will create
these tables in such a way that when REGION table is UPDATED
automatical the FOREGN KEY in DISTRICT table is also updated.
I will appriciate for your assistance !
Regards
James Kitambara
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
