UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Can't Update Table When Foreign Key Is An Empty String, Access 2013    
 
   
daviddurr
post Sep 12 2019, 11:10 AM
Post#1



Posts: 6
Joined: 12-September 19



I have a form that intentionally sometimes submits an empty string as the value of a text field that updates a field in table 2 that is a foreign key from table 1. The empty string needs to be stored in table 2 on the many side of a relationship that enforces integrity. The key field on the one side is set to allow zero length, yet I am still getting a key violation when I run an Update query.

I have verified that the form is sending an empty string to the query. Since the key on table 1 is set to allow zero length, shouldn't it allow me to have the empty string in table 2? It won't, I get a key violation. If I disable integrity, table 2 updates.

What am I missing?
This post has been edited by daviddurr: Sep 12 2019, 11:11 AM
Go to the top of the page
 
theDBguy
post Sep 12 2019, 11:43 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,382
Joined: 19-June 07
From: SunnySandyEggo


Hi David. Welcome to UA! welcome2UA.gif

If you have Referential Integrity turned on, then I think Access won't let you use a ZLS unless you also managed to have a ZLS as a PK in the parent table. Do you have a ZLS as a PK in Table1?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Sep 12 2019, 11:47 AM
Post#3


UA Admin
Posts: 35,867
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

I'm afraid I need a bit more background. In Table 1, is this field the Primary Key? I see that you've set it to allow a Zero Length String, which is not what I'd normally expect to see.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
daviddurr
post Sep 12 2019, 12:32 PM
Post#4



Posts: 6
Joined: 12-September 19



I added one earlier as a work around, but doesn't seem like it should be necessary. It works, but I don't like making up data to get things to do what I want them to (and they should) do.

Thanks for the response. Appreciate your time.
Go to the top of the page
 
theDBguy
post Sep 12 2019, 01:01 PM
Post#5


Access Wiki and Forums Moderator
Posts: 76,382
Joined: 19-June 07
From: SunnySandyEggo


Hi. So, are you all sorted out now? Just wondering...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
daviddurr
post Sep 12 2019, 01:15 PM
Post#6



Posts: 6
Joined: 12-September 19



I suppose so. It works, I just don't like having to create a "false" piece of data just to get the form to update. Wish there were another way without adding a record to the table.

Thanks again.
Go to the top of the page
 
theDBguy
post Sep 12 2019, 02:06 PM
Post#7


Access Wiki and Forums Moderator
Posts: 76,382
Joined: 19-June 07
From: SunnySandyEggo


How about disabling Referential Integrity? Just a thought...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
kfield7
post Sep 12 2019, 02:16 PM
Post#8



Posts: 1,003
Joined: 12-November 03
From: Iowa Lot


I seem to be missing some context here.
I agree with George, a ZLS should not be acceptable as a primary key or a foreign key, the relationship is too vague.
If there's no data associated with a table record, it should not be added to the other table's FK.
If there is data, it should have an acceptable PK. (to clarify, yes you could have an acceptable PK with the other fields null, "", etc., but I'd be surprised to find anything useful having a ZLS PK.)

Can you show/change your table design?

BTW, I have in the past had to create a "fake record" -- for other reasons that I can't think of presently, maybe default values or something like that. But it always had an acceptable primary key.
This post has been edited by kfield7: Sep 12 2019, 02:21 PM
Go to the top of the page
 
nvogel
post Sep 13 2019, 01:13 AM
Post#9



Posts: 1,010
Joined: 26-January 14
From: London, UK


As already mentioned, you won't be able to set a foreign key as an empty string unless there is a corresponding empty string in the table being referenced. However, you can update the value to NULL if the column is nullable (meaning Required=No). No null is required in the table being referenced. Null is not the same as an empty string and it sounds like a null is what you were looking for here.

Although you could us a null in this way, it's not generally a good idea to allow nulls in foreign keys. Try to avoid or minimise your use of nulls as much as possible. You can avoid a null in this situation by putting this column into a new table and only populating that table when the (non-null) value is required.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th October 2019 - 05:16 AM