restriction on no. of fields in primary key

GoMaD
GoMaD Member Posts: 313
edited 2007-06-07 in SQL General
Hi,

I'm currently doing a migration of a native DB to SQL. I'm using the migration script from NAV (with a little adjustment here and there to kick out some extra special characters).

But ....


during the restore of the navision backup (taken after the exec of the migration codeunit) I get an error that the primary key of the Invoice Posting Buffer table exceeds the restriction of 16 colums in the primary key. This results in a complete rollback.

When I look into the table definition of the Invoice Posting Buffer table I see that somebody has added some extra fields to the primary key and the key now holds 18 fields.

I always thought that the primary key could hold 20 fields, isn't that correct or is this only for native (as proven by this case, but I'm asking to be sure).

I'm glad that I can remove the two standard dimension fields from the primary key, and now I'm starting the backup/restore again.

Is there a work around this so I do not have to change the key definition, or can I add the deleted fields after the restore has completed succesfully?

Regards,
Now, let's see what we can see.
...
Everybody on-line.
...
Looking good!

Comments

  • David_Singleton
    David_Singleton Member Posts: 5,479
    The code using the Invoice Buffer is badly designed (by Navision). To save some lines of code they extended the primary key. In reality the primary key should have been an integer. I guess that since the record is only used as a TempTable they felt its OK, but what it means is that its very hard to customize the way GL posting of an invoice is made. In the UK this is quite a big issue, since Accountants are used to the idea of splitting up invoices to multiple GL accoutns, and automatic accruals, and this silly oversight makes that very difficult.

    Oh and take a look at the Sales Price table if you want to see where they also did this.

    A primary key should be designed well enough to be small AND allow future enhancements with out having to change it.

    But leaving al this aside, its weird (but true) that in Native you can have 20 fields in a primary key but in SQL only 16.
    David Singleton
  • GoMaD
    GoMaD Member Posts: 313
    As this is a 2.01 DB in a 3.70 client, there is no Sales Price table :)

    Thanks for the reply!
    Now, let's see what we can see.
    ...
    Everybody on-line.
    ...
    Looking good!
  • kriki
    kriki Member, Moderator Posts: 9,121
    I suppose it is a Belgian DB.
    If I remember correctly this problem is caused by extra fields Navision BE added for their version with multiple dimensions.
    I also encountered that problem at a certain time.
    I removed some not used fields from the key (like the USA Taxes fields that are not used in Belgium) and all worked fine.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Categories