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

Admin 1
Viewed 30864

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

ddl constraints foreign-keys mysql
7 Answers

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.

Bill_Karwin_208600

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.

Dan_C_263910

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.

Admin1

I am going to throw my hat in the ring here, although I don't actually know if my answer is accurate, so if you know the internal guts of database engineering, please correct me. But if I am right, I think this will help.

A Foreign Key and its associated Foreign Key Constraint are not the same thing, in the way a car engine and a crank-shaft are not the same thing. The engine converts gasoline explosions into straight line motion (the pistons), and the crankshaft converts that straight-line motion into turning motion, which then turns the wheels of the car. Together, the engine and the crank-shaft make the car go.

Likewise, a Foreign Key and a Foreign Key Constraint are not the same thing, but they work together to create the idea of a "Foreign Key Relationship".

DEFINITIONS:

"Foreign Key" is short for Foreign Key Index.

"Constraint" is short for Foreign Key Constraint.

The Index and the Constraint together make the "Foreign Key Relationship".

The Foreign Key Relationship is the requirement for a value in a child table to exist in its parent table, thus ensuring data integrity in a database.

Because Key means Index, we don't say "Foreign Key Index". We just say Foreign Key, but not saying "Index" is the cause of much confusion.

Creating a Foreign Key (a Foreign Key Index) creates a Binary Search Tree (also called a dictionary, because the tree is used to look up values). The Binary Search Tree (BST) then exists in computer memory and takes up physical disk space, but allows for O(log n) JOIN access time (almost instantly) from the child table to the parent table.

Creating a Foreign Key Constraint is creating a rule, which is a piece of code that gets called when you process (INSERT, SELECT, etc...) on the foreign key column. A constraint is essentially a database trigger. A constraint is like an email filter: a piece of code that gets called on a certain action, such as WHEN (new email) IF (From: crzy@xgfrnd.com) {SEND TO Trash Folder;}.

Thus, a Foreign Key "Constraint" would be a piece of code that gets called (a trigger, essentially), that looks like such: WHEN INSERT child_column IF (NOT IN parent_table) DO NOT ALLOW INSERT.

And then you have your Cascades and Updates and Delete rules (constraints), wth their various if / then conditions and operations, etc...

So, a "Foreign Key" is a BST dictionary mapping child table column values to parent table column values. The purpose of the Foreign Key is speed (NOT data integrity, since data integrity can be achieved, albeit slowly, without an index).

A Foreign Key Constraint is a rule: code that gets triggered on SQL statements, and that rule uses the BST as a dictionary for fast processing, to avoid traversing tables, which may eventually create Cartesian-like behavior. The purpose of the Foreign Key Constraint is data integrity.

I have never created a parent table where the referenced parent column was not itself a key in the parent table. So the question then is, is the Foreign Key Index (the BST dictionary) actually needed? The Constraint is definitely needed, to ensure data integrity, but Foreign Key Index (the BST dictionary) is actually not needed to fulfill the Foreign Key rule, thus, "Key" and "Index" have two different definitions. The "Index" is the BST tree, and the "Key" is the rule (the idea that the child value must exist in the parent table). In MySQL, however, the Foreign Key Index is needed, only because they programmed it that way, but they didn't have to. Having a BST tree is just faster, when the parent column is not itself indexed. I would never recommend making the referenced parent column not a key (index). But if someone did reference a non-indexed parent column using a Foreign Key Constraint without a BST, then the SQL operations would be progressively slow, and your application may eventually come to a crawl.

THE CONFUSION: Adjectives and verbs.

When we say "Foreign Key" colloquially, we are usually referring to the Foreign Key Relationship, not the Foreign Key Index. But the word Key means Index. So that's the root source of all the confusion. I.e. lack of reserved keyword definition standards. In a MySQL CREATE TABLE statement, FOREIGN KEY means the Index (the BST), and CONSTRAINT names the rule, therefore, the confusion is coming from the difference in the phrase "Foreign Key" when we speak, versus "Foreign Key" being defined in an actual SQL statement.

In computer code, "Foreign" is an adjective and "Key" is a noun, meaning the Index.

In colloquial speech, the phrase "Foreign Key" is an adjective, and the words "Index", "Constraint", and "relationship" are all nouns. When we speak to each other across office cubicles, "Foreign Key" means the "idea" of data integrity (i.e. the rule, not the index).

Unfortunately, programmers are always searching for short-hand ways of typing, which often causes confusion. Everything in computer science is a trade-off, and that includes coding style.

If the syntax for creating a MySQL table instead used the following reserved words, then the confusion would disappear: FOREIGN KEY CONSTRAINT fk1_rule_child_column FOREIGN KEY INDEX (fk_bst_child_to_parent_column) REFERENCES parent_table (parent_column)

Furthermore, since MySQL always creates both an Index and a Constraint, the MySQL creators could have completely hidden (abstraction) the dual element of the Foreign Key Relationship. Or, perhaps I should say, they could have bundled them together so the user doesn't have to think about the dual aspect, and instead just creates a "Foreign Key" my_foreign_key with the dual details hidden.

Nevertheless, MySQL is inexpensive, robust, and it's great. For the record, I have zero complaints, and I have only gratitude for the creators. For my part, they can do as they please.

Incidentally, as a style recommendation, you should ALWAYS name your parent and child columns the same, and your table names should ALWAYS contain their Foreign Key Relationships. So your tables

customers products attributes orders

should instead be named

customers products product_attributes customer_product_orders

That way, you and your successors know the foreign key relationships just by reading any table name. If that's too much typing for you, then

cust prod prod_attr cust_prod_ord

That being said, I am guessing. I don't actually know if my BST and Rule explanation is correct, but I think it's correct, and hopefully will clear up this confusing issue. But I would appreciate if you database-guts guys out there would either confirm, modify, supplement, or deny what I have written, and if I am mistaken, what is the real answer, so we can finally get this multi-generational mystery solved. If I am completely off, and this answer needs to be deleted, that is fine too.

Admin1
Related