Sugar Developer Guide Version 5.1

Primary Keys, Foreign Keys, and GUIDs

By default, Sugar uses globally unique identification values (GUIDs) for primary keys for all database records. Sugar provides a create_guid() utility function for creating these GUIDs in the following format: aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee. The primary key column length is 36 characters.

The GUID format and value has no special meaning in Sugar other than the ability to match records in the DB. Sugar links two records (such as an Account record with a Contact record) with a specified ID in the record type relationship table (e.g. accounts_contacts).

Sugar allows a primary key to contain any unique string. This could either be a different GUID algorithm, a key that has some meaning (such as bean type first, followed by info), an external key, and/or auto-incrementing numbers (converted to strings). The Sugar development team chose GUIDs rather than auto-incrementing keys to allow for easier data synchronization across databases (in order to avoid primary key collisions). This data synchronization issue comes into play when the Sugar Offline Client (part of Sugar Enterprise) syncs data between the main Sugar installation and the Offline Client or when developers use the Sugar SOAP APIs or a tool like Talend for data synchronization.

The strategy of the Offline Client using GUIDs for primary keys is elegant in that it is very easy to implement and handling data conflicts is simpler than with other schemes. If a developer changes Sugar to use some other ID scheme and does need to accommodate data synchronization across data stores, then he would have to either partition the IDs ahead of time or work out a system similar to the Sugar implementation for Cases, Quotes and Bugs. For these modules, which have human-readable ID numbers (integers) that need to be synchronized across databases, Sugar implements a server ID that is globally unique and concatenates that with an incrementing Case, Quotes or Bug number. Attempting such a change to Sugar, while certainly possible, would require some careful planning and implementation.

However if the developer does not need to worry about data synchronization issues, then he can certainly change the primary key format to some other unique string.

Also, it is not a problem to import data from a previous system with one primary key format and then simply have all new records in Sugar use the GUID primary key format. All keys simply need to be stored as unique strings with no more than 36 characters.

To implement a new primary key method or to import existing data with a different primary key format and then rely on the existing GUID mechanism for new records, there are a few things to look out for:

•

The system expects primary keys to be string types and will format the SQL with quotes. If you change the primary key types to an integer type, you might have SQL errors to deal with since we put all ID values in quotes in our generated SQL. The database might be able to ignore this issue. MySQL running in Safe mode will have issues for instance.

•

Case-sensitivity can be an issue. IDs “abc” and “ABC” are typically treated the same in MySQL but represent different values in Oracle. Some other CRM systems from which people were migrating to SugarCRM were using case sensitive strings as their IDs on export. If this is the case, and you are running MySQL, you need to run an algorithm on the data to make sure all of the IDs are unique. One simple algorithm is to MD5 the ids that they provide. A quick check will let you know if there is a problem. If you imported 80,000 leads and there are only 60,000 in the system, some might have been lost due to non-unique primary keys resulting from this case-sensitivity issue.

•

Sugar only tracks the first 36 characters in the primary key. Any replacement primary key will either require changing all of the ID columns with one of an appropriate size or to make sure you don’t run into any truncation or padding issues. MySQL in some versions has had issues with Sugar where the IDs were not matching because it was adding spaces to pad the row out to the full size. MySQL’s handling of char and varchar padding has changed in some of the more recent versions. To protect against this, you will want to make sure the GUIDs are not padded with blanks in the DB.