You could: INSERT INTO REGION VALUES (33, 'New Dar'); UPDATE DISTRICT SET region_id = 33 WHERE region_id = 99; DELETE
FROMREGION 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 <jameskitambara@yahoo.co.uk> 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
>
>