====== 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 ====
- Make a backup of the ~\Cyrious\SMS\Data folder by copying the folder and then renaming it with today's date.
- Run the [[:dbrebuild|DBRebuild]].
- Run the [[:id_repair|IDRepair]] utility (unless customer has a flag stating otherwise)
- Open the DBBrowser.
==== 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 ====
**Before running queries**
**Determining what tables need to be checked will save time**
> (i.e. Customer reports the error when editing an estimate, check the Estimate, Estimate Details, and Estimate Modifiers table; Customer reports the error when posting a payment, check the Adjustment, GL, and Payment tables; when editing an order, check the Adjustment, GL, Order, Order Details, and Order Modifiers tables; if not sure run all the major tables)
- 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