Hi all,

It has been many months since my last posting on here. I always enjoy no notice life changes

While it is great to be back I am afraid I think I have a bug with the "Enforce relationship for INSERTs and UPDATEs" relationship property. In my case, I have this property turned off for all relationships but they are always created with the property turned on. Looks like the property is ignored.

Can you have a look please?

Thanks.

Aaron

I just did a test and it seems to work form me. The relationship is created, but the update and delete actions are set to "no action" and the "enforce FK constraint" is set to No.  What are you seeing that seems wrong?

Thanks for trying it Greg. I have checked it again and it fails for me. I have tried it on both SQL 2005 and 2008.

The attached DDT screenshot shows the settings for a particular relationship. The SQL screenshot shows what it looks like after a completely new database is created. The DDT is set to disable the Enforce FK Constraint but it is created with it turned on in SQL.

I am using the latest version of SF from the website.

Aaron,

The SF team has tested this quite a bit in the last 24 hours. We haven't figured out why this sometimes happens (this occurred for me and Ivan, but Trent couldn't get it to fail), but when we are very precise in the steps taken, it works, every time. It is very likely that there is something the user (i.e you and me) is doing that isn't quite right.

If you can't get it to work, you can send us the package (email it to support@strataframe.net)and we'll see how it works here. Of course, we'd need two packages, one to create the db in its initial state and another one to run the update.

Thanks for your patience with this one, I know it can be frustrating...

Hi Greg,

I can't get it to work at all and I don't know what steps I can do differently. In my case it fails when creating a new empty database so only one package is needed which I will email shortly.

Thanks for looking into this one.

I have found out where it is going wrong and have sent the details to support. However, just in case anyone else is having the same problem I will post the details here too.

For some reason, if you deploy data packages the relations are created correctly before the data is deployed, then dropped and recreated incorrectly after the data is deployed. To explain further, I created a post deployment script as a temporary fix to set all the relations to “Enforce FK Constraint” to No and I was surprised to see it didn’t work. I then ran a SQL profile and found the following:-

1.       During the database sync phase, the relations were actually created correctly as “Enforce FK Constraint” = no. At this point the relationships are correctly defined as per the database model.

2.       The post-deployment script ran and (needlessly) also set the relations to “Enforce FK Constraint” = no.

3.       The data deployment process ran and after this stage the SQL profiler showed that every relationship was dropped and recreated incorrectly as “Enforce FK Constraint” = yes.

I have found that if I remove all of my deployment data packages then the relationships are created correctly. If I add a simple data package then the relationships are created correctly before the data is deployed, then they are dropped and recreated incorrectly after the data has been deployed. There is nothing complicated about the data package. It is a simple table containing a single record to show the database version and it has no relationships to any other table.

There appears to be a secondary process that drops and recreates the relationships incorrectly which is only triggered when a data package is deployed and this is undoing what has been correctly done up to that point.

Aaron

Hello I have the same problem.

Now since nearly one year no updates are availble.
I got big problems with this bug.

It would by nice to hear something about this problem.

Thank you

Rainer

Hi,

Given that things seem to be getting back on track, can I bump this problem please? I think the data deployment stage is incorrectly recreating relationships after the data has been deployed as ALTER TABLE xxxxxx CHECK CONSTRAINT xxxxxx instead of ALTER TABLE xxxxxxx NOCHECK CONSTRAINT xxxxxxx. The post data deployment stage is assuming that all relationships enforced the FK constraint which is not the case with me.

Thanks,

Aaron

Aaron,

Well, this is debatable I think.  It is actually doing what it intended to do, but maybe I don't understand what YOU are trying to do.  Maybe a small sample would help.  Thanks.

Trent,

In the DDT I have my relationships setup with "Enforce Relationship for INSERTs and UPDATEs" turned off as in the DDT1 screenshot. When I create a SQL database and do NOT deploy any data packages, then the relationship is correctly created in SQL as in screenshot DDT3 (i.e. "Enforce Foreign Key Constraint" is turned off). However, if I include a data package then the relationship is created incorrectly as in DDT2. So you get a different relationship whether you deploy data or not - regardless of whether the data is even deployed into the same table.

It all centres on whether you are deploying any data as part of your DDT package. If you are deploying data then "Enforce Foreign Key Constraint" is assumed to be on by the process which runs immediately after the data is deployed. Don't confuse this with the pre-data deployment stage when the database is physically created. During this stage the relationships are created correctly. It all goes wrong AFTER the data is deployed.

I can guess what is happening:-

1. If I had a table with relationships with "Enforce Relationship for INSERTs and UPDATEs" turned on AND I also wanted to copy data into the table during the deployment, the relationships would either have to be dropped or converted to the off state to allow the data to be deployed to the table - or else the constraint could reject some data.

2. After deploying the data, the relationship would then have to be recreated with "Enforce Relationship for INSERTs and UPDATEs" turned on.

The above two points are fine - provided that the relationship was defined as "Enforce Relationship for INSERTs and UPDATEs" turned on. In my case it is turned off but the DDT is making the assumption it is on when it recreates the relationships after the data has been deployed.

Please check the routine that creates the relationships AFTER the data has been deployed. I bet it assumes that all relationships have the "Enforce Relationship for INSERTs and UPDATEs" turned on and doesn't check the true setting.

Sorry for the long winded explanation but I know it is wrong and would love a fix

Regards,

Aaron