Adding primary keys to tables in a SQL2K5 supporting Axapta

Posted on 2008-11-12
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
    LVL 5

    Expert Comment


    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

    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.
    LVL 5

    Accepted Solution

    (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.

    Author Comment

    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.
    LVL 5

    Expert Comment

    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

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    This is a walkthrough guide I wrote whilst upgrading my on-premise MS Dynamics CRM 3.0 deployment to 4.0. This covers the actual installation of the product to a working level for my system, I ran into a lot of issues that the steps below fixed so h…
    I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    This video discusses moving either the default database or any database to a new volume.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now