Troubles with referential integrity

JKelly — Thu, 06 Apr 2006 12:56:09 GMT

Hey guys,

I have reduced my database schema and objects down to something very simple, in this case just two tables with identity columns

[Assignment (PK: Assignment_ID, FK: Users_ID]

[Users PK: Users_ID]

with the intuitive DRI of

Assignment.Users_ID References Users.Users_ID (M:1)

No, these are not the exact object names but are representative. There is a separate business object for each object

boAssignment

boUsers

and boAssignment is configured as the “ParentBusinessObject” for boUsers.

The tables have been pre-populated with test data. I can view and edit data. I cannot delete data from the Users table due to the FK to the Assignment table.

The DELETE statement conflicted with the REFERENCE constraint "xxx_Assignment_Users_ID_To_Users_Users_ID". The conflict … table "xxx.Assignment", column 'Users_ID'.

I’ve tried a number of things including (the highly questionable method of ) making the form delete event “aware” of the referential integrity with the following sequence of commands

boAssignment.DeleteByPrimaryKey(CLng(strArg))

boAssignment.Save()

boUsers.DeleteByPrimaryKey(CLng(strArg))

But the boAssignment.Save does not commit so the Users delete fails as well.

I can get around all of this (as in a typical client/server model) by creating a custom method in the business object and executing a scalar to invoke a parameterized stored procedure that takes care of the deletes with regards to the DRI and concurrency checking and then force a refresh of the of the business object data tables but that kludge defeats the whole purpose of the framework.

There has got to be an easy way to do this.

If I drop the foreign keys and execute the statements in order (again, not the best as I am now embedding implied DRI in the form) I can get the deletes to work.

Please tell me where I am going wrong here. Is declared referential integrity just plain out of the question?

Please proselytize me.

Thanks – J.

RE: Troubles with referential integrity

StrataFrame Team — Thu, 06 Apr 2006 12:56:09 GMT

J,

We looked long and hard at adding the functionality to cascade DELETEs through the business objects themselves, however, since every database worth using supports that functionality, there was no need to implement it. Basically, the symptoms you're describing are exactly what's supposed to happen. However, you've got a few things swapped around. The foreign key field User_ID on the Assignment table inplies that the Assignment table should be the child of Users, however, you've got the relationships within your business objects backwards (Users is the child of Assignment). So, since your relationship in the database is configured to have Users as the parent of Assignment, the relational integrity is operating properly (you cannot delete a parent record if it still has children). So, for one, you need to configure the relationships the same in both the database and your business objects (Users parenting or Assignment parenting, doesn't matter as long as they're the same). Secondly, if you want the type of functionality I think you're hinting at, then you'll want turn on cascading deletes on your relationship you defined on SQL Server. Then, when you delete a parent record and save the business objects, it's just a matter of "refreshing" your child bo by refilling it. When it is refilled, it will be missing the records that were deleted through the cascading delete trigger (correctly).

Ben

StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Troubles with referential integrity

Troubles with referential integrity

RE: Troubles with referential integrity