This is an old revision of the document!
Key Violation
Each record in the SMS datbase (and most others) has a key field. This field uniquely identifies the record (row of data) and is used by the database to quickly find the record. In SMS tables, the first column in the table (usually ending in “ID”) is used as the key field.
A “Key Violation” is the computerese phrase saying that you are trying to save a record with the same “key” as one already in the database. Since this field must be unique, this is not allowed.
Steps
Make sure all users are out of SMS while the following is being done.
Open Database Browser
Look for Bad Data At the Top of Each Table
- Delete any blank records at the top of the database (no entries in ANY field in a given row).
- Records with blank key fields. If the data looks meaningful, consult technical support before deleting this record.
- If a blank ID records turns up with valid information, then an unused ID number should be assigned to the record.
- Delete any large negative values records at the top of the database (i.e. -117346859).
- Renumber any normal size negative number with the positive value (i.e. -3456 rekey as 3456).
- If you get a key violation renumbering it (meaning the positive record already exists), delete the negative record.
Query the Tables for Problems
- Run these all queries for the following tables and associated IDs as appropriate:
- Adjustment Database - AdjustmentID
- Contact Database - ContactID
- Customer Database - CustomerID
- Estimate Database - OrderID
- Order Database - OrderID
- Order Details Database - OrderDetailID
- Estimate Details Database - OrderDetailID
- Estimate Modifiers Database - ID
- Order Modifiers Database - ID
- Deleted Records Database - ID (SMS > 8.9)
- GL Database - ID
- Payments Database - PaymentID
- Product Database - ProductID
- Deleted Records (only in 8.9)
- Run SQL 1 to locate conflicting ID Numbers.
- Each record in a database must have a unique and distinct primary key ID number (i.e. Contact Database uses “ContactID” as a primary key in this database and the ContactID field is a foreign key in other tables which are linked to it).
- If an entry is posted, such as updating a contact record using ContactID # 5642 and one already exists for a different contact, then this will result in an immediate key violation. The ID number must be changed to one that is unused.
- If the information is the same in both records, you will need to delete one of the 2 records.
- In some cases, the primary key is made up of two fields like the GL (i.e. the ID and the StoreID make up the primary key.)
- If you have an ID that is the same, but the information is different in this case, you will need to assign a new ID
- If you have duplicate ContactIDs in the Contact table, use SQL 2 to correct all the appropriate tables:
- SQL 3 (far below) can be used to fix duplicate keys in other tables.
SQL 1
SELECT ContactID, COUNT (ContactID) AS CustCNT FROM "Contact Database" GROUP BY ContactID ORDER BY CustCNT DESC
- Note that any word can be substituted for “Contact” above in each area where the word is found in the query, depending on the database in which you're running the search
- Example :
SELECT OrderID, COUNT(OrderID) AS CustCNT FROM "Order Database" GROUP BY OrderID HAVING COUNT(OrderID) > 1 ORDER BY CustCNT DESC
SQL 2
// GET a list OF ALL duplicated contacts SELECT ContactID FROM "Contact Database" GROUP BY ContactID HAVING COUNT(cONTACTid) > 1 ; DupContactList CREATE INDEX TempContactIndex1 ON DupContactList (ContactID) ; // GET the Contact, Customer pairing SELECT ContactID AS OldContactID, CustomerID, ContactID AS NewContactID FROM "Contact Database" WHERE ContactID IN (SELECT ContactID FROM DupContactList) ; DupContacts // Make sure you reset the NewContactID TO be greater than the exsting IDs // UPDATE the 22000 TO the LAST ID IN the DATABASE UPDATE DupContacts D1 SET NewContactID = OldContactID + 22000 WHERE CustomerID IN (SELECT MAX(CustomerID) FROM DupContacts AS D2 WHERE D2.OldContactID = D1.OldContactID ) ; CREATE INDEX TempContactIndex2 ON DupContacts (OldContactID) ; // UPDATE the Contact DATABASE UPDATE "Contact Database" C SET ContactID = (SELECT NewContactID FROM DupContacts D WHERE D.OldContactID = C.ContactID AND D.CustomerID = C.CustomerID) WHERE ContactID IN (SELECT ContactID FROM DupContactList) ; // UPDATE the ORDER DATABASE UPDATE "Order Database" O SET ContactID = (SELECT NewContactID FROM DupContacts D WHERE D.OldContactID = O.ContactID AND D.CustomerID = O.CustomerID) WHERE ContactID IN (SELECT ContactID FROM DupContactList) ; // UPDATE the ORDER DATABASE UPDATE "Estimate Database" O SET ContactID = (SELECT NewContactID FROM DupContacts D WHERE D.OldContactID = O.ContactID AND D.CustomerID = O.CustomerID) WHERE ContactID IN (SELECT ContactID FROM DupContactList) ; // UPDATE the Customers PRIMARY Contact ID UPDATE "Customer Database" C SET PrimaryContactID = (SELECT NewContactID FROM DupContacts D WHERE D.OldContactID = C.PrimaryContactID AND D.CustomerID = C.CustomerID) WHERE PrimaryContactID IN (SELECT ContactID FROM DupContactList) ; // UPDATE the Customers AP Contact ID UPDATE "Customer Database" C SET APContactID = (SELECT NewContactID FROM DupContacts D WHERE D.OldContactID = C.APContactID AND D.CustomerID = C.CustomerID) WHERE APContactID IN (SELECT ContactID FROM DupContactList) ;
- SQL 3 can be used to fix duplicate keys in the Adjustment, GL, or Payments tables.
SQL 3
SELECT ID, COUNT (ID) AS CNT FROM "GL Database" GROUP BY ID ORDER BY CNT DESC ;tempa SELECT ID FROM "tempa" WHERE CNT > 1 ;tempb UPDATE "GL Database" SET ID = ID + 1XXXXXX WHERE ID IN (SELECT ID FROM Tempb) AND StoreID = 1
note: XXXXXX should represent the number places of the last entry in the table, so ID goes from XXXXXX (6 characters) to 1XXXXXX (1+6 characters). You must then manually resequence the new IDs
- SQL 4
This is only used for a large quantity of duplicate IDs in the GL and Payments tables. It can NOT be used on any other table due to potential key issues.
Only use this SQL if you have more than 50 duplicate IDs
SQL 4
SELECT ID AS OldID, COUNT (ID) AS CNT, MIN(StoreID) AS FirstStoreID, MAX(StoreID) AS LastStoreID, CAST(0 AS INTEGER) NewID FROM "GL Database" GROUP BY ID HAVING COUNT(ID) > 1 ORDER BY CNT DESC ;TempA2 CREATE INDEX TempNewIDIndexB ON TempA2 (OldID, LastStoreID) ; UPDATE TempA2 T1 SET NewID = 1 + (SELECT MAX(ID) FROM "GL Database") + (SELECT COUNT(*) FROM TempA2 T2 WHERE T2.OldID < T1.OldID) ; UPDATE "GL Database" GL SET ID = (SELECT NewID FROM TempA2 T WHERE GL.ID = T.OldID), StoreID = 1 WHERE StoreID = (SELECT LastStoreID FROM TempA2 T WHERE GL.ID = T.OldID) ; SELECT * FROM TempA2
Note: You will need to adjust the Table name and ID name