Devart Forums

We are working on transferring data from several identical MySQL databases to SQLite 3 DB.
However, there are databases in which no errors have occurred and databases in which errors occur during conversion.
I don't know what the problem is.
The table structure is all the same.
Below is a table where many error messages occur.

Error message: UNIQUE construct failed: SaleSub.Su_Code

I accessed one of the several databases and checked it.
The total number of data in the SaleSub table is about 270,000, but when you access the table after conversion and check it, about 14,000 are converted.
Below is the table structure with errors.

//----- MySQL ---

CREATE TABLE 'SaleSub' (
'Su_Code' INT(11) NOT NULL AUTO_INCREMENT,
'S_Code' INT(11) NOT NULL DEFAULT '0',
'Su_Date' VARCHAR(10) NULL DEFAULT NULL,
'Su_Count' INT(11) NOT NULL DEFAULT '0',
'A_Code' VARCHAR(20) NULL DEFAULT NULL,
'Su_Article' VARCHAR(120) NULL DEFAULT NULL,
'Su_Size' VARCHAR(60) NULL DEFAULT NULL,
'Su_Unit' VARCHAR(5) NULL DEFAULT NULL,
'Su_Amount' FLOAT NULL DEFAULT NULL,
'Su_Money' INT(11) NULL DEFAULT NULL,
'Su_Total' INT(11) NULL DEFAULT NULL,
'Sto_Code' SMALLINT(3) UNSIGNED NULL DEFAULT NULL,
'Sto_Name' VARCHAR(30) NULL DEFAULT NULL,
'MC_Code' SMALLINT(3) UNSIGNED NULL DEFAULT NULL,
'MC_Name' VARCHAR(30) NULL DEFAULT NULL,
'Su_Check1' SMALLINT(1) UNSIGNED NULL DEFAULT '0',
'A_Set' SMALLINT(1) UNSIGNED NULL DEFAULT '0',
'AMK_Code' VARCHAR(20) NULL DEFAULT NULL,
'Su_inMoney' INT(11) UNSIGNED NULL DEFAULT '0',
'Su_PriceMoney' INT(11) NULL DEFAULT '0',
'Su_Check2' SMALLINT(1) NULL DEFAULT '0',
'OrSub_Code' TEXT NULL DEFAULT NULL,
'Su_Memo' TEXT NULL DEFAULT NULL,
PRIMARY KEY ('Su_Code', 'S_Code', 'Su_Count') USING BTREE,
INDEX 'suscode' ('S_Code') USING BTREE,
INDEX 'suacode' ('A_Code') USING BTREE,
INDEX 'suarticle' ('Su_Article') USING BTREE
)
ENGINE=InnoDB
;


//----- SQLite 3

CREATE TABLE SaleSub (
Su_Code INTEGER PRIMARY KEY AUTOINCREMENT,
S_Code int NOT NULL default '0',
Su_Date varchar(10) default NULL,
Su_Count int NOT NULL default '0',
A_Code varchar(20) default NULL,
Su_Article varchar(120) default NULL,
Su_Size varchar(60) default NULL,
Su_Unit varchar(10) default NULL,
Su_Amount decimal(12,2) default NULL,
Su_Money int default NULL,
Su_Total int default NULL,
Sto_Code int default NULL,
Sto_Name varchar(30) default NULL,
MC_Code int default NULL,
MC_Name varchar(30) default NULL,
Su_Check1 int default '0',
A_Set int default '0',
AMK_Code varchar(20) default NULL,
Su_inMoney int default '0',
Su_PriceMoney int default '0',
AC_Code1 int default '0',
AC_Name1 varchar(50) default NULL,
AC_Code2 int default '0',
AC_Name2 varchar(50) default NULL,
Su_Check2 int default '0',
OrSub_Code text,
Su_Memo text
);

Create INDEX suscode on SaleSub (S_Code);
Create INDEX suacode on SaleSub (A_Code);
Create INDEX suarticle on SaleSub (Su_Article);
Create INDEX susize on SaleSub (Su_Size);

Hi There,

In you sample MySQL key has 3 fields, and in SQLite table only 1 field.

Accordingly, in MySQL table the uniqueness of the primary key is checked by three fields, and the Su_Code field may contain duplicated values.

This is where the error occurs when inserting data into a SQLite table, where you set the primary key only by the Su_Code field, and this field should not have duplicate values.

Best regards,
Evgeniy

Thank you.

I missed that the default key for MySQL Table is three.
Thanks to you, I finished the work well.

Hi There,
I’m glad that your issue is resolved and you can use all features of the software. Please feel free to contact us anytime in case you have any other questions.

Best regards,
Evgeniy