MySQL terminology "constraints" vs "foreign keys" difference?

Question

I'm looking at the MySQL docs here and trying to sort out the distinction between FOREIGN KEYs and CONSTRAINTs. I thought an FK was a constraint, but the docs seem to talk about them like they're separate things.

The syntax for creating an FK is (in part)...

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)

So the "CONSTRAINT" clause is optional. Why would you include it or not include it? If you leave it out does MySQL create a foreign key but not a constraint? Or is it more like a "CONSTRAINT" is nothing more than a name for you FK, so if you don't specify it you get an anonymous FK?

Any clarification would be greatly appreciated.

Thanks,

Ethan

Answer 1

Yes, a foreign key is a type of constraint. MySQL has uneven support for constraints:

• PRIMARY KEY: yes as table constraint and column constraint.
• FOREIGN KEY: yes as table constraint, but only with InnoDB and BDB storage engines; otherwise parsed but ignored.
• CHECK: parsed but ignored in all storage engines.
• UNIQUE: yes as table constraint and column constraint.
• NOT NULL: yes as column constraint.
• DEFERRABLE and other constraint attributes: no support.

The CONSTRAINT clause allows you to name the constraint explicitly, either to make metadata more readable or else to use the name when you want to drop the constraint. The SQL standard requires that the CONSTRAINT clause is optional. If you leave it out, the RDBMS creates a name automatically, and the name is up to the implementation.