Al: New field in primary key does not update index

[larswestman]

I added a new field in a table as the first field in the key, published the app from VS Code with "schemaUpdateMode": "Synchronize". No errors about key change (great!), but when exploring the unique index in SQL the new field is not added to the index and trying to use it in BC generates a duplicate error.

It's really great news that we can add fields to existing keys, so I hope we can get this all the way to the index in SQL.

Runtime 4.0. BC15.0.35903.0 and vsix 4.0.170493

[atoader]

@larswestman could you please provide a sample with your code before and after the the change? For future issues, please follow the template. It helps us greatly in identifying the root cause with higher efficiency.

[larswestman]

The added field is marked with red

As you can see from the previous screenshot from SSMS the new field isn't present in the clsutered index.
DefaultPropertyValue.Tab12057544.zip

The file in it's modified form is attached

[larswestman]

I think I have provided necessary input now. Otherwise please let me know so we can go forward with this. @atoader

[atoader]

Thank you very much for the input! We'll follow up first thing next week. We've been restructuring our GitHub process but we now have a plan and the needed dev power to better handle the issues you file.

[larswestman]

Sounds great! We have an update in our app waiting for this to be resolved.

Just let know if you need anything more from me.

[AndreyKorepanov]

Hi @larswestman, you are right it is a bug in synchronization engine. Adding a field to key is considered as a destructive change and should not be allowed when using "schemaUpdateMode": "Synchronize".

[larswestman]

Ok. But why is it consider ed a dustructive change? I agree it's breaking, but it doesn't destroy data. We can do this in c/side and I would really like to do it in AL as well.

/Lars

[larswestman]

@atoader : Can we have some feedback on this? Why shouldn't it be allowed to add a field to a key? That doesn't destroy any data. We have been able to do this before without any issues.

[gintuoklis]

hey @larswestman,
I presume it is problematic due to dependant apps.
We are running in mixed mode C/AL + Extensions, and face these kind of problems. Developers changes key on C/AL by adding additional column (perfectly fine from C/SIDe perspective). But, the same table is extended by one of the extensions. You can still compile extension, but if you try to publish/sync you will receive and error.
at least on AL you are protected from making these mistakes, but if you run mixed mode, you get to know it very late:)

[atoader]

We discussed this internally and there are a few challenges to implementing this:

1. Companion tables (table extensions) use the PK of the main table. If the PK of the table changes, then all records in the companion tables need to be brought in sync.
2. If you have a header-lines relationship between two tables in two different extensions and the header suddenly gets an extra field in the primary key but the lines in another extension doesn't, then you might be a duplicate key exception in your extension when trying to add lines to a header.

Considering that fixing this would require changes to the runtime, it is outside the scope of this repository. I recommend opening a BC Idea or upvoting one if it already exists.

The initial problem bug was fixed so I will close this issue for now.