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.
mkarnofelAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AgrippineCommented:
Hi,

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!
Agrippine

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.
0
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.
0
AgrippineCommented:
(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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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.
0
AgrippineCommented:
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?
0
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!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Dynamics

From novice to tech pro — start learning today.