foreign keys

Why does Nav’s SQL stgructure lack foreign keys. It seems like i always have to hunt around for my SQL hook in to another table so i can make a join. If NAV utilized foreign keys I would be able to use the query designer within SSMS

Because table relationships are enforced inside the application. NAV doesn’t use Foreign keys the way that SQL Server does. You’ll have to figure out relationships inside the object designer.

It’s possible to turn on foreign keys and it should generates them for all table relationships on SQL Server, but since NAV never uses them, it is overhead. You could take a local copy of the database and turn it on there, just for your research purposes, but I would not do it in the production database. Go to File, Database, Alter. On the Integration tab you’ll find a checkbox for ‘Maintain Relationships’. Turn that on it should create the FK’s on SQL Server.

Only on your local copy though :slight_smile:

awesome thanks! I have been trying to figure a way to make my back end SQL development easier.

It pays to know the table design inside NAV though, you should really learn how that works too.

I am really new to NAV, and have only been working at my current Co. for 2 months. So i am trying to learn the data structure of the CO. and NAV at the same time. I have worked with SSRS for my reporting in the past. What books or material would you recommend for a developer being introduced to NAV?

Download the training material from partnersource/customersource for the two development courses, that will get you started. What’s especially important for you probably is to learn about SIFT (SumIndex and Flowfield Technology), because that affects how data is stored on SQL Server.

Your profile says you’re a DBA, and it looks like you’re a SQL Server DBA. One thing that’s really important for NAV databases is to NOT modify the table design on SQL Server directly, you can really screw up your database that way (because object definitions are stored inside the application and discrepancies between the stored definition and the SQL Server design can cause severe damage to your database). All table modifications should be made in C/SIDE.