Hi,

> > Sure you can. You can even have an RI action
> > ON DELETE SET NULL
> > to return the dependent row to a "pool of orphans" when the master row

gets

> > deleted.
>
> I know about ON DELETE SET NULL.
> I think I did not make myself perfectly clear - I think "nullable" was
> not the correct word for what I mean.
>
> I would like to have a foreign key field that can be NULL - and when
> it is NULL, no RI should take place.
> But when the value is different then NULL, RI should take place and
> deletes and updates should *not* be possible - i. e.
> ON UPDATE NO ACTION and ON DELETE NO ACTION.
> But a foreign key field defined like that does not accept NULLs.
> This is my problem ;-)

Sure it does. If you define it as NULLable :-)

eg:

PERSON
--
Name
Last_Name
CarID

Where "car" is a FK to table CARS. CarID should be nullable.

CARS
--
CarID
Name
Brand
etc...

(obviously, this design is... well, sub-optimal, but it works)

With regards,

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