Jasir Jasir - 5 months ago 9
SQL Question

What is the use of CONSTRAINT keyword when adding a Foreign key?

I Can't figure out what is the difference between these two.

CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES ..


and

FOREIGN KEY (P_Id) REFERENCES ..


Is it just naming, or something else?

Answer

As MySQL manual on foreign keys indicates, the CONSTRAINT symbol_name part of the constraint syntax is optional:

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

The difference is in the naming of the foreign key. As the above linked document describes:

Otherwise, MySQL implicitly creates a foreign key index that is named according to the following rules:

• If defined, the CONSTRAINT symbol value is used. Otherwise, the FOREIGN KEY index_name value is used.

• If neither a CONSTRAINT symbol or FOREIGN KEY index_name is defined, the foreign key index name is generated using the name of the referencing foreign key column.