Foreign key creation issue in MySQL

Question

I have followed this article: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html to create a foreign key between two tables. Every single attempt fails. Is there anything that I am missing?!

It's really frustrating and I never expected to encounter this issue at all!

Thanks.

Answer 1

What exactly is going wrong? We can't really help you without details...

mysql> CREATE TABLE parent (id INT NOT NULL,
    ->                      PRIMARY KEY (id)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE child (id INT, parent_id INT,
    ->                     INDEX par_ind (parent_id),
    ->                     FOREIGN KEY (parent_id) REFERENCES parent(id)
    ->                       ON DELETE CASCADE
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

Works fine for me.

Answer 2

Example:

use trading_research;
drop table if exists stock_history;
create table stock_history
(
        company_id integer(4) NOT NULL,
        price_date date NOT NULL,
        primary key(company_id, price_date),
        opening_price decimal(10,2) NULL,
        closing_price decimal(10,2) NULL,
        high_price decimal(10,2) NULL,
        low_price decimal(10,2) NULL,
        adjusted_closing_price decimal(10,2) NULL,
        volume decimal(20, 2) NULL,
        constraint foreign key (company_id) references stock_company (company_id) on delete cascade on update cascade
);

If you give details of what you're trying and/or the errors you get, I can help more.

Answer 3

One thing you have to remember, that if you're child table/column has values that don't exist in the parent table/column, you will always get an error.

Answer 4

You don't show what you tried or what the error was, so all of the answers are only guesswork.

Here's a checklist of things that must be true before foreign keys can work:

• InnoDB storage engine must be enabled.

  mysql> SHOW VARIABLES LIKE 'have_innodb';
  

• Both tables must use the InnoDB storage engine (MyISAM doesn't support referential integrity constraints).

  mysql> SHOW CREATE TABLE <parent_table>;
  mysql> SHOW CREATE TABLE <child_table>;
  

• The foreign key column and its referenced primary key column must be of exactly the same data type (must match signed vs. unsigned, int vs. bigint, string charset, etc.)

• You have to get the constraint declaration syntax right. :-)

• If you're adding a constraint to a populated table, all the existing values must satisfy the constraint (thanks to Jordan S. Jones' answer on this thread).

Answer 5

omg ty. i was having issues with the signed/unsigned. did not realize that mattered at all. serials get unsigned by default which i was using for PKs.