MySQL Duplicate on Insert

Question

First table

  $sql = "CREATE TABLE if not exists mainInfo (
sku varchar(20) primary key not null,
name varchar(20) not null,
price int(30) not null,
type int(2) not null
)";

Second Table

$sql="CREATE TABLE if NOT EXISTS properties (
size int(9),
bWeight int(9),
fHeight int(9),
fWeight int(9),
fLenght int(9),
sku varchar(20) not null,
CONSTRAINT FK_mainInfoProperties FOREIGN KEY (sku) REFERENCES mainInfo(sku)
)";

Inner join table

      $sql = "CREATE TABLE if NOT EXISTS allInfo (
        sku varchar(20) primary key not null,
        name varchar(20) not null,
        price int(30) not null,
        type int(2) not null,
        size int(9),
        bWeight int(9),
        fHeight int(9),
        fWeight int(9),
        fLenght int(9)
         )";

       $sql = "INSERT INTO allInfo (sku, name, price, type, size, bWeight, 
       fHeight, fWeight, fLenght)
        SELECT mainInfo.sku, name, price, type, size, bWeight, fHeight, 
       fWeight, fLenght
        FROM mainInfo INNER JOIN properties
        ON mainInfo.sku = properties.sku";

First time i use this code it works, but when i add new rows to first and second table, inner join table doesn't update it, giving me duplicate entry for key 'PRIMARY' how can i update this table adding new rows but leaving the ones that are already there untouched?

Answer 1

Mark Suk Field in allInfo as foreign key and add new primary key like allInfoId to identify record uniquely