Thread: May I have an assistance on CREATE TABLE Command

James Kitambara wrote:
> 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)

When you define your foreign key mark it "ON UPDATE CASCADE" (there is a
similar option for ON DELETE). See manuals for details.

--  Richard Huxton Archonet Ltd

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