官方语法:
[CONSTRAINT [外键的作用:如有table A和table B,table A中有username,password, table B中有username,friendname,当把table B中的username设置为外键,关联table A中的主键username,当删除或更新table A中的username时,table B也会更新或删除,并且table B中不能插入table A中没有的usernamesymbol
]] FOREIGN KEY [index_name
] (index_col_name
, ...) REFERENCEStbl_name
(index_col_name
,...) [ON DELETEreference_option
] [ON UPDATEreference_option
]reference_option
: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
创建table的官方例子:table必须为innodb类型,也要创建索引
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;同一个table的两个外键对应一个table的主键的创建:
create table if not exists t_user( username varchar(64) primary key, password varchar(64))ENGINE=InnoDB DEFAULT CHARSET=latin1; create table if not exists t_friend( username varchar(64), friend varchar(64), primary key(username, friend), INDEX (username,friend), CONSTRAINT fk_PerOrders FOREIGN KEY (username) REFERENCES t_user(username) ON DELETE CASCADE, CONSTRAINT fk_PerOrders1 FOREIGN KEY (friend) REFERENCES t_user(username) ON DELETE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=latin1;
上面的fk_PerOrders可以任意定义的
删除table t_user时,要按照如下:
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE t_user;
DROP TABLE t_user;
SET FOREIGN_KEY_CHECKS=1;