2

I have a CSV that's formatted like this:

MasterSKU, Location, Notes, SubSku1, SubSku2, SubSku3, SubSku4, SubSku5, ...

I have two tables in a MySQL database designed like this:

CREATE TABLE `master_sku` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `master_sku` varchar(150) NOT NULL,
  `bin` varchar(150) DEFAULT NULL,
  `note` varchar(250) DEFAULT NULL,
  `createdtime` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

CREATE TABLE `sub_sku` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `master_sku_id` int(11) NOT NULL,
  `sub_sku` varchar(150) NOT NULL,
  `createdtime` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

So what I need to do is allow a user to upload the CSV and then I insert the values into master_sku and sub_sku respectively. Some master_skus won't have sub skus, some will have many sub skus.

Is there a way with foreign keys to do an insert into both tables at the same time?

I also want the user to be able to upload a CSV with additional sub skus later that may match existing master_skus, and only insert the new sub skus.

wirikidor
  • 33
  • 3

1 Answers1

1

You cannot insert rows "at the same time" but you can ensure that the rows in master_skus and sub_skus will be inserted in the database or both will fail. That can be achieved using a database transaction.

Tarik
  • 10,810
  • 2
  • 26
  • 40