我的问题是为什么我能够从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/