0

Background:

Yesterday I was creating a small database for practice, and created about 9 tables, and got this error in the create table statement of only one of them. The question about that is located here. Thankfully somebody told an alternative way by creating the table without the foreign key and then use the alter table statement to add the foreign key constraint, and it worked.

Today I am creating another database, and I am getting this error in all the tables which contain foreign keys. I have tried the alternative way of creating the table and then adding foreign keys by alter table statement. But it doesn't seem to help.

My Research:

I know this question has been been addressed before on this website, but I have tried the solutions on this, this page, except adding indexes because firstly, I don't need them in such a small database (second point in the first answer), and secondly I don't know them and I want to keep it simple. None of those answers helped.

Moreover, as it is mentioned here, " If the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. "

Please tell me what's wrong with my foreign key constraint.

CREATE TABLE table1 (
    table1_id INT(11) AUTO_INCREMENT,
    name VARCHAR(5000),
    code VARCHAR(5000),
    color VARCHAR(5000),
    PRIMARY KEY (table1_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE table2( -- error code 1005 can t create table errno 150 mysql
    table2_id INT(11) AUTO_INCREMENT,
    date DATE,
    start_time TIME,
    end_time TIME,
    table1_id INT(11) COMMENT 'FK FROM table1',
    PRIMARY KEY (table2_id),
    FOREIGN KEY (table1_id)
        REFERENCES table1(table1_id) 
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

So any hints/tips on this?

Community
  • 1
  • 1
Solace
  • 8,612
  • 22
  • 95
  • 183
  • 3
    works for me: http://sqlfiddle.com/#!2/1ddf4/1 –  Apr 22 '14 at 13:44
  • @a_horse_with_no_name Does that mean there is something wrong with my MySQL and I should reinstall it? Even in yesterday's question, somebody suggested that it might be an "undiagnosable problem in the client"... – Solace Apr 22 '14 at 13:49
  • @Zarah If you get rid of the database you've previously created, recreate it, and issue the SQL you've shown in your question, do you still get the error? That would be the first thing I'd try if I were you, rather than reinstalling MySQL. In your question you mention using `ALTER` statements but the SQL you show in the question does not use `ALTER`. So perhaps you just lost track of the state of your database, and the `ALTER` statements did not work because of this. – Louis Apr 22 '14 at 14:02
  • Just tried it. Dropped the database. Then created again. It's the same error again. Regarding the `ALTER` statement, I tried it that way but that did not help either. I had tried that by recreating the database. DO you suggest me to reinstall MySQL now? I personally don't think "bad formed foreign key constraints" are a problem in the MySQL, but I am a beginner so I am asking you. – Solace Apr 22 '14 at 14:20
  • @Zarah I've never had a case where I needed to reinstall MySQL to fix an error like this. If the labor required to reinstall is not great, then I guess you can try reinstalling and see if the problem goes away. – Louis Apr 22 '14 at 15:13

0 Answers0