Hi,

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

Here you have a SET NULL FK constraint, not a CASCADE?

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

Not that I can think of. Can you update to another existing value?

With regards,

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