By Edhy Rijo - 9/18/2007
Hi All,I am testing on a simple form with a one field and when trying to save I got the error below: BusinessLayerException An error occurred while saving an the data to the server. DataLayerSavingException The INSERT statement conflicted with the FOREIGN KEY constraint "Relation_203". The conflict occurred in database "ATR", table "dbo.Buildings", column 'PK_Buildings'. The statement has been terminated. SqlException The INSERT statement conflicted with the FOREIGN KEY constraint "Relation_203". The conflict occurred in database "ATR", table "dbo.Buildings", column 'PK_Buildings'. The statement has been terminated. Source : MicroFour StrataFrame Business
The table ServiceCalls is related to the table Buildings, but in the form only a ServiceCallsBO is in place. I will appreciate if anybody could help me figure out what may be wrong in this case. I am new to SF, VB.NET & MS-SQL Express 2005. My background is with Visual FoxPro. Thanks
|
By Trent L. Taylor - 9/18/2007
This is a message that is being returned by SQL Server. A foreign key relationship is setup on the table but you did not provide a primary key or a foreign key. You generally see this more on child records. But it is possible that you have a PK that is not autoincrementing and you did not provide a value for the PK.
|
By Edhy Rijo - 9/18/2007
Trent L. Taylor (09/18/2007)
This is a message that is being returned by SQL Server. A foreign key relationship is setup on the table but you did not provide a primary key or a foreign key. You generally see this more on child records. But it is possible that you have a PK that is not autoincrementing and you did not provide a value for the PK. Hi Trent, This is a bit confusing for me since I use xCase to design my data and relationship are usually created automatically. There are other tables which also has relations and does not generate this error. Let me provide more detail about this table and its related table: - Main Table Name: ServiceCalls
PRIMARY FIELD: PK_ServiceCalls FOREING FIELD: FK_Buildings
PRIMARY FIELD: PK_Buildings
In the form, only the ServiceCalls data is used with a SF BO, I setup this form with the same procedure as the others I have done while testing and this is the only one giving me that error. Please notice in the image the relation detail for relation named Relation_203. Just so you know, these two tables has not records, and both Primary fields are AutoIncremented by the server.
|
By Trent L. Taylor - 9/18/2007
Yeah, this goes back to my eariler point. First, you have a BO that represents the Buildings table I assume. This is the table that you are trying to update. So does the primary key field auto-increment or do you have to provide the primary key. Second, it is a child of ServiceCalls so the value that is in the PK_Buildings field violates the relationship. So the first thing to address is that the primary key is being auto-incremented by the server or provided by you somehow. Next, you may need to review the relationship (Relation_203) to see if a parent record is required. If so, then you may have to have a ServiceCalls record which the Buildings is associated with (I think this is less likely).
|
By Edhy Rijo - 9/18/2007
Trent L. Taylor (09/18/2007)
Yeah, this goes back to my eariler point. First, you have a BO that represents the Buildings table I assume. This is the table that you are trying to update. So does the primary key field auto-increment or do you have to provide the primary key. Second, it is a child of ServiceCalls so the value that is in the PK_Buildings field violates the relationship. So the first thing to address is that the primary key is being auto-incremented by the server or provided by you somehow. Next, you may need to review the relationship (Relation_203) to see if a parent record is required. If so, then you may have to have a ServiceCalls record which the Buildings is associated with (I think this is less likely).For testing pupose I went to the Relation properties in SQLExpress Manager console and set "Enforce Foreign Key Constraint = No" and that allow the record to be saved, even thought I am not so sure what this setting may break . Now to explain better my settings, I have a form to handle the ServiceCalls data with a ServiceCallsBO.vb class, I add just one field CustomerName and when trying to save I got the initial error in the first message. ServiceCalls is a child table of the Building table, so eventually in this form I will have a combobox to lookup the building data, but so far I am just testing with a plain and simple field. All primary fields in both tables are auto-incremented. To resume, in this case a ServiceCall record may have a Building record associated with it, but not necessarily, so is the changed I did above in the relation correct? Thanks!
|
By StrataFrame Team - 9/19/2007
If the ServiceCall is the child, but it does not necessarily have a building as it's parent, then yes, you changed the correct property. Your only other option would be to enforce the relationship, but create a "dummy" building record that is the "catch-all" for the service calls that don't have a building. That way, you end up with a "<No Building Assigned>" building record and all of the service calls are still attached to a building, but might be attached to that one.It all makes sense on the way you want to query the records. If you want to join the ServiceCalls to the Buildings table, you will have to to an OUTER JOIN to get the ServiceCalls that don't have an assigned building. Where as, if you create the dummy record, both an INNER JOIN and an OUTER JOIN will retrieve the records. It's all up to you.
|
By Edhy Rijo - 9/19/2007
Ben Chase (09/19/2007)
Your only other option would be to enforce the relationship, but create a "dummy" building record that is the "catch-all" for the service calls that don't have a building. That way, you end up with a "<No Building Assigned>" building record and all of the service calls are still attached to a building, but might be attached to that one.Hi Ben, Thanks for the confirmation and your new idea above, I do like it and I am considering implementing the dummy building record. If it is not too much to ask, could you elaborate a bit on this idea on how to accomplish this with SF? So far I know I can create the record manually with specific PK or add another field to flag this record as a dummy one, but then how to tell the ServiceCalls form to use that record for non assigned building? Is the answer by knowing ahead of time the PK or the flag field of the dummy record and setting it as default for the combobox lookup field of the ServiceCallsBO? Also another question, does SF has anything to do with handling the enforcement of referential integrity or it is the sole responsibility of the database to handle that?
|
By StrataFrame Team - 9/19/2007
We help along the referential integrity by setting foreign key values when you specify a relationship within the business objects, but we don't enforce referential integrity; that's the sole responsibility of the database.As for using a dummy record, there are a couple of ways you could go about doing this. You could know the PK ahead of time, either because you manually assigned it (IDENTITY INSERT ON) and always use the same PK, or because you put a flag on the record and retrieve it when you start up the application. Either way you would have the PK in a shared variable or a constant somewhere that would allow you to assign the value within the SetDefaultValues() of a business object. So, records would start out bound to that dummy record unless the user chooses to assign them to a different building. Like I said, though, you certainly don't have to use a dummy record, and if keeping track of the dummy record is a pain, then using 0 (which you would always know) would certainly work, too, but you would have to turn off the enforcing of your PK (cascading deletes/updates would still work, though because you have the relationship defined, turning off the enforcement only prevents the DB from throwing an exception when the relationship isn't followed).
|
By Edhy Rijo - 9/19/2007
Hi Ben,Once again, thanks for your input. Now I know how to get there in either case.
|
|