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

Can't answer for MySQL but FK's are constraints. Anything that forces your data into a certain condition is a constraint. There are several kinds of constraints, Unique, Primary Key, Check and Foreign Keys are all constraints. Maybe MySQL has others.

Sometimes words are allowed in commands but not required sheerly for readability like the FROM in the DELETE statement.

Answer 2

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.

Answer 3

In general (not necessary MySQL), foreign keys are constraints, but constraints are not always foreign keys. Think of primary key constraints, unique constraints etc.

Coming back to the specific question, you are correct, omitting CONSTRAINT [symbol] part will create a FK with an auto-generated name.

Answer 4

This is probably the most confusing topìc in MySQL.

Many people say that, for instance, the 'PRIMARY KEY', the 'FOREIGN KEY', and the 'UNIQUE' key are actually indexes! (MySQL official documentation is included here)

Many others, on the other hand, say that they're rather constraints (which does make sense, cause when you use them, you're really imposing restrictions on the affected columns).

If they really are indexes, then what's the point in using the constraint clausule in order to give it a name, since you're supposed to be able to use the name of that index when you created it?

Example:

... FOREIGN KEY index_name (col_name1, col_name2, ...)

If FOREIGN KEY is an index, then we should be able to use the index_name to handle it. However, we can't.

But if they're not indexes but actual contraints which do use indexes to work, then this does make sense.

In any case, we don't know. Actually, nobody seems to know.