re-create table results in errno 150 on MySql-DB

Question

I created a table named "Payments" and later on realized, that I needed to change the names and datatypes of 2 of its columns. I dropped the table and wanted to create it again - but now I get the error-message: "Can't create table 'dev_datenbank.payment' (errno: 150)"

CREATE TABLE Payment(
payment_id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
payment_gemeindeid INTEGER NOT NULL,
payment_initiator TIMESTAMP,
payment_zyklus INTEGER,
payment_received BOOLEAN DEFAULT false
);
ALTER TABLE Payment ADD FOREIGN KEY(payment_gemeindeid) REFERENCES Gemeinde(gemeinde_id);

I looked at similar problems here, but I haven't found a solution. Most of the times when others encountered this problem, it had to do with tables having different datatypes on the FK columns. But in my case both are INTEGER. Also the database-type of all columns is 'InnoDB'.

I assume that the foreign key constraint has not correctly been removed from MySQL. I checked in the table KEY_COLUMN_USAGE in the information_schema but I cannot see any remains here.

The other table is created as follows:

CREATE TABLE Gemeinde
(gemeinde_id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
 gemeinde_name VARCHAR(50) NOT NULL,
 gemeinde_amt INTEGER,
 gemeinde_status INTEGER NOT NULL DEFAULT 1,
 gemeinde_info VARCHAR(512)
);

Thanks for the reply. I added the create statement to the main description. — Tongamann, Nov 11 '15 at 18:11
Both tables need to run on the same engine and fields must have the same type and length — Axel Amthor, Nov 11 '15 at 18:15
Both run on InnoDB and both are of type integer. Strangely it worked like this the first time I ran these statements but after dropping the table I can't run them again. — Tongamann, Nov 11 '15 at 18:35

score 0 · Answer 1 · answered Nov 11 '15 at 19:48

0

Create an index on payment_gemeindeid before using it as a foreign key.

answered Nov 11 '15 at 19:48

Honeyboy Wilson

175
2
5

I can't because I can't even create the table. Why would this help? As far as I have learned, indices help performance aspects - especially with large tables - but what effect does this have for my create table/foreign key problem? – Tongamann Nov 11 '15 at 23:09
Try adding the index and the foreign key constraint as part of the create table command. MySQL will only enforce a referential integrity constraint on a foreign key column if it is indexed (and the table is InnoDB). – Honeyboy Wilson Nov 12 '15 at 01:17
1

fancyPants, actually MySQL requires indexes on foreign keys and referenced keys. Later versions will automatically create an index on a foreign key, but earlier versions required that the index be explicitly created. – Honeyboy Wilson Nov 12 '15 at 12:43

score 0 · Answer 2 · edited May 23 '17 at 11:44

The problems I had, disappered after I updated my XAMPP from an old version (5.0.x) to a newer version (5.5.30). Now I can drop an recreate tables as expected.

Anyhow the hints with indexing my foreign keys was really helpful and I will start doing this from now on. I never paid much attention to this before, since my DBs were rather small. Thanks for your help.

Also reading through following discussion helped me get more understanding to this: Does a foreign key automatically create an index?

re-create table results in errno 150 on MySql-DB

2 Answers2