I need to create a new table that will require a unique secondary key. (Primary Key: Field1 - Secondary Key: Field2, Field3). The secondary key should only exist once in the table regardless of the primary key…which will always be unique anyway.
Should I simply test for the secondary keys existance prior to insert or modify… or is there a better way?
No, you have to test it.
This can be done if you are using SQL.
Add the key to your Navision table definition.
Set the SQLIndex property the same as the key fields. This way Navision will not append the primary key fields when it creates the index in SQL. This will result in a non-unique index being created.
Go to SQL and turn on the unique property for the index.
The error message users will get may be somewhat misleading as it will refer only to the primary key fields. Also you will need to remember to reset the unique property whenever the key is updated from Navision.
But it is not adviced to change Navision-tables directly in SQL. Because next time you import the object, the changes in SQL can be deleted.
I don’t like that solution simply for the fact that when oyu modify anything in the NAV table definition it will wipe out that setting on SQL Server. Do yourself a favor and program this in the table triggers.
Although this will work, and its a quite inovative way of getting around C/SIDE limitations, it really isnot recommended. It is not a 100% solution, andyouwill get caught at some time. You really do need to create a key on the field, and use some C/SIDe code tocheckthat the key is unique. Also you need to be carefull if this is a table with a lot of records,since this key is appended to the primary key, and the performance based on code will be different in C/SIDE and SQL.
Sorry to Kriki and Denster for cross posting, I am on an unfamiliar computer and didn’t see your posts.
Still, three MVPs saying the same thing somewhat impresses the importance of doing it properly [:D]
PS Alain, how great it would have been if I was in Milan yesterday [:(][:’(]
Just saying that it can be done, not that it is recommended. (Note the last sentence in my posting). Just another area where Navision’s implementation cripples SQL. Unique secondary keys are common practice in SQL databases.
Exactly, we should have that property available in NAV. We should also have an easy way to do SELECT DISTINCT for instance.
And the list goes on…
Mark and I came out crying from a SQL performance session at Tech Ed,where they were saying things like “never use cursors” "Avoid SELECT * " at all costs etc. It was pretty amazing to finally understand what the SQL experts have been saying for years about Navision’s implementation on SQL.
I have had the “pleasure” of working with the initial SQL releases of several products whose prior databases were ISAM based. Many of the products implemented on SQL by using cursors. IMHO, this was done to duplicate the record-by-record behavior of ISAM, and provide a faster time to market.