key violation error in Access that shouldnt happen
Hi all-
I'm wondering if anyone else has seen this. In an Access 2002/2003 database I have a table- call it table1- that has a field (field1) joined to another table (table2) in a one-to-many relationship. Referential Integrity & cascading updates & deletes are on.
I cannot insert a new record into this table using a Query or ADO.NET unless I provide a value for this field. I get a "key violation" error. But I shouldn't have to provide a value!
I can add a new record without supplying a value for that field if I do it directly in Table View. And- here's where it gets weird - I have several other fields also joined to the same field in table2 in the exact same way - and I don't have to supply a value for those fields in order to Insert a record!
I have checked dozens of times that the field is not a key field, has no indexes set on it, is not a required field, and has "Allow zero length" turned on. Exactly the way the other fields are set up!!
This one is really driving me nuts, and of course the project was due last week... :-)
Anyone seen this before?
Thanks,
-Andrew
# 1 Re: key violation error in Access that shouldnt happen
I don't think you're following all of the referential integrity rules here. See if the following helps:
http://www.ehow.com/how_13625_understand-referential-integrity.html
# 2 Re: key violation error in Access that shouldnt happen
So I have to insert a NULL for field1, which works. Then why don't I also have to insert NULLs for the other fields which are joined in exactly the same way to the same field in table2??
The thing that is so frustrating when trying to understand these rules, is that they don't seem to be absolute. Access seems to follow them at will, and to discard them at will. Every time I think I have a handle on something, Access contradicts what I think I have learned. Then I think my understanding is wrong!
It turns out that I DID understand this correctly when I started working on this database, but I was led astray by Access allowing incorrect inserts in the other fields.
So, given that I should be inserting NULLs when there is no entry in table1, WHY does Access let me get away with NOT doing it in the 2 or 3 other fields which are joined in exactly the same way to the same field in table2?? It's infuriating...
Thanks again, Paul...
-Andrew
# 3 Re: key violation error in Access that shouldnt happen
Referential Integrity only applies when the column from the primary table is a primary key or index. That's why they call them "foreign keys". But, yes you must enter a Null value into the foreign key column if there are no corresponding records in the related table.
I'm not sure how to explain the other table relationships other than to say that there must be something that is slightly different.
