都将其表的外键删除在进行删除或修改
 
E.G : ALTER TABLE <表名> DROP FOREIGN KEY <键名>;//先删除外键
 
DROP TABLE <表名>;//删除表
 
OR
 
ALTER TABLE <表名> ENGIER = <引擎>;
 
SHOW ENGIER;//你可以用此查找你的数据库所拥有的引擎
 

 

外键
 
定义了外键，就是给俩个表建立联系，删除的时候，数据库会会查看删除的数据会不会导致表结构的破坏，若会破坏，则不能删除。
 
解决办法，将外键屏蔽
 
设置如下：SET FOREIGN_KEY_CHECKS = 0;
 
具体例子： 
  
 这个是俩个表的结构，test3关联了test2的id主键。 
  
 当直接删除test2_id = 1 的数据，因为会test3中含有test2_id = 1的数据，删除时报异常。 
 先设置在删除，就可以了，删除后记得在设置回来。
 
那么，问题来了，在java程序里面怎么弄？jdbc好像不支持set这样的语句。

 

 
I have two tables with data.
 
I want to delete rows in both tables.
 
But I have foreign keys between them.
 
How can I do this?
 
departure
 
id departure_date
 
1 2016-09-29
 
2 2016-09-30
 
departure_time
 
id departure_id (fk)
 
1 1
 
2 2
 
the best thing would be to have a query that gets all rows to be deleted, and deletes rows in both tables at the same time.
 
Is there a way to do this without removing constraints/FK?
 
In this example, say I would like to delete all departures from date 2016-09-30
 
(delete departure: id 2 and departure_time: id: 2)
 
解决方案
 
Please try this, hope it will help.
 
DELETE FROM departure, departure_time
 
USING departure
 
INNER JOIN departure_time
 
WHERE departure_date = '2016-09-30'
 
AND departure_time.id = departure.id
 
Or
 
DELETE FROM departure, departure_time
 
USING departure
 
INNER JOIN departure_time
 
WHERE departure_date = '2016-09-30'
 
AND departure_time.departure_id = departure.id
 
Or you can use ON DELETE CASCADE that will do work automatically for you .

 
 
Cannot delete or update a parent row: a foreign key constraint fails，出现这个报错的原因是：想要删除的数据或表与其他数据或表拥有主外键关系，Mysql规定，为了维护表结构的稳定，禁止执行该操作，即外键约束失败
 
 
解决方法：
 在sql数据库里面时：
 
SET foreign_key_checks = 0;  // 先设置外键约束检查关闭
 
drop table table1; 
detele from table where ;
 // 删除表或数据
 
SET foreign_key_checks = 1; // 开启外键约束检查，以保持表结构完整性
 
在实现方法里面时
 
//    由id删除对象
    @Override
    public void deleteById(Integer id) {
        QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
        try {
            // 1.解除外键关联
            qr.update("SET foreign_key_checks = 0");
            // 2.执行删除语句
            qr.update("delete from tb_user where id = ?",id);
            // 3.重新关联外键
            qr.update("SET foreign_key_checks = 1");
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("由id删除对象失败",e);
        }
    }