Identity increment in CRM tables

Primary keys in SQL tables allow us to uniquely identify the rows. These keys also allow us to establish the relationship between tables based on foreign key-primary key concept.

Though we can have different id field names in CRM tables, the standard primary key feature is well maintained with field names like “_” (e.g. Case_CaseId). This is nothing but the primary key in SQL table of CRM database. A good normalized database requires not having any duplicate values in this field over the rows. Well, this is quite specific and this can be also done in SQL tables setting some column properties, but the approach being used in CRM for identity increment is quite good.

Let’s see how this works for Cases table.

    •    Get the max id value from Cases table
          select max(case_caseid) from cases
          Lets say this gives you the value 6010.

    •    Now check the table id for Cases table.
          select * from custom_tables where bord_name=‘Cases’
          Let’s say the table id is 3.

    •    Now see what will be the next id for this table.
         select * from sql_identity where id_tableid=3

It must be 6011 or some value greater than 6010 which is max id of case table.

Every time we make use of standard methodologies to create records in CRM tables, the id value is incremented by 1 and updated in “SQL_identity” table through “crm_next_id” stored procedure.

Pro’s and Con’s:

Usage through CRM and custom built pages using standard methods are well handled through above concept. We never need to find and set the primary key value manually.

Keeping the next id always in linked table make it available for all the tools creating record in that table and avoid duplications.

We need to always make sure that whatever concept of data insert we design for our table be it Trigger, procedure anything we always update the next id in SQL_identity table.

Why do we need to this? Let’s consider an example where the next id for the table is set to 6010 in SQL_identity table and max record id is 6009. Now you migrate some 100 records in that table say using Trigger, procedure or some third party tool. Now the max id is set to 6109, but till now we have not updated the value of next id in SQL_identity table. Now suppose for this table there is a Custom page or interface in CRM which uses CreateRecord function to create record. This will read the next id from SQL_identity table and find it to be. Now the internally created insert query will have id field values as 6010,but the record for this id already exists in the table and hence you end up getting below error

And the error in log file says

Cannot insert duplicate key row in object ‘dbo.Cases’ with unique index ‘IDX_Case_CaseId’

Each person in this world has his/her own identities then why SQL table rows can’t have the same?? JJ..   

If you find this content useful, please feel free to drop us an email at crm@greytrix.com.