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?

Accepted Answer

`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.

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

Solution

Problem