No Nulls allowed for FK in Access?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave - 05 Nov 2005 19:05 GMT

When I specify "Enforce Referential Integrity" on a table relationship in MS
Access 2003 it fails saying that the child table FK cannot be NULL.

SQL Server allows NULL FKs.

Is there any way to enforce referential integrity in MS Access and still
allow NULLs in the FK?

Reply to this Message

Rob Oldfield - 05 Nov 2005 19:25 GMT

Try using the Join Type button.

> When I specify "Enforce Referential Integrity" on a table relationship in MS
> Access 2003 it fails saying that the child table FK cannot be NULL.
[quoted text clipped - 3 lines]
> Is there any way to enforce referential integrity in MS Access and still
> allow NULLs in the FK?

Reply to this Message

Pat Hartman(MVP) - 05 Nov 2005 20:36 GMT

Foreign keys may be null but primary keys may not. Make sure the pk's are
defined properly for both tables and make sure the relationship is properly
defined. In order for foreign keys to be null, you need to remove the
automatic default of 0 which is what Access assumes when you define a
numeric field. Make sure the required property is set to No.

> When I specify "Enforce Referential Integrity" on a table relationship in
> MS Access 2003 it fails saying that the child table FK cannot be NULL.
[quoted text clipped - 3 lines]
> Is there any way to enforce referential integrity in MS Access and still
> allow NULLs in the FK?

Reply to this Message