on delete cascade sql不工作

20jt8wwn  于 2021-06-20  发布在  Mysql
关注(0)|答案(0)|浏览(170)

我已经创建了两个表 users 以及 login_flag 下图:

CREATE TABLE IF NOT EXISTS users (
    user_id int NOT NULL AUTO_INCREMENT,
    email varchar(200) NOT NULL,
    first_name varchar(100) NOT NULL,
    last_name varchar(100) NOT NULL,
    password varchar(200) NOT NULL,
    priv varchar(20) NOT NULL,
    status varchar(20) NOT NULL DEFAULT 'unlocked',
    ctime TIMESTAMP NOT NULL default CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id)
);

CREATE TABLE IF NOT EXISTS login_flag (
    flag_id int NOT NULL AUTO_INCREMENT,
    user_id int NOT NULL,
    flag int NOT NULL DEFAULT 0,
    PRIMARY KEY (flag_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

mysql> select * from users;

| user_id | email          | first_name | last_name | password                                                     | priv  | status   | ctime               |

|       1 | admin@test.com | admin      | admin     | $2b$12$oMMYOM45LxzsibodyPPBx.JUocceiBoU0Ui2TrZaKEmXnhO0jkCTW | admin | unlocked | 2018-08-23 11:02:40 |

mysql> select * from login_flag;
+---------+---------+------+
| flag_id | user_id | flag |
+---------+---------+------+
|       1 |       1 |    0 |
+---------+---------+------+
1 row in set (0.00 sec)

我把记录从文件中删除了 users 表格:

mysql> delete from users where user_id='1';
Query OK, 1 row affected (0.16 sec)

这也应该从表中删除条目 login_flag 但事情不是这样的。

mysql> select * from login_flag;
+---------+---------+------+
| flag_id | user_id | flag |
+---------+---------+------+
|       1 |       1 |    0 |
+---------+---------+------+
1 row in set (0.00 sec)

谁能给我指出正确的方向或者让我知道我做错了什么。
我还检查了 foreign_key_checks 它的价值是 ON ```
mysql> SHOW VARIABLES LIKE 'foreign_key_checks';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| foreign_key_checks | ON |
+--------------------+-------+
1 row in set (0.00 sec)

我可以看到引擎是innodb

mysql> SHOW TABLE STATUS where Name = 'users'
-> ;
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| users | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 10485760 | 2 | 2018-08-23 11:58:47 | NULL | NULL | latin1_swedish_ci | NULL | | |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.02 sec)
`show create table login_flag;`
mysql> show create table login_flag;
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| login_flag | CREATE TABLE login_flag (
flag_id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
flag int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (flag_id),
KEY user_id (user_id),
CONSTRAINT login_flag_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题