List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:November 28 2004 3:11pm
Subject:Re: How to retrieve constraints and links from MySQL tables?
View as plain text  
Varakorn Ungvichian wrote:
> Just realised something: my table was MyIsam. I
> switched it to InnoDB (which support FKs), but now:
> 1. "Show Create Table" still doesn't show reference
> links, and

Well, of course not.  It won't show them until you create them, which you 
haven't done according to #2.

> 2. I can't seem to make foreign keys, because it
> generates the error: "ERROR 1005: Can't create table
> '.\ltab\#sql-60c_11.frm' (errno: 150)". It happens
> through both defining it as part of Create Table, and
> trying to add it later with Alter Table ("alter table
> employees add foreign key (position_id) references
> positions(position_id);").

If both are InnoDB, this is usually due to a missing index.  From the manual 
<http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html>, 
"Both tables must be InnoDB type. In the referencing table, there must be an 
index where the foreign key columns are listed as the first columns in the 
same order. In the referenced table, there must be an index where the 
referenced columns are listed as the first columns in the same order. Index 
prefixes on foreign key columns are not supported."

> Varakorn Ungvichian

Michael
Thread
How to retrieve constraints and links from MySQL tables?Varakorn Ungvichian26 Nov
  • Re: How to retrieve constraints and links from MySQL tables?Martijn Tonies26 Nov
    • Re: How to retrieve constraints and links from MySQL tables?Varakorn Ungvichian28 Nov
    • Re: How to retrieve constraints and links from MySQL tables?Varakorn Ungvichian28 Nov
      • Re: How to retrieve constraints and links from MySQL tables?Martijn Tonies28 Nov
      • Re: How to retrieve constraints and links from MySQL tables?Michael Stassen28 Nov
        • Re: How to retrieve constraints and links from MySQL tables?Varakorn Ungvichian29 Nov
          • Re: How to retrieve constraints and links from MySQL tables?Michael Stassen29 Nov
RE: How to retrieve constraints and links from MySQL tables?Mechain Marc26 Nov
  • Re: How to retrieve constraints and links from MySQL tables?Martijn Tonies26 Nov