{"id":259,"date":"2010-09-23T11:09:00","date_gmt":"2010-09-23T11:09:00","guid":{"rendered":"http:\/\/www.greytrix.com\/blogs\/sagecrm\/?p=259"},"modified":"2010-09-23T11:09:00","modified_gmt":"2010-09-23T11:09:00","slug":"identity-increment-in-crm-tables","status":"publish","type":"post","link":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2010\/09\/23\/identity-increment-in-crm-tables\/","title":{"rendered":"Identity increment in CRM tables"},"content":{"rendered":"

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.<\/p>\n

Though we can have different id field names in CRM tables, the standard primary key feature is well maintained with field names like \u201c_\u201d (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.<\/p>\n

Let\u2019s see how this works for Cases<\/strong> table.<\/p>\n

    \u2022    Get the max id value from Cases table
          select<\/span> max<\/span>(case_caseid) from<\/span> cases
          Lets say this gives you the value 6010<\/span>.<\/p>\n

    \u2022    Now check the table id for Cases table.
          select<\/span> * from<\/span> custom_tables where<\/span> bord_name=‘Cases’<\/span>
          Let\u2019s say the table id is 3<\/span>.<\/p>\n

    \u2022    Now see what will be the next id for this table.
         select<\/span> * from<\/span> sql<\/span>_identity where<\/span> id_tableid=3<\/p>\n

It must be 6011 or some value greater than 6010 which is max id of case table.<\/p>\n

Every time we make use of standard methodologies to create records in CRM tables, the id value is incremented by 1 and updated in \u201cSQL_identity\u201d table through \u201ccrm_next_id\u201d stored procedure. <\/p>\n

Pro\u2019s and Con\u2019s:<\/b><\/p>\n

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.<\/p>\n

Keeping the next id always in linked table make it available for all the tools creating record in that table and avoid duplications.<\/p>\n

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. <\/p>\n

Why do we need to this? Let\u2019s 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<\/strong> function to create record. This will read the next id from SQL_identity<\/strong> table and find it to be. Now the internally created insert query will have id field values as 6010<\/strong>,but the record for this id already exists in the table and hence you end up getting below error<\/p>\n

And the error in log file says<\/p>\n

Cannot insert duplicate key row in object ‘dbo.Cases’ with unique index ‘IDX_Case_CaseId’<\/em><\/p>\n

Each person in this world has his\/her own identities then why SQL table rows can\u2019t have the same?? <\/span>JJ<\/span><\/span><\/span>..  <\/span><\/span> <\/span><\/span><\/p>\n

If you find this content useful, please feel free to drop us an email at crm@greytrix.com<\/a>. <\/b><\/marquee><\/p>\n","protected":false},"excerpt":{"rendered":"

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 \u201c_\u201d (e.g. Case_CaseId).\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[649,853,1377,2325,2402],"class_list":["post-259","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-case","tag-custom","tag-identity","tag-sql","tag-table"],"yoast_head":"\nIdentity increment in CRM tables - Sage CRM \u2013 Tips, Tricks and Components<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2010\/09\/23\/identity-increment-in-crm-tables\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Identity increment in CRM tables - Sage CRM \u2013 Tips, Tricks and Components\" \/>\n<meta property=\"og:description\" content=\"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 \u201c_\u201d (e.g. Case_CaseId).\u2026 Read More »\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2010\/09\/23\/identity-increment-in-crm-tables\/\" \/>\n<meta property=\"og:site_name\" content=\"Sage CRM \u2013 Tips, Tricks and Components\" \/>\n<meta property=\"article:published_time\" content=\"2010-09-23T11:09:00+00:00\" \/>\n<meta name=\"author\" content=\"greysagecrm\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"greysagecrm\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2010\/09\/23\/identity-increment-in-crm-tables\/\",\"url\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2010\/09\/23\/identity-increment-in-crm-tables\/\",\"name\":\"Identity increment in CRM tables - Sage CRM \u2013 Tips, Tricks and Components\",\"isPartOf\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website\"},\"datePublished\":\"2010-09-23T11:09:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2010\/09\/23\/identity-increment-in-crm-tables\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2010\/09\/23\/identity-increment-in-crm-tables\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2010\/09\/23\/identity-increment-in-crm-tables\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Identity increment in CRM tables\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website\",\"url\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/\",\"name\":\"Sage CRM \u2013 Tips, Tricks and Components\",\"description\":\"Explore the possibilities with Sage CRM insights through our comprehensive blogs. As a leading Sage partner, Greytrix helps businesses maximize their Sage CRM potential with its rich expertise and immense knowledge. Here, you will find blogs that feature expert advice, tips & tricks, best practices, and comprehensive guides on customizing and configuring Sage CRM for your business. Stay informed with our regular updates and expert insights!\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f\",\"name\":\"greysagecrm\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g\",\"caption\":\"greysagecrm\"},\"url\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/author\/greysagecrm\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Identity increment in CRM tables - Sage CRM \u2013 Tips, Tricks and Components","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2010\/09\/23\/identity-increment-in-crm-tables\/","og_locale":"en_US","og_type":"article","og_title":"Identity increment in CRM tables - Sage CRM \u2013 Tips, Tricks and Components","og_description":"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 \u201c_\u201d (e.g. Case_CaseId).\u2026 Read More »","og_url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2010\/09\/23\/identity-increment-in-crm-tables\/","og_site_name":"Sage CRM \u2013 Tips, Tricks and Components","article_published_time":"2010-09-23T11:09:00+00:00","author":"greysagecrm","twitter_card":"summary_large_image","twitter_misc":{"Written by":"greysagecrm","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2010\/09\/23\/identity-increment-in-crm-tables\/","url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2010\/09\/23\/identity-increment-in-crm-tables\/","name":"Identity increment in CRM tables - Sage CRM \u2013 Tips, Tricks and Components","isPartOf":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website"},"datePublished":"2010-09-23T11:09:00+00:00","author":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f"},"breadcrumb":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2010\/09\/23\/identity-increment-in-crm-tables\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.greytrix.com\/blogs\/sagecrm\/2010\/09\/23\/identity-increment-in-crm-tables\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2010\/09\/23\/identity-increment-in-crm-tables\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/"},{"@type":"ListItem","position":2,"name":"Identity increment in CRM tables"}]},{"@type":"WebSite","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website","url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/","name":"Sage CRM \u2013 Tips, Tricks and Components","description":"Explore the possibilities with Sage CRM insights through our comprehensive blogs. As a leading Sage partner, Greytrix helps businesses maximize their Sage CRM potential with its rich expertise and immense knowledge. Here, you will find blogs that feature expert advice, tips & tricks, best practices, and comprehensive guides on customizing and configuring Sage CRM for your business. Stay informed with our regular updates and expert insights!","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f","name":"greysagecrm","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g","caption":"greysagecrm"},"url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/author\/greysagecrm\/"}]}},"_links":{"self":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/posts\/259","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/comments?post=259"}],"version-history":[{"count":0,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/posts\/259\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/media?parent=259"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/categories?post=259"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/tags?post=259"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}