create database priceTag;
use priceTag;

CREATE TABLE `ProductNumber` (
  `Sku` INT auto_increment not null,
  `Model` VARCHAR(100),
  PRIMARY KEY (`Sku`)
);

ALTER TABLE ProductNumber AUTO_INCREMENT=60000;


CREATE TABLE `Manufacture` (
  `Manufacture` VARCHAR(100),
  `Model` VARCHAR(100),
  `Category` VARCHAR(100),
  PRIMARY KEY (`Model`)
);

CREATE TABLE `OpenBox` (
  `Condtion` VARCHAR(100),
  `LP` INT auto_increment not null,
  `MissingItems` VARCHAR(100),
  `Model_` VARCHAR(100),
  FOREIGN KEY (`Model_`) REFERENCES `Manufacture`(`Model`),
  PRIMARY KEY (`LP`)
);

ALTER TABLE OpenBox AUTO_INCREMENT=200000000;

CREATE TABLE `TAG` (
  `SKU*` INT,
  `Model*` VARCHAR(100),
  `PRICE*` DECIMAL(10,2),
  `LP*` INT,
  `condtion*` VARCHAR(100), 
  FOREIGN KEY (`SKU*`) REFERENCES `ProductNumber`(`Sku`),
  FOREIGN KEY (`Model*`) REFERENCES `Manufacture`(`Model`),
  FOREIGN KEY (`LP*`) REFERENCES `OpenBox`(`LP`),
  FOREIGN KEY (`condtion*`) REFERENCES `OpenBox`(`condtion`)
);

CREATE TABLE `Inventory` (
  `INV` int,
  `Sku!` int,
  `Model!` VARCHAR(100),
  FOREIGN KEY (`Sku!`) REFERENCES `ProductNumber`(`Sku`),
  FOREIGN KEY (`Model!`) REFERENCES `Manufacture`(`Model`)
);

CodePudding user response：

The column which you refer on in FOREIGN KEY (`condtion*`) REFERENCES `OpenBox`(`condtion`) (i.e. OpenBox.condtion) is not indexed.

Add needed unique index creation then create TAG table.

DEMO

CodePudding user response：

I tested your code, and then ran SHOW ENGINE INNODB STATUS to get more detailed information about the error.

---

LATEST FOREIGN KEY ERROR

2021-12-02 09:47:16 0x700007565000 Error in foreign key constraint of table test2/tag: FOREIGN KEY (condtion*) REFERENCES OpenBox(condtion) ):

Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint.

It's complaining about this foreign key:

FOREIGN KEY (`condtion*`) REFERENCES `OpenBox`(`condtion`)

The OpenBox.condtion column is not a primary key or unique key. Foreign keys must reference a key of the parent table.

You already have another foreign key in your TAGS table referencing the OpenBox table. Are you intending that the condtion column of the respective row be copied to the TAGS table? That's not how foreign keys are intended to be used.