1

There are two tables in a Microsoft Access database. One of them defines elements, the other one references them. This reference is however optional. The value can be NULL. As long as the foreign key exists, I am not allowed to insert or update the NULL value. After saving the NULL value without the foreign key, I cannot add the key.

The reference column is set to "Required = false" and NULL values are allowed on its own. Only the referential integrity cannot handle NULL values.

How can that be solved?

Update: Something possibly relevant came to my mind: My foreign key uses two columns. One of them is always set but the other can be null.

Added sample schema:

Table bus:
Columns:
• objid integer
• busnumber integer
• name varchar
Primary key: objid, busnumber

Table conn:
Columns:
• objid integer
• name varchar
• bus1 integer
• bus2 integer
Primary key: objid, name

conn (objid, bus1) is referencing to bus (objid, busnumber) as 1:n and
conn (objid, bus2) is referencing to bus (objid, busnumber) as 1:n

Both references are optional, but objid is part of a superior object and is always set.

ygoe
  • 18,655
  • 23
  • 113
  • 210
  • Please post sample data from both tables indicating which columns are used as indexes and foreign keys. Of course it is relevant that you have a combined key. – Fionnuala Jan 21 '13 at 14:39
  • @Remou: I have added a sample schema description above. – ygoe Jan 21 '13 at 16:17
  • varchar? You are using an MS Access(Jet/ACE) back-end or SQL Server? – Fionnuala Jan 21 '13 at 16:21
  • It's text. I'm not used to Access data types, I'm more regularly at home on MySQL or SQLite. – ygoe Jan 21 '13 at 16:44
  • I do not see how this can work, as it stands. All I can suggest is that you remove referential integrity and validate via a form. – Fionnuala Jan 21 '13 at 17:03
  • Okay, you mean by any means of code. I'm using Access via ADO.NET only as a data storage. (I know, there are better solutions but my client wanted to have Access...) – ygoe Jan 22 '13 at 08:20

1 Answers1

1

In the sample set-up illustrated, it is quite possible to edit a null to an existing element and to create records with nulls. I suspect you may have something not quite set correctly.

relationship with nulls

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • 1
    Okay, but it only works with a single column reference, or with multiple columns but maybe they all need to be null, not only some of them. – ygoe Jan 23 '13 at 13:02
  • Yes, you can have all null for the FK or all completed, but not a mix when you are using referential integrity. You could create an autonumber and use that. – Fionnuala Jan 23 '13 at 13:05
  • After multiple attempts with various combinations of "Ignore Nulls" index properties, "Unique" property both on and off, different index combinations in the primary and foreign table, etc., I could not get it to work with multi-columns keys. At one point I thought that I had it working and even submitted an answer, but I had forgotten to re-enable the referential integrity constraint. In the end, I conclude the same thing as LonelyPixel, that Access does not support the null-able, **multi-column** relationship while still enforcing referential integrity. – C Perkins Jun 16 '16 at 16:55
  • 2
    While it is old, I found myself with this problem with a Number type field FK. Besides the design shown in the image from Fionnuala, keep in mind that the FK field in the reference table (ElementID) **must** have its "Default value" property set to blank. When you define the field as a Number, Access defaults it to "0", and 0 <> Null, which in turn would make new records in Reference table to violate the referential integrity property of the relationship. – Ricardo Palomares Martínez Sep 20 '16 at 20:04