我的问题是为什么我能够从dept表中删除记录,dept表中有相应的记录。
例如,我可以删除dept表中deptno=10的记录,而emp表中的记录使用外键引用该记录。

mysql> select * from emp;
+-------+-------+---------+------+----------+------+------+--------+
| empno | ename | job     | mgr  | hiredate | sal  | comm | deptno |
+-------+-------+---------+------+----------+------+------+--------+
|  7698 | BLAKE | MANAGER | 7839 | NULL     | 2850 | NULL |     30 |
|  7782 | CLARK | MANAGER | 7839 | NULL     | 2450 | NULL |     10 |
+-------+-------+---------+------+----------+------+------+--------+
2 rows in set (0.00 sec)

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)


mysql> delete from dept where deptno = 10;
Query OK, 1 row affected (0.00 sec)

这是我的桌子结构
CREATE TABLE dept( deptno int, dname varchar(14), loc varchar(13), PRIMARY KEY (deptno));

CREATE TABLE emp( empno int, ename varchar(10), job varchar(9), mgr int, hiredate date, sal int, comm int, deptno int, PRIMARY KEY (empno),FOREIGN KEY (deptno) REFERENCES dept (deptno));

INSERT INTO dept VALUES(10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES(20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES(30,'SALES','CHICAGO');
INSERT INTO dept VALUES(40,'OPERATIONS','BOSTON');


INSERT INTO emp
VALUES( 7698,
        'BLAKE',
        'MANAGER',
        7839,
        STR_TO_DATE('1-5-1981','dd-mm-yyyy'),
        2850,
        NULL,
        30);


INSERT INTO emp
VALUES( 7782,
        'CLARK',
        'MANAGER',
        7839,
        STR_TO_DATE('9-6-1981','dd-mm-yyyy'),
        2450,
        NULL,
        10);

提前谢谢。
这是我的emp显示表结构
mysql> show create table dept;
+-------+---------------------------------------------------
| Table | Create Table
+-------+---------------------------------------------------
| dept  | CREATE TABLE `dept` (
  `deptno` int(11) NOT NULL DEFAULT '0',
  `dname` varchar(14) DEFAULT NULL,
  `loc` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`deptno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------

最佳答案

MyISAM存储引擎将接受外键声明,但不会强制执行约束;为了保证外键的完整性,可以切换到InnoDB引擎,在mysql配置文件中全局指定:

 [mysqld]
 ...
 default-storage-engine=InnoDB

或者在数据库中本地删除表,然后使用完整的mysql特定语法重新添加它们,如:
 CREATE TABLE `dept` (
  `deptno` int(11) NOT NULL DEFAULT '0',
  `dname` varchar(14) DEFAULT NULL,
  `loc` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`deptno`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

注意:InnoDB是Mysql 5.5之后的默认存储引擎,要确定InnoDB在您的设置中是否可用,请执行查询:my.cnf

关于mysql - 为什么mysql允许删除外键引用的记录?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/26381538/

10-14 18:00
查看更多