i have this:
 
DROP TABLE IF EXISTS `sf_guard_user`;
 
CREATE TABLE `sf_guard_user`
 
(
 
`id` INTEGER(11) NOT NULL AUTO_INCREMENT,
 
`username` VARCHAR(128) NOT NULL,
 
PRIMARY KEY (`id`),
 
UNIQUE KEY `sf_guard_user_U_1` (`username`)
 
)Type=InnoDB;
 
DROP TABLE IF EXISTS `shop_orders`;
 
CREATE TABLE `shop_orders`
 
(
 
`orders_id` INTEGER(11) NOT NULL AUTO_INCREMENT,
 
`sfgu_id` INTEGER(11) NOT NULL,
 
PRIMARY KEY (`orders_id`),
 
INDEX `shop_orders_FI_1` (`sfgu_id`),
 
CONSTRAINT `shop_orders_FK_1`
 
FOREIGN KEY (`sfgu_id`)
 
REFERENCES `sf_guard_user` (`id`)
 
ON UPDATE SET NULL
 
ON DELETE SET NULL,
 
)Type=InnoDB;
 
and I'm getting this error:
 
1005 - Can't create table
 
'prueba1.shop_orders' (errno: 150)
 
if i do not remove the lines ON UPDATE SET NULL and ON DELETE SET NULL.
 
Any idea why?
 
Regards
 
Javi
 
解决方案
 
I think that is because you declared the field NOT NULL

 
我有这个：
 
DROP TABLE IF EXISTS `sf_guard_user`;
 
CREATE TABLE `sf_guard_user`
 
(
 
`id` INTEGER(11) NOT NULL AUTO_INCREMENT,
 
`username` VARCHAR(128) NOT NULL,
 
PRIMARY KEY (`id`),
 
UNIQUE KEY `sf_guard_user_U_1` (`username`)
 
)Type=InnoDB;
 
DROP TABLE IF EXISTS `shop_orders`;
 
CREATE TABLE `shop_orders`
 
(
 
`orders_id` INTEGER(11) NOT NULL AUTO_INCREMENT,
 
`sfgu_id` INTEGER(11) NOT NULL,
 
PRIMARY KEY (`orders_id`),
 
INDEX `shop_orders_FI_1` (`sfgu_id`),
 
CONSTRAINT `shop_orders_FK_1`
 
FOREIGN KEY (`sfgu_id`)
 
REFERENCES `sf_guard_user` (`id`)
 
ON UPDATE SET NULL
 
ON DELETE SET NULL,
 
)Type=InnoDB;
 
我收到这个错误：
 
1005 – Can’t create table
 
‘prueba1.shop_orders’ (errno: 150)
 
如果我不删除ON UPDATE SET NULL和ON DELETE SET NULL行.
 
知道为什么吗？
 
问候
 
哈维

 
定义父表
 
CREATE TABLE provinces(
 
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 
-> pname VARCHAR(20) UNSIGNED
 
-> );
 
插入3个值
 
INSERT provinces(pname) VALUES('A');
 
INSERT provinces(pname) VALUES('B');
 
INSERT provinces(pname) VALUES('C');
 
定义子表
 
CREATE TABLE users2(
 
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 
username VARCHAR(10),
 
pid SMALLINT UNSIGNED,
 
FOREIGN KEY (pid) REFERENCES provinces (id) ON UPDATE SET NULL
 
);
 
插入值
 
INSERT users2(username,pid) VALUES('Tom',3);
 
INSERT users2(username,pid) VALUES('Jon',2);
 
INSERT users2(username,pid) VALUES('Kang',3);
 
删除父表第3列报错！！！！！！！
 
DELETE FROM provinces WHERE id=3;
 
错误如下：
 
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`users2`, CONSTRAINT `users2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ON UPDATE SET NULL)
 
求助：这是为什么呢，我子表的外键列pid并没有设置为NOT NULL，为什么这个外键不生效呢？删除不了父表来更新。

 

 
protected void GVImages_RowUpdating(object sender, GridViewUpdateEventArgs e)
 
{
 
string ID = ((Label)GVImages.Rows[e.RowIndex].FindControl("lblID")).Text;
 
string Name = ((TextBox)GVImages.Rows[e.RowIndex].FindControl("txtName")).Text;
 
FileUpload f = ((FileUpload)GVImages.Rows[e.RowIndex].FindControl("FileUpload2"));
 
byte[] imageBytes = null;
 
if (f.HasFile)
 
{
 
//variable to store the image content
 
imageBytes = new byte[f.PostedFile.ContentLength];
 
HttpPostedFile uploadImage = f.PostedFile;
 
//read the image stream from the post and store it in imageBytes
 
uploadImage.InputStream.Read(imageBytes, 0, (int)f.PostedFile.ContentLength);
 
}
 
con.Open();
 
MySqlCommand cmd = new MySqlCommand("Update images set Name=@Name,Content=@Content where ID="+ID, con);
 
cmd.Parameters.Add("@ID", MySqlDbType.Int16).Value = ID;
 
cmd.Parameters.Add("@Name", MySqlDbType.VarChar).Value= Name;
 
cmd.Parameters.Add("@Content", MySqlDbType.LongBlob).Value = imageBytes;
 
cmd.Connection = con;
 
cmd.ExecuteNonQuery();
 
GVImages.EditIndex = -1;
 
con.Close();
 
databind();
 
}
 
解决方案Try replaceing
 
MySqlCommand cmd = new MySqlCommand("Update images set Name=@Name,Content=@Content where ID=" +ID, con);
 
with
 
MySqlCommand cmd = new MySqlCommand(" Update images set Name=@Name,Content=@Content where ID=@ID", con);
 
Basically, ExecuteNonQuery() method should return you an integer, but you don''t catch it in any variable in your code ; you can watch its value by debugging, either.
 
The question you should concentrate on is : What do you want to get from an UPDATE command ?
 
Do you expect to get a resultset (i.e., one or more rows) ? You won''t.
 
If you want to get a resultset from a query, it must be a SELECT one.
 
So, please, we need to know what exactly you are expecting from your SQL request.
 
As ThePhantomMenace pointed out, you are concatenating on the ID in the string but then are adding a parameter named @ID. You need to change the SQL command text to be
 
"Update images set Name=@Name,Content=@Content where ID=@ID"