Hi David,

> Problem #1
> ===========
> East Timor (TP) is now Timor-Leste (TL). I want to change all references
> of 'TP' to 'TL' in the other tables. But, I _don't_ want to do this to
> the 'countries' table:
>
> UPDATE countries SET alpha2='TL',name='Timor-Leste' WHERE alpha2='TP';
>
> Instead I want to do this:
>
> UPDATE countries SET is_obsolete=1 WHERE alpha2='TP';
> INSERT INTO countries VALUES (3,'TL','Timor-Leste',0);
>
> So obviously UPDATE CASCADE clause in FK declaration doesn't help here.
>
> Question: is the a better way than to manually update all the other
> tables? Remember, I have FK to countries(alpha2) in various tables.

Well, what you _could_ do - is doing things the other way around.

- update COUNTRIES set ALPHA2 = 'TL'
- insert a new entry (basically, copying the "old" record) with alpha2='TP'
and so on.

>
> Problem #2
> ===========
> Czechoslovakia (CS) is now split into two: Czech (CZ) and Slovakia (SK).
> The twist is, CS is now reused by another country: Serbia and
> Montenegro. So I want to do this to the 'countries' table:
>
> UPDATE countries SET alpha2=NULL,is_obsolete=1 WHERE alpha2='CS';
> INSERT INTO countries VALUES (4,'CZ','Czech',0);
> INSERT INTO countries VALUES (5,'SK','Slovakia',0);
> INSERT INTO countries VALUES (6,'CS','Serbia and Montenegro',0);
>
> Setting old 'CS' record to null seems to be the correct way for me. Old
> addresses in Czechlovakia are now in unknown country (some of them might
> be in CZ now, some of them in SK, I can't tell which is which unless I
> parse cities/states). The addresses are certainly not in 'CS' anymore,
> since CS means a different country.
>
> The problem is that I can't seem to set 'CS' to null due to foreign key
> constraint.
>
> Question: Am I correct that FK value cannot be null? If yes, then again,
> is there a better way than to manually update all the other tables?

Wrong: FK columns can be NULL, but only if you created the columns
as NULLable.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com