Cannot update value in table with a Null value but want to :( Help!

VBKid04
04-15-2004, 10:30 PM
I am running a command Update button which uses a SQL update to update the values in a table.

The Update takes the values from what the user enters in textboxes and Updates the table with the appropriate values.

However, one of the fields I want it to be able to update with a null value but it will not allow me to.

The reason I am guessing is because this value is a foreign key... we shall call it txtForeignID

In the database I have set txtForeignID to allow Zero Length strings, but that has not made any difference.

It still demands that I enter a (valid) related value into the textbox so it can store it, it will not allow me to enter nothing.


Is there any way I can store nothing (not the word null, but nothing, no spaces, nadda, zip) in the textbox with removing my relationships between the tables ?

Thanks

wengwashere
04-15-2004, 11:29 PM
what database are you using?

If you want to put up <NULL> values to it... or should i say if you dont want to put values on it... you should set in your design that the field should not be "required" or it should "allow nulls" on it - depending on your db.

VBKid04
04-15-2004, 11:40 PM
I am using access and yes I have set that property

but its not liking it :|

wengwashere
04-15-2004, 11:49 PM
As you said it is a foreign key to another table... have you done a relationship between them?

if so, there must be a restrictions somewhere there... Open the Relationships window and uncheck the "Enforce Referential Integrity" then try adding some values manually onto access...

MKoslof
04-16-2004, 07:19 AM
Why would a foreign key ever be null? If these values can be null, there is no point in having a foreign key.

wengwashere
04-16-2004, 01:14 PM
Why would a foreign key ever be null? If these values can be null, there is no point in having a foreign key.

There are some cases in a record, or in a design, wherein there is no value for a foreign key, or it is not a required field...

like for example if you have an employees table like this


EmployeeID
Name
ManagerEmployeeID (This is your foreign key)


as you can see, a manager is a part of employee, but a manager doesnt manage himself (Specifically if he is on top of the org. chart)

So if Joy is under John and John doesnt have any supervisors.. then our data would loook like this :
EmployeeID/Name/ManagerEmployeeID
1/John/*<NULL>
2/Joy/1

*<NULL> would probably be any value not connected to EmployeeID

Hope i explained it clearly

MKoslof
04-16-2004, 07:48 PM
Well, a foreign key should always be a required field. The purpose of a foreign key is to provide indexing (usually, but not required) and some way of identifying a relationship on a table. So, if I design a database that has a foreign key and the user allows this field be null, what's the point (in terms of the big picture, not one or two records)? If ManagerID is null and I have 300 null values in this field, I have defeated the purpose. I understand how in some situations, a record or two would be null. But, I don't agree that this is a good design strategy.

wengwashere
04-18-2004, 08:26 PM
Well, a foreign key should always be a required field. The purpose of a foreign key is to provide indexing (usually, but not required) and some way of identifying a relationship on a table. So, if I design a database that has a foreign key and the user allows this field be null, what's the point (in terms of the big picture, not one or two records)? If ManagerID is null and I have 300 null values in this field, I have defeated the purpose. I understand how in some situations, a record or two would be null. But, I don't agree that this is a good design strategy.

I agree with you. It is not advisable to set null value to foreign keys. Im just trying to do some workaround on the kids' (hes VBKid right?) question. Probably he has some good reasons why he need that since he knows more of what he is doing than we :)

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum