CODEWITHSUNDEEP
mysql
Dannyboy
Dannyboy

Reputation: 2052

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

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?

Upvotes: 7

Views: 3067

Answers (2)

Marcus Adams
Marcus Adams

Reputation: 53830

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.

Upvotes: 7

Rahul
Rahul

Reputation: 77876

From MySQL Ref document

index_name represents a foreign key ID. If given, this is ignored if an index for the foreign key is defined explicitly. Otherwise, if MySQL creates an index for the foreign key, it uses index_name for the index name.

Upvotes: 0

Related Questions