asked on

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

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

SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

Mark Wills

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

fester62

ASKER

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

Tony

fester62

ASKER

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

fester62

ASKER

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

fester62

ASKER

Attached relatonship definition:
OU-Code-relationship.bmp

Mark Wills

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 ?

ASKER CERTIFIED SOLUTION

fester62

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

fester62

ASKER

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