Martijn Tonies wrote:

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

Good idea, thanks!

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

Actually I did. Both countries.ALPHA2 and addresses.COUNTRY_CODE are
nullable:



SQL> show table countries;
ID INTEGER Not Null
ALPHA2 CHAR(2) CHARACTER SET OCTETS Nullable
NAME VARCHAR(64) CHARACTER SET OCTETS Not Null
IS_OBSOLETE (D_BOOLEAN) SMALLINT Not Null
CONSTRAINT INTEG_143:
Primary key (ID)
CONSTRAINT INTEG_144:
Unique key (ALPHA2)

SQL> show table addresses;
ID INTEGER Not Null
LINE1 VARCHAR(64) CHARACTER SET OCTETS Not Null
LINE2 VARCHAR(64) CHARACTER SET OCTETS Not Null
CITY VARCHAR(64) CHARACTER SET OCTETS Not Null
STATE VARCHAR(64) CHARACTER SET OCTETS Not Null
COUNTRY_CODE CHAR(2) CHARACTER SET OCTETS Nullable
CONSTRAINT INTEG_153:
Foreign key (COUNTRY_CODE) References COUNTRIES (ALPHA2) On Update
Set Null

CONSTRAINT INTEG_148:
Primary key (ID)



but isql still won't let me update ALPHA2 to null.



SQL> delete from addresses;
SQL> delete from countries;
SQL> commit;

SQL> insert into countries values (1,'CS','Czechoslovakia',0);
SQL> insert into addresses values (1,'l1','l2','c','st','CS');

SQL> update countries set alpha2=null;
Statement failed, SQLCODE = -530

violation of FOREIGN KEY constraint "INTEG_153" on table "ADDRESSES"



Something I left out or did wrong?

--
dave