Adding primary keys to tables in a SQL2K5 supporting Axapta

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.
Who is Participating?
AgrippineConnect With a Mentor Commented:
(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.

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.
mkarnofelAuthor Commented:
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.
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

mkarnofelAuthor Commented:
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.
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?
mkarnofelAuthor Commented:
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!!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.