Mysql创建外键[论坛摘录]

［From］ http://bbs.chinaunix.net/viewthread.php?tid=373766

Linux_cainiao:   我今天刚在学mysql, 但是就是搞不懂如何创建约束.
比如说我有两个表: Members和Email
CREATE TABLE Members (Member_ID INT NOT NULL
PRIMARY KEY AUTO_INCREMENT, First_Name VARCHAR(30)
NOT NULL, Last_Name VARCHAR(30) NOT NULL,
Bday DATE NOT NULL, Gender ENUM('M', 'F') DEFAULT 'F');

CREATE TABLE Email (Email VARCHAR(50), Member_ID INT NOT NULL, PRIMARY KEY (Email, Member_ID),
CONSTRAINT FOREIGN KEY (Member_ID) REFERENCES Members(Member_ID) ON DELETE RESTRICT ON UPDATE CASCADE);

创建后, 如果Members有两个记录, Member_ID分别是1和2,
如果我尝试在Email里,输入一条Member_ID是3的记录,操作成功. 如果我删除Members表里的记录, 尽管Email表里有记录相关联, 还是能删除成功. 所以约束好象一点都起不到作用. 肯定是哪里错了
请问该如果设置正确的约束呢?

-------------------------------------------------------
dragon76：

A simple example that relates parent and child tables through a single-column foreign key:

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

A more complex example in which a product_order table has foreign keys for two other tables. One foreign key references a two-column index in the product table. The other references a single-column index in the customer table:

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                      PRIMARY KEY(category, id)) TYPE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                      PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                      product_category INT NOT NULL,
                      product_id INT NOT NULL,
                      customer_id INT NOT NULL,
                      PRIMARY KEY(no),
                      INDEX (product_category, product_id),
                      FOREIGN KEY (product_category, product_id)
                        REFERENCES product(category, id)
                        ON UPDATE CASCADE ON DELETE RESTRICT,
                      INDEX (customer_id),
                      FOREIGN KEY (customer_id)
                        REFERENCES customer(id)) TYPE=INNODB;

Starting from MySQL 3.23.50, InnoDB allows you to add a new foreign key constraint to a table by using ALTER TABLE:

ALTER TABLE yourtablename
    ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
    [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

Remember to create the required indexes first. You can also add a self-referential foreign key constraint to a table using ALTER TABLE.

Starting from MySQL 4.0.13, InnoDB supports the use of ALTER TABLE to drop foreign keys:

ALTER TABLE yourtablename
    DROP FOREIGN KEY fk_symbol

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
Linux_cainiao： 非常感谢!    这下通了. 我比较了一下，问题出在TYPE=INNODB;和声明索引上.
是不是用外键的表创建的时候都要声明TYPE=INNODB;? 这是什么意思呢?
另, 是不是声明外键的时候一定要把外键用的列作为索引?

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
dragon76： 在MySQL中只有InnoDB类型的表才支持外键与存储过程所有要建立外键的字段必须建立索引