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

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

Trent,

As a follow-on to my previous posting, please consider this little test. I deployed my DDT package to a fresh database and did NOT deploy any data. All of the relationships where created correctly in the following format:-

ALTER TABLE [dbo].[GAZETTEER] NOCHECK CONSTRAINT [FK_GAZETTEER_DEPOT]

So far, the database is perfect but I haven't deployed my data yet. For the record, the only data I am deploying are the security permission related tables as in the attached screenshot.

Next I ran the same DDT package and this time choose to only deploy data - no schema changes. Before the data was deployed, the same SQL command as above was run for each relationship. The DDT is clearly trying to turn off the constraint before it copies the data and the DDT is making the assumption the constraint is actually turned on - which it wasn't. Then, the data is deployed. Finally, the DDT recreates the relationships but in the incorrect format of:-

ALTER TABLE [dbo].[GAZETTEER] CHECK CONSTRAINT [FK_GAZETTEER_DEPOT]

The DDT makes assumptions about how the relationships should be re-created after the data is deployed. It simply doesn't check how they are defined in the DDT package.

The problem is definitely only there if you deploy data.

Regards,

Aaron

I will flag this and look at it when we revisit the DDT next.  Thanks for the heads up.