Search code examples
mysqlforeign-keysinnodb

how to put foreign key in mysql

I want to know how to use a foreign key in a table,

I have a code here:

create table penerbit_buku(
  id_buku char(8), 
  foreign key(id_buku) references buku(id_buku),
  id_penerbit char(3), 
  foreign key(id_penerbit) references penerbit(id_penerbit)
)

Can I use this code instead:

create table penerbit_buku(
  id_buku char(8) references buku(id_buku),
  id_penerbit char(3) references penerbit(id_penerbit)
)

I have tried both and it succeed, is that correct?

Solution

  • No, MySQL parses but ignores the standard inline REFERENCES syntax.

    When you declare a foreign key along with an individual column definition, it accepts the syntax as legitimate SQL, but then does not store the foreign key constraint. There's no error reported, but it's as if you didn't write the foreign key syntax at all.

    You must declare foreign keys as table-level constraints (your first example above).

    This is a case where MySQL is missing a feature of standard SQL. The issue was reported back in 2004, but never fixed! https://bugs.mysql.com/bug.php?id=4919

    The reason for this issue is that historically, foreign key constraints were not supported by MySQL itself, but by the InnoDB storage engine, which was made by another company back then. They had to implement their own parser for CREATE TABLE and ALTER TABLE to support foreign keys, and they didn't feel like going the extra steps to support inline foreign key syntax, when table-level foreign key syntax would work.

    The architect of InnoDB posted this response:

    [6 Sep 2006 10:03] Heikki Tuuri

    This will be fixed in MySQL foreign keys, when they are available for all table types.

    The MySQL project is gradually working their way toward integrating foreign keys and similar features directly into the MySQL product. Perhaps in a few more years we'll see better support for standard FK syntax.