CODEWITHSUNDEEP
mysqlsql
Christopher Volpacchio
Christopher Volpacchio

Reputation: 25

Table creation troubles foreign key error message

As I continuously try to create a table with a couple of foreign key constraints I am running into a 'errno: 150' that gives no descriptive insight as to what is wrong with the table.

I have tried putting both foreign key constraints at the end of my code, spacing the words 'FOREIGN KEY' between the field reference but I was presented with the same error, with no detail as to what was wrong. Also, both of the tables being referenced exist.

CREATE TABLE line_Sales(
    customer_id INT NOT NULL, 
    FOREIGN KEY(customer_id) REFERENCES d_customer(customer_id), 
    order_number INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    total_amt INT NOT NULL, 
    order_Date DATE, 
    product_key INT NOT NULL, 
    FOREIGN KEY(product_key) REFERENCES product_table(product_key), 
    purchase_channel VARCHAR(10)
);

the error is 1005 specifically errno:150

Upvotes: 1

Views: 195

Answers (1)

spencer7593
spencer7593

Reputation: 108510

The generic nature of the MySQL error message is MySQL relaying an error returned from the InnoDB storage engine. The underlying problem is that InnoDB is refusing to create the foreign key constraint.

There's a number of possible reasons. The Usual reasons for InnoDB refusing to create foreign key constraints:

  • the datatypes of the referencing column(s) are not identical to the referenced columns
  • the referenced table name does not exist in the database
  • the referenced table is not InnoDB storage engine
  • the referenced column(s) do not exist in the referenced table
  • the referenced column(s) are not leading columns in an index

This list is not exhaustive. This is just a few of the most common reasons for the error.

As an aside... we should note that table names are case sensitive on some platforms with some file systems and some system variables settings.

Excerpt from MySQL Reference Manual:

it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use.

https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html

Suggestions:

Check the definitions of the referenced tables i.e.

SHOW CREATE TABLE product_table ;
SHOW CREATE TABLE d_customer ;

compare the column names, datatypes, check that appropriate indexes exist.

Also we can create the table without foreign key constraints, and then attempt to add foreign key(s) in separate statement(s). This should help us narrow down the problem.

CREATE TABLE line_Sales (
customer_id       INT NOT NULL,
order_number      INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
total_amt         INT NOT NULL,
order_Date        DATE,
product_key       INT NOT NULL,
purchase_channel  VARCHAR(10)
);

Then as separate steps:

ALTER TABLE line_Sales
ADD CONSTRAINT FK_line_Sales_d_customer 
  FOREIGN KEY(customer_id) REFERENCES d_customer(customer_id) 
  ON UPDATE CASCADE ON DELETE RESTRICT ;

and 

ALTER TABLE line_Sales
ADD CONSTRAINT FK_line_Sales_product
  FOREIGN KEY(product_key) REFERENCES product_table(product_key) 
  ON UPDATE CASCADE ON DELETE RESTRICT ;

Note:

We expect that product_table exists and is InnoDB storage engine. We expect the column product_key exists in product_table. We expect that the datatype of product_key column matches exactly the datatype of the product_key column in line_sales. We expect there is an index defined on product_table that has product_key as the leading column. et al.

Upvotes: 2

Related Questions