Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Trying to update a table with foreign keys causes a key violation error.

Posted on 2008-06-11
9
Medium Priority
?
245 Views
Last Modified: 2011-10-19
I am trying to update a table wihch has a single Primary key and a number of foreign key fields - the foreign key fields have properties of:  Required = No, Indexed = Yes (Duplicates OK) and Default value = NULL. When I try to UPDATE this table supplying the primary key and one or several of the foreign keys I get a Key violation error. If the FKs allow nulls, I am bemused why this should occur ? Any ideas anyone ?
The PK I am supplying id definitly unique.
If I dont supply any of the FKs I dont get a key violation and the table updates.
I am enforcing referential integrity between the primary and foreign key tables.

Tony
0
Comment
Question by:fester62
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
9 Comments
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 200 total points
ID: 21762076
Remove NULL as the 'Default Value'
By definition - they will be Null unless you populate them

Also ... are you *sure* that you have INexed = Yes (Duplicates OK) for all FK's ?

mx
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 720 total points
ID: 21762141
There is a reasonable probability that the columns being supplied are not NULL, they may well be an empty string, or zero length string which is not the same as NULL and will try to populate. That is the typical reason when it works if you do not supply the column. So, check those columns, and make sure they are in fact NULL.
0
 

Author Comment

by:fester62
ID: 21767246
MX: yes all FK fields are set to Yes(Duplicates OK) in primary table; I have re-checked.

Tony
0
RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

 

Author Comment

by:fester62
ID: 21767381
mark_wills: I am only supplying non-null values at present, so there is no opportunity for getting "" mixed up with NULLs. I am aware of the difference.

I have tried a number of INSERTS using different combinations of the fields in the Primary table (there are some 30 odd altogether). At present it seems to fail on one particular field, that happens to be called 'OU Code'. The INSERT statement is as follows:

INSERT INTO tblResources ([GPN], [FirstName], [LastName], [FTE], [PAC], [OUCode], [DataSource]) VALUES ('HRID11112', 'test 1', 'test 2', 1, 1, 'GUBY', 'M');

The field has the properties in the update table and Related table as shown in the attached.

tony



OU-Code-properties.bmp
0
 

Author Comment

by:fester62
ID: 21767421
Btw, I have tried the above statement omitting the OU Code and it is successful, so it cannot be due to any other field. I have a similar problem with other FK Fields (not included in the above INSERT), but they are all defined in a similar way to the above.

Tony
0
 

Author Comment

by:fester62
ID: 21767469
Attached relatonship definition:
OU-Code-relationship.bmp
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21768174
Hi tony,  Good detective work... cannot see anything untoward, you have allow zero length, and it is not required... You wouldnt happen to have a test database or something that does not compromise security / IP etc that you would care to share ?
0
 

Accepted Solution

by:
fester62 earned 0 total points
ID: 21768234
I think I may have just figured the problem...and the clue is in the above! I have another table called OUstructures which defines a hierarchy of OU Codes. However, the primary table for OU Codes in tblOUCodes and any validation of OU Codes should always be done against this. For some reason, although whilst doing some work in the relationships window, I managed to invert the relationship between OUStructures and OUCodes, so that OUStructures became the Primary table.

In another case I set a default of '0' for a numeric key on the many side but did not allow this as a default value in the primary table.

Tony


Tony
0
 

Author Comment

by:fester62
ID: 21768278
Thanks for your comments, often just posting stuff here makes you go through and retest things you thought you had made good, but sometimes havent!

Part of the solutuion was prompted by mark_wills comments re: checking what values, nulls, "" etc are being supplied, so I will award half the points there. The rest nobody could have figured out until I posted the last screenshot. Thx Mark.

Tony
0

Featured Post

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question