The setup:
==========
I have a 'countries' table that list ISO 3166 countries.

CREATE DOMAIN D_BOOLEAN AS SMALLINT;

CREATE TABLE countries (
id int not null primary key,
alpha2 char(2) unique,
name varchar(64) not null,
is_obsolete D_BOOLEAN not null
);

INSERT INTO countries VALUES (0,null,'Unknown country',0);
INSERT INTO countries VALUES (1,'TP','East Timor',0);
INSERT INTO countries VALUES (2,'CS','Czechoslovakia',0);

I have _various_ tables referring to the countries(alpha2). This is just
but one example:

CREATE TABLE addresses (
id int not null primary key,
line1 varchar(64) not null,
line2 varchar(64) not null,
city varchar(64) not null,
state varchar(64) not null,
country_code char(2) REFERENCES countries(alpha2)
ON UPDATE CASCADE
);


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.


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?

--
dave