CODEWITHSUNDEEP
mysqlforeign-keysentity-relationship
Arash
Arash

Reputation: 4260

Foreign key creation issue in MySQL

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.

Upvotes: 0

Views: 425

Answers (4)

Bill Karwin
Bill Karwin

Reputation: 562721

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 column(s) in the referenced table must be the leftmost column(s) of a key. Typically this is the PRIMARY KEY, but InnoDB actually allows them to be part of any key.

  • 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.). Only the string length is allowed to be different.

  • 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).

Upvotes: 3

Jordan S. Jones
Jordan S. Jones

Reputation: 13903

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.

Upvotes: 1

C. Ross
C. Ross

Reputation: 31858

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.

Upvotes: 0

Paolo Bergantino
Paolo Bergantino

Reputation: 488644

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.

Upvotes: 1

Related Questions