InnoDB foreign key constraint

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Morten

    InnoDB foreign key constraint


    Hi. Creating the below to tables give errno 150, any idea why?

    CREATE TABLE categories (
    id int(11) unsigned NOT NULL auto_increment,
    name varchar(64) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY name (name)
    ) TYPE=InnoDB;

    CREATE TABLE types (
    id int(11) unsigned NOT NULL auto_increment,
    category_id int(11) unsigned NOT NULL,
    name varchar(64) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (category_id) REFERENCES categories(id)
    ) TYPE=InnoDB;

    Br,

    Morten

  • Heikki Tuuri

    #2
    Re: InnoDB foreign key constraint

    Morten,

    please use

    SHOW INNODB STATUS;

    to print a detailed error explanation.

    Best regards,

    Heikki Tuuri
    Innobase Oy
    Foreign keys, transactions, and row level locking for MySQL
    InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
    tables


    "Morten" <usenet@kikobu. com> kirjoitti
    viestissä:cq4on e$i4u$1@news.cy bercity.dk...[color=blue]
    >
    > Hi. Creating the below to tables give errno 150, any idea why?
    >
    > CREATE TABLE categories (
    > id int(11) unsigned NOT NULL auto_increment,
    > name varchar(64) NOT NULL,
    > PRIMARY KEY (id),
    > UNIQUE KEY name (name)
    > ) TYPE=InnoDB;
    >
    > CREATE TABLE types (
    > id int(11) unsigned NOT NULL auto_increment,
    > category_id int(11) unsigned NOT NULL,
    > name varchar(64) NOT NULL,
    > PRIMARY KEY (id),
    > FOREIGN KEY (category_id) REFERENCES categories(id)
    > ) TYPE=InnoDB;
    >
    > Br,
    >
    > Morten
    >[/color]


    Comment

    • Morten

      #3
      Re: InnoDB foreign key constraint


      Thanks for the pointer. The issue was a lacking index on the
      types.category_ id column.



      Heikki Tuuri wrote:[color=blue]
      > Morten,
      >
      > please use
      >
      > SHOW INNODB STATUS;
      >
      > to print a detailed error explanation.
      >
      > Best regards,
      >
      > Heikki Tuuri
      > Innobase Oy
      > Foreign keys, transactions, and row level locking for MySQL
      > InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
      > tables
      > http://www.innodb.com/order.php
      >
      > "Morten" <usenet@kikobu. com> kirjoitti
      > viestissä:cq4on e$i4u$1@news.cy bercity.dk...
      >[color=green]
      >>Hi. Creating the below to tables give errno 150, any idea why?
      >>
      >>CREATE TABLE categories (
      >> id int(11) unsigned NOT NULL auto_increment,
      >> name varchar(64) NOT NULL,
      >> PRIMARY KEY (id),
      >> UNIQUE KEY name (name)
      >>) TYPE=InnoDB;
      >>
      >>CREATE TABLE types (
      >> id int(11) unsigned NOT NULL auto_increment,
      >> category_id int(11) unsigned NOT NULL,
      >> name varchar(64) NOT NULL,
      >> PRIMARY KEY (id),
      >> FOREIGN KEY (category_id) REFERENCES categories(id)
      >>) TYPE=InnoDB;
      >>
      >>Br,
      >>
      >>Morten
      >>[/color]
      >
      >
      >[/color]

      Comment

      Working...