Does anybody know how to add foreign keys with "ON DELETE CASCADE" for table of type "InnoDB"
有没有人知道如何为“InnoDB”类型的表添加“ON DELETE CASCADE”的外键
1 个解决方案
#1
1
First, make sure that both tables are InnoDB. Even if create statement indicates InnoDB, if this engine is not installed, MySQL will use MyISAM instead. So check if they are actually InnoDB.
首先,确保两个表都是InnoDB。即使create语句指示InnoDB,如果未安装此引擎,MySQL也将使用MyISAM。所以检查它们是否实际上是InnoDB。
Next, make sure both columns are exactly the same type (for example if one of them is int(11) and the other one is int(11) unsigned, you can't create a foreign key). Make sure that the column you are referencing (id in the bussiness table in your case) have unique index (MySQL will allow to use any indexed column, but it's best to use unique index). If it's the primary key, it already has unique index.
接下来,确保两列完全相同(例如,如果其中一列是int(11)而另一列是int(11)unsigned,则无法创建外键)。确保您引用的列(在您的情况下在商务表中的id)具有唯一索引(MySQL将允许使用任何索引列,但最好使用唯一索引)。如果它是主键,则它已经具有唯一索引。
Next, run the statement, according to the documentation and as pointed by other users:
接下来,根据文档并按照其他用户的指示运行该语句:
ALTER TABLE yourtable ADD FOREIGN KEY xyzkey (field) REFERENCES foreigntable (foreignfield) ON DELETE CASCADE;
ALTER TABLE yourtable ADD FOREIGN KEY xyzkey(field)REFERENCES foreigntable(foreignfield)ON DELETE CASCADE;
If you can't add foreign key, you can create trigger instead
如果无法添加外键,则可以创建触发器
#1
1
First, make sure that both tables are InnoDB. Even if create statement indicates InnoDB, if this engine is not installed, MySQL will use MyISAM instead. So check if they are actually InnoDB.
首先,确保两个表都是InnoDB。即使create语句指示InnoDB,如果未安装此引擎,MySQL也将使用MyISAM。所以检查它们是否实际上是InnoDB。
Next, make sure both columns are exactly the same type (for example if one of them is int(11) and the other one is int(11) unsigned, you can't create a foreign key). Make sure that the column you are referencing (id in the bussiness table in your case) have unique index (MySQL will allow to use any indexed column, but it's best to use unique index). If it's the primary key, it already has unique index.
接下来,确保两列完全相同(例如,如果其中一列是int(11)而另一列是int(11)unsigned,则无法创建外键)。确保您引用的列(在您的情况下在商务表中的id)具有唯一索引(MySQL将允许使用任何索引列,但最好使用唯一索引)。如果它是主键,则它已经具有唯一索引。
Next, run the statement, according to the documentation and as pointed by other users:
接下来,根据文档并按照其他用户的指示运行该语句:
ALTER TABLE yourtable ADD FOREIGN KEY xyzkey (field) REFERENCES foreigntable (foreignfield) ON DELETE CASCADE;
ALTER TABLE yourtable ADD FOREIGN KEY xyzkey(field)REFERENCES foreigntable(foreignfield)ON DELETE CASCADE;
If you can't add foreign key, you can create trigger instead
如果无法添加外键,则可以创建触发器