Cascade-To-Null Foreign Key

[IpeXeuS]

Hello,

I'm using Allen Brownes very helpful cascade-to-null relations (http://allenbrowne.com/ser-64.html) code in my database project, but there comes problems whit a foreign key and null values, cause foreign key shouldn't be null. Is there anyone else using this method with relational tables? What I should do to make this work?

All hints and tips are welcome and approciated, thanks on advantage. =)

- IpeXeuS

 

[RoyVidar]

If this is a theoretical question, about foreign keys and Null, you'll probably find some people finding that quite OK, and others thinking the concept of Null violates the purity of the relational model. There should be plenty of amusing discussions in the "Theory and practice..." category, though, if you've embraced the Cascade-To-Null option, then that does need nullable foreign key field.

If it's about practical usage, ensure the required property of the foreign key field is no (and isn't part of any Non Null indices, including primary key) - and no, I haven't yet used it in production, only tested it.

 

[Pat Hartman]

It is perfectly valid for some foreign keys to be null. If you want to allow null values be sure to remove the default that Access automatically sets to 0 for numeric fields. This will cause a problem when you try to save a record without entering a FK value. For example, many Church groups maintain mailing lists that group members by FamilyID. A single person would not be related to a family and so that field would be null.

 

[IpeXeuS]

Ok guys, thanks for your advices, you were great. =P

 

[The_Doc_Man]

I'm one of those "null" purists, but the link provides at least SOME justification.

Don't forget the BIG elephant in the living room: For this to be meaningful, there has to be a need to retain child records that lose their parent, perhaps for historical reasons.

To me, the bigger issue is deleting a parent with children, thus immediately creating orphans. If it makes sense in your real-world problem, then do it. But normally, you simply INVALIDATE the parent (which implicitly invalidates the children since in that case, you get to the children from the parent.)

I'll also add that I've never seen a case where this was ever necessary, but then I've only seen a subset of all the world's possible problems... guess I just missed the ones that need this feature.

I always add this disclaimer: If you are going to voluntarily include anything NULL in your DB, remember that you invite issues with things that are harder to find than normal. It is your nickel. Be careful how you spend it.

 

[Pat Hartman]

I agree with Doc regardint "child" records. You need to understand your relationships. You would NEVER want to cascade to null in a hierarchial relationship since that would create what are called orphans. It makes no sense to delete an order header for example while keeping the order details. However it does make sense to delete a room where a class is being held. You wouldn't want to cascade the delete to the class. You would only want to set its location to null until a new location is assigned. Deleting a room would be unusual but buildings don't live forever and so it is conceviable.

I am having trouble coming up with a real world case where you would want to use the cascade to null option but I see many cases where having a null FK would be valid.

Another situation where you would allow nulls in a foreign key is in a company's asset database. There are computers, cars, etc that are owned by the corporation but not assigned to an employee at this time. Usually you don't delete employees when they leave but if you did, you'd want the asset records FK to be set to null rather than having the asset records deleted.

 

[IpeXeuS]

I've database where's lots of person details and they all are in certain group and when I remove some value from group list, it will automatically removed from person details as well. So, should I use that Allen Browne's cascade-to-null code in this case or what do you suggest?

 

[The_Doc_Man]

Your description is exactly the case where you DON'T delete anything. I strongly suspect that either we have a language barrier or YOU have a non-normalized database.

Normalizing a database is a way to prevent data loss. Your description says you are going to lose data. You are trying to do exactly what normalization normally would prevent. I think that is why we are confused and giving you warnings about what you claim you want to do.

Understand, we are not saying this can't happen. However, in my experience (and I infer that Pat can't see why you want this either), this is a strange situation.

 

[IpeXeuS]

Erm, I'm not quite sure now what you guys trying to say? I'm about to delete value from group list for example, but you say I'm not deleting anything. Now I'm confused tbh.

 

[The_Doc_Man]

We may have a "jargon" problem caused by your being more familiar with the language of your problem than the language of databases.

When you delete something from a list and that something has a relationship to something else, and when cascade-to-null is enabled, you essentially break the link between the group element and the items associated with that element. The associated item becomes dis-associated.

The thing that worries me is just that usually you don't do it this way, you set a flag in the group element showing that it is no longer valid. Then you assign a default value to the association link to show that the "child" records have returned to a pool.

But if your problem allows you to delete that group element, OK. And NULL can be the marker for "POOL" items if that is what you want. It is just rare to constructively use NULL in this way. Most of the time people AVOID use of NULL because it has such ... interesting ... consequences.

Rather than try to stifle your creativity, let me just say that this is a point where you need to be very careful in what you do with nulls. Search the forum, particularly in the design topic, for threads related to nulls and their use. I wouldn't use them this way but you aren't me and you have a design goal in mind. Chacun a son gu as my wife's old grandmother used to say. (Except she said it with a Cajun accent.)

 

[IpeXeuS]

Ok, but if there's some values in list and you dont need them anymore (in this case person group for example), what you would do, cause you can't delete them if foreign key is set to not null and they're in use?

Do I need to go every single person through and select another group and after that I'm able to remove that specific value from the list? How would you approach this in my position?

Heres another very good MS Access related website for example about referential integrity ( http://www.databasedev.co.uk/data_integrity.html#ReferentialIntegrity ) and I found this:

"If we delete a record in the Departments table, then the cascade deletes option will delete all related records in the Employees table that contained the deleted department. Most database products will warn the user before making this (potentially dangerous) deletion.

Access will first delete all related child records and then delete the primary record from the parent table.

If you do not select the cascade deletes option, Microsoft Access will not allow you to delete a record that has related records in another table. In these cases, you would need to delete all related records in the child table(s) and then delete the primary key value from the parent table."

I found this topic ( http://www.access-programmers.co.uk...p?t=130778&highlight=delete+relational+record ) in here, I ment to delete only the FK, not the whole record (for example I don't want to delete all person details, only group detail of that person).

I was also thinking that if I take "Enforce Referential Integrity" option off in table relations, it just might be a solution for this, what you guys think?

Allen Browne also wroted in his "Cascade to Null" article that:

"What that check box does not do is prevent Nulls in the foreign key. In most cases, you must block this possibility by setting the Required property of the foreign key field in its table. But there are cases where a Null foreign key makes good sense. Batch operations like the receipt letters above are common. Even for something as simple as items in a category, you might want to allow items that have no category, so the CategoryID foreign key can be Null."

So what you guys think about that?

Anyways thank's guys for your replies and you've been very kind and helpful.

 

[RoyVidar]

From your description of your relationship, I think you are using Cascade-To-Null as it is intended.

 

[The_Doc_Man]

As I said earlier, and as I have said in many other threads, NULL is a tricksly little bugger. You can surely use it. However, since NULL (as a value) has properties that no other possible values have (including its reaction to NZ and its propagation through formulas, to name two), you are asking for extra work in preventing NULL from going where it is not wanted. Beware of trying to make null do more than indicate one and ONLY one thing: That which is now NULL is pointing to something that does not exist (when it is a foreign key field). Or... That which is now NULL has no known value (for non-FK fields) AND no other indicator exists for this condition. When you "push" too much on NULL, it doesn't push back. It becomes evasive.

So if you want to use null, just remember what NULL means when you use it. It means... NOTHING. Not zero, which is a value. Nothing! Empty set. Total absence of data. If you apply any other meaning than this, you have strayed afield. If you retain the idea that NULL means the absence of data, the absence of a link, or the absence of some attribute, then you are at least using it right.

 

[Pat Hartman]

You can solve this problem quite easily by creating a table to hold a person's group affiliation. It is essentially a junction table that joins the group table with the person table. You can have cascade delete set to yes for this relationship. That way if you delete a group, all the relation records for that group will be deleted but the cascade stops there. It does not go "up" to the employee records.

No code is required for this solution and anyone who sees it will understand it.

 

[The_Doc_Man]

Good point, Pat. Fake a many-to-many and let the junction table be a roadblock to a full-blown child-record cascade delete. I like it.

 

[Pat Hartman]

Thanks