Primary keys changes in AppSource not allowed
Views (68)
Jalmaraz
669
Like
Share
ReportIf you are publishing Business Central Apps in AppSource you will notice a fact, you cannot change the primary key…..of your own app tables. I am talking about non-destructive changes but can be felt as breaking changes in some cases:
. Increase the length of a field included in primary key in not allowed.
. Is not allowed to add a new field at the end of the primary key .
When did I have the need to increase the primary keys fields? Two scenarios:
. A field of the primary is related with another primary key table that increase its length. Example, you have in your key the Item Variant Code field and tomorrow BC increase to 20 characters the “Code” field of the table Item Variant.
. The second scenario could be more painful: You make a table "Sales Price" style in your own app with a complex primary key with many fields., This key includes all search criteria for your prices: Item No, Variant Code, Sales Code, Unit of Measure Code. I mean the kind of table like price, discount, any searching criteria table. If tomorrow there is a new searching criteria, "Drop shipment" for example, you need to increase the primary key to add this new field. This is an example, I am talking about my own app tables, not standard app tables. I don´t want to change any primary key in the standard of BC.
In both cases you need to do these steps to make the change. In fact, you cannot make the change, instead you must do the next:
. Mark the table as obsolete.
. Create a new table with the new search criteria in the primary key.
. Make upgrading app code to transfer data from your old table to the new one.
. All the code targeting to old table must be targeted to new one.
I post an idea in Microsoft and lobbied for allow this primary key change in Social Networks (in my poor and small version of social networks, I am a boomer), in this address:
If you are interested vote for allow this change.
What can we do? Avoiding PK changes.
I feel that the my request in Microsoft ideas has not a great future. So, we must be ready to design without primary keys changes.
Let´s do a non-hello-world example, to explain the kind of table in wich you already know you will change its primary key soon or later: we have promotions in some items and customers, and with a minimum quantity of sale we will give an additional free quantity of item.
The table fields are:
field(2; "Item No."; Code[20]) {
TableRelation = Item.
}
field(4; "Variant Code"; Code[20]) {
TableRelation = "Item Variant";
}
field(5; "Customer No."; Code[20]) {
TableRelation = Customer;
}
field(6; "Minimun Quantity"; Decimal) {}
field(7; "Free Quantity"; Decimal)
In classic design we set the primary key to these fields:
key(Key1; "Item No.", "Variant Code", "Cusntomer No.", "Minimun Quantity")
But, we are not sure if we will need another field to set the promotion (unit of measure or any new field y our app). So we can add a new field to set the primary key:
field(1; "Entry No."; Integer)
And set the keys this way:
key(Key1; "Entry No.")
{
Clustered = true;
}
key(Key2; "Item No.", "Variant Code", "Customer No.", "Minimun Quantity")
{Unique = true;}
We declare “Entry No.” a sequential integer as primary key and set a secondary key with the property unique set to true.
Unique property: AppSource change keys solutions?
Unique property in a secondary key set this set of values as unique in the table, and if we try to add thew same key twice SQL server will raise an error. When this property was added to AL design, there can be only one secondary key with this property, but if we check the last docs, in the example are two keys with the property unique set to true:
I am assuming this fix my problem with primary keys changing in AppSource, and AppSource allow add new fields to this unique secondary keys, but I don´t know yet. AppSource is not a toy and I can´t do this check with live apps, will take a while to have this problem again and test this solution. I will write here my tests.
*This post is locked for comments