In MySQL, how do I create a column with a foreign key?

Question

A column with a foreign key to another table's column. How do I do that?

Answer 1

This is not possbile with table engine MyISAM but with InnoDB, e.g.:

CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
                    INDEX par_ind (parent_id),
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
                      ON DELETE CASCADE
) ENGINE=INNODB;

Otherwise (with MyISAM) you just have to check the columns manually. It is still (at least logical) a foreign key but without the constraint.

In the end a foreign key is just a reference to another table. The table does not necessarily have to know about this, but it makes life easier.

Answer 2

First, make sure you are using InnoDB (or another engine that supports foreign keys; MyISAM does not). Then, use the appropriate DDL statements.