Why don't you want to create a table?

Question

MySQL

CREATE TABLE IF NOT EXISTS `offers_representative` (
  `offers_representative_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `customers_id` INT NOT NULL,
  `products_id` INT NOT NULL,
  `products_price` DECIMAL( 14, 6 ) NOT NULL,
  FOREIGN KEY(customers_id) REFERENCES customers(customers_id),
  FOREIGN KEY(products_id) REFERENCES products(products_id)
) ENGINE=InnoDB;

Mistake

#1005 - Can't create table 'project.offers_representative' (errno: 150)

This problem is related to `FOOREIGN KEY`, but what is the correct way to write them?

Answer 1

CREATE TABLE IF NOT EXISTS `offers_representative` (
  `offers_representative_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `customers_id` INT NOT NULL,
  `products_id` INT NOT NULL,
  `products_price` DECIMAL( 14, 6 ) NOT NULL,
  CONSTRAINT `offers_representative_ibfk_1` FOREIGN KEY(`customers_id`) REFERENCES `customers` (`customers_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `offers_representative_ibfk_2` FOREIGN KEY(`products_id`) REFERENCES `products` (`products_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

However, the `customers` and `products` tables must be created before this table is created. The keys `customers_id` and `products_id` must also be specified. Instead of `CASCADE` there can be `RESTRICT` or `SET NULL`.
By the way, you may have confused with `REFERENCES `customers` (`customers_id`)`. Maybe you wanted to specify this: REFERENCES `customers` (`id`). Those. REFERENCES should contain the table you are referring to and its primary key (column).

Answer 2

The details of why the foreign key could not be hung can be found in a "logical and obvious" place: in the output of the SHOW ENGINE INNODB STATUS command, there is a piece of text LATEST FOREIGN KEY ERROR.
Common mistakes are int field in one table, unsigned int in another. These are different, incompatible types, aha.