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

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

Solution 2

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.

Solution 3

As of now, our CREATE TABLE DDLs are of this format - notice the UNIQUE KEY and FOREIGN KEY definition syntax we have used.

CREATE TABLE my_dbschema.my_table (
    id INT unsigned auto_increment PRIMARY KEY,
    account_nbr INT NOT NULL,
    account_name VARCHAR(50) NOT NULL,
    active_flg CHAR(1) NOT NULL DEFAULT 'Y',
    vendor_nbr INT NOT NULL,
    create_ts TIMESTAMP NOT NULL DEFAULT current_timestamp,
    create_usr_id VARCHAR(10) NOT NULL DEFAULT 'DFLTUSR',
    last_upd_ts TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp,
    last_upd_usr_id VARCHAR(10) NOT NULL DEFAULT 'DFLTUSR',
    UNIQUE KEY uk1_my_table(account_nbr, account_name),
    FOREIGN KEY fk1_my_table(vendor_nbr) REFERENCES vendor(vendor_nbr)
    );

In this format, MySQL is creating INDEX-es with the names uk1_my_table and fk1_my_table automatically; but the FK object name is something different - my_table_ibfk_1 (ie. tablename_ibfk_N – system defined) . So ALTER TABLE my_table DROP FOREIGN KEY fk1_my_table won’t work (and hence frustrating and raising alarms), as there’s no FK db object by that name.

Here’s an alternative DDL format wrt the constarints (Ref : https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html) :-

CREATE TABLE my_dbschema.my_table (
    id INT unsigned auto_increment PRIMARY KEY,
    account_nbr INT NOT NULL,
    account_name VARCHAR(50) NOT NULL,
    active_flg CHAR(1) NOT NULL DEFAULT 'Y',
    vendor_nbr INT NOT NULL,
    create_ts TIMESTAMP NOT NULL DEFAULT current_timestamp,
    create_usr_id VARCHAR(10) NOT NULL DEFAULT 'DFLTUSR',
    last_upd_ts TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp,
    last_upd_usr_id VARCHAR(10) NOT NULL DEFAULT 'DFLTUSR',
    CONSTRAINT uk1_my_table UNIQUE KEY (account_nbr, account_name),
    CONSTRAINT fk1_my_table FOREIGN KEY (vendor_nbr) REFERENCES vendor(vendor_nbr)
    );

In this format, MySQL is still creating INDEX-es with the names uk1_my_table and fk1_my_table automatically, but the FK object name is not something different – it’s fk1_my_table as mentioned in the DDL. So ALTER TABLE my_table DROP FOREIGN KEY fk1_my_table works, but leaves behind the namesake INDEX.

And, note that ALTER TABLE my_table DROP INDEX fk1_my_table won’t work initially (when the FK is not yet dropped), with an error message that it is being used in a FK! If the DROP FK command has been executed successfully, only then the DROP INDEX works.

Hope this explains and helps resolve the confusion.

Solution 4

If I'm not wrong, the constraints need indexes, so when you create, for example, a foreign key constraint MySQL automatically creates an index too.

View more solutions

Comments

[hpaknia]

A great answer!