mysql> CREATE TABLE user(
-> u_no INT NOT NULL,
-> u_pwd VARCHAR(16) NOT NULL,
-> u_name VARCHAR(8),
-> u_email VARCHAR(18),
-> u_state TINYTEXT,
-> h_id INT,
-> PRIMARY KEY(u_no),
-> FOREIGN KEY(h_id) REFERENCES head(h_id)
-> ) engine=innodb;
ERROR 1005 (HY000): Can't create table 'kdwb.user' (errno: 150)
-> u_no INT NOT NULL,
-> u_pwd VARCHAR(16) NOT NULL,
-> u_name VARCHAR(8),
-> u_email VARCHAR(18),
-> u_state TINYTEXT,
-> h_id INT,
-> PRIMARY KEY(u_no),
-> FOREIGN KEY(h_id) REFERENCES head(h_id)
-> ) engine=innodb;
ERROR 1005 (HY000): Can't create table 'kdwb.user' (errno: 150)
create table head
(
h_id int)
2.外键 建立索引
CREATE INDEX sdsd ON head(h_id)就Ok了
u_no INT NOT NULL,
u_pwd VARCHAR(16) NOT NULL,
u_name VARCHAR(8),
u_email VARCHAR(18),
u_state TINYTEXT,
h_id INT,
PRIMARY KEY(u_no),
FOREIGN KEY(h_id) REFERENCES head(h_id)
) engine=innodb;CREATE TABLE head(
h_id INT NOT NULL,
h_des VARCHAR(40) NOT NULL,
h_data MEDIUMBLOB NOT NULL,
u_no INT,
PRIMARY KEY(h_id),
FOREIGN KEY(u_no) REFERENCES user(u_no)
) engine=innodb;
这样的两个互为外键的表的如何创建啊,在mysql command中老是不过,就是ERROR 1005 (HY000): Can't create table 'kdwb.user' (errno: 150)...
CREATE TABLE user(
u_no INT NOT NULL,
u_pwd VARCHAR(16) NOT NULL,
u_name VARCHAR(8),
u_email VARCHAR(18),
u_state TINYTEXT,
h_id INT,
PRIMARY KEY(u_no)
) engine=innodb;
FOREIGN KEY(h_id) REFERENCES head(h_id)
CREATE TABLE head(
h_id INT NOT NULL,
h_des VARCHAR(40) NOT NULL,
h_data MEDIUMBLOB NOT NULL,
u_no INT,
PRIMARY KEY(h_id)
) engine=innodb;
FOREIGN KEY(u_no) REFERENCES user(u_no)
/*创建外键*/
alter table user add constraint user_head_FK foreign key(h_id) references head
(h_id) on delete cascade on update cascade;
alter table head add constraint head_user_FK foreign key(h_id) references user
(h_id) on delete cascade on update cascade;
CREATE TABLE user(
u_no INT NOT NULL,
u_pwd VARCHAR(16) NOT NULL,
u_name VARCHAR(8),
u_email VARCHAR(18),
u_state TINYTEXT,
h_id INT,
index(u_no, h_id),
PRIMARY KEY(u_no)
) engine=innodb;
CREATE TABLE head(
h_id INT NOT NULL,
h_des VARCHAR(40) NOT NULL,
h_data MEDIUMBLOB NOT NULL,
u_no INT,
index(u_no, h_id),
PRIMARY KEY(h_id)
) engine=innodb;
alter table user add constraint user_head_FK foreign key(h_id) references head
(h_id) on delete cascade on update cascade;
alter table head add constraint head_user_FK foreign key(u_no) references user
(u_no) on delete cascade on update cascade;插入数据的时候,执行executeUpdate这句会失败...
"insert into user(u_no,u_name,u_pwd,u_email,u_state,h_id)" +
"values(?,?,?,?,?,?)"
报错:
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`kdwb`.`user`, CONSTRAINT `user_head_FK` FOREIGN KEY (`h_id`) REFERENCES `head` (`h_id`) ON DELETE CASCADE ON UPDATE CASCADE)
原因???