Constraint symbol vs foreign key index name -> what's the difference?

Question

In mysql foreign keys are defined like this:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

Why do we need CONSTRAINT and symbol? It seems like there is index_name anyway - so I totally don't get the reason for CONSTRAINT keyword. Can someone clarify what it's for?

Answer 1

CONSTRAINT is the key word that tells MySQL that you wish to add a constraint. [symbol] is an optional name for the constraint. You can name it whatever you like. If you omit the name, MySQL will generate a name on its own, internally.

It's good to use the fk prefix like fk_something.

You'll need that symbol name when dropping the constraint:

ALTER TABLE tbl_name DROP FOREIGN KEY fk_something;

You can get the symbol name using SHOW CREATE TABLE:

SHOW CREATE TABLE tbl_name;

You can't use an index name to refer to a constraint. Any index that may be added when you issue the CONSTRAINT clause may be automatically removed later (e.g. if an index is explicitly added that will serve in its place).

All of this is answered in more detail in the MySQL Documentation.