Adding primary keys to tables in a SQL2K5 supporting Axapta

Posted on 2008-11-12
Medium Priority
Last Modified: 2012-05-05
Greetings Experts,

I'm attempting to set-up transactional replication in SQL2K5.  When you attempt to create a publication and are creating articles, it is telling me that you cannot select tables that do not have a primary key.

This database is supporting Microsoft Axapta 3.0.  I added a primary key to a table in SSMS.  That part is easy.  The problem is with Axapta, when you synchronize the database in the AOT Axapta removes the primary key.

Can someone tell me how to define a tables primary key in Axapta?  I'm assuming you use the Data Dictionary in the AOT of Axapta.
Question by:mkarnofel
  • 3
  • 3

Expert Comment

ID: 22947707

yes, you are right - the AOT data dictionary.

The simplest thing in your case is to use the RecID as the primary key. Therefor, select the table you want to modify in AOT- > data dictionary -> Tables. In the context menu select "Properties", a property sheet opens.
Here, set the property "CreateRecIdIndex" to yes.
From the context menu, select "Compile" and "Synchronize".
The index should now be shown in the database.

Hope this helps!

PS: If I remember correctly, in some very early versions of AX3, the RecId was not always unique, but let's deal with that if you really run into the problem.

Author Comment

ID: 22947763
Thanks for the response.  I looked at my PriceDiscTable as an example.  I went to the AOT --> Data Dictionary --> Tables --> PriceDiscTable and selected Properties.  On this table the CreateRecIDIndex was already set to yes.  However when I examine the table in SQL Server there is still no primary key.

I also learned that the normal way to make a primary key is to use a relation on an extended data type that refers back to itself.  My problem with this plan is that the RecID column is not visible in the AOT Data Dictionary because it seems to be a system column.  So it seems that the tables in AX that have primary keys are ones that have other columns natively that are unique.  An example of that would be CustTable's AcctNum column.

Thanks again for yorur help.

Accepted Solution

Agrippine earned 2000 total points
ID: 22948331
(Sorry, was away in a meeting.)

I do not think that it can be the extended data type thing.

On PriceDiscTable: Additionally, try setting the property PrimaryIndex to RecIdIdx. It should be available for selection from the combo box.

If that still does not work, you may still add a new index in the AOT to the table and add the RecID. You just cannot drag it from the field list, but have to right click on the index -> add field. Choose that new entries properties and type RecID into the DataField property.
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.


Author Comment

ID: 22952521
That did the trick.  I have one last question.  Can you see any negative implications of adding primary keys to tables in AX?  Obviously, so long as the RecID is unique there doesn't seem to be one to me.

Thanks for your help.

Expert Comment

ID: 22953226
From a functional point of view there is no negative implication. I am not sure though about performance - I have to admit that I am not a SQL server specialist.
I guess when querying the table, performance will still be OK, for inserting I am not sure.

Maybe a good idea would be to set the primary key as the cluster index - still, it could be that AX does that automatically. Well, not sure about that.

These are the things I would ponder about and then ask a SQL specialist, and from your question, it seems that you are one, so maybe you now better?

Author Closing Comment

ID: 31516264
Thanks a bunch for your help.  I certainly know more about SQL than AX, however, I went ahead and asked Microsoft the same question.  They confirmed that adding PK's with this method should not be an issue.  Thanks again!!!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question