I have several tables that I need to contain a "Null" value, so that if another table links to that particular record, it basically gets "Nothing". All of these tables have differing numbers of records - if I stick something on the end, it gets messy trying to find the "Null" record. So I would want to perform an INSERT query to append a record that either has the value 0 for the ID field, or a fixed number like 9999999. I've tried both, and Access doesn't let me run the query because of a key violation.
The thing is, I've run the same query before, and it's worked fine. But then I had to delete all the data and re-upload it, and now that I'm trying it again, it's not working. Here is the query:
INSERT INTO [Reading] ([ReadingID], [EntryFK], [Reading], [NotTrueReading])
VALUES (9999999, 0, "", FALSE)
Where 9999999 is, I've also tried 0. Both queries fail because of key violations.
I know that this isn't good db design. Nonetheless, is there any way to make this work? I'm not sure why I can't do it now whereas I could do it before.
I'm not sure if I'm fully understanding the issue here, but there may be a couple of reasons why this isn't working. The biggest thing is that any sort of primary key column has to be unique for every record in your lookup table. Like you mentioned above, 0 is a pretty common value for 'unknown' so I think you're on the right track.
Does 0 or 9999999 already exist in [Reading]? If so, that could be one explanation. When you wiped the table out before, did you completely drop and recreate the table or just truncate it? Depending on how the table was set up, some databases will 'remember' all of the keys it used in the past if you simply deleted all of the data in that table and re-inserted it rather thank dropping and recreating it (that is, if I had 100 records in a table and then truncated it (or deleted those records), the next time I insert a record into that table it'll still start at 101 as its default PK value).
One thing you could do is to drop and recreate the table and set it up so that the primary key is generated by the database itself if it isn't already (aka an 'identity' type of column) and ensure that it starts at 0. Once you do that, the first record you will want to insert is your 'unknown' value (0) like so where you let the database itself handle what the ReadingID will be:
INSERT INTO [Reading] ([EntryFK], [Reading], [NotTrueReading]) VALUES (0, "", FALSE)
Then insert the rest of your data. If the other table looking up to [Reading] has a null value in the FK column, then you can always join back to [Reading] on coalesce(fk_ReadingID,0) = Reading.ReadingID.
Hope that helps in some capacity.