作者herman602 (奸商)
看板Database
标题[问题] SET NULL 问题
时间Sat Aug 4 15:15:48 2012
我使用的是MySQL InnoDB
目前有两个资料表
announcement
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| class | int(11) | YES | MUL | NULL | |
| subject | varchar(50) | NO | | NULL | |
| content | varchar(100) | NO | | NULL | |
| start_date | date | NO | | NULL | |
| end_date | date | NO | | NULL | |
| contact_id | int(11) | YES | MUL | NULL | |
+------------+--------------+------+-----+---------+----------------+
announcement_class
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| description | varchar(50) | NO | UNI | NULL | |
+-------------+-------------+------+-----+---------+----------------+
CONSTRAINT `announcement_ibfk_4` FOREIGN KEY (`
class`) REFERENCES
`announcement_class` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `announcement_ibfk_3` FOREIGN KEY (`contact_id`) REFERENCES
`staff` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
但是我这样删除nannouncement_class的时候
announcement里面若有record参照到被删除的nannouncement_class
理应上class要被设为NULL
但是我实际执行起来
那些有被参照到的record也被一并连锁删除了
请问是什麽问题呢?
谢谢!
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 219.68.58.62
1F：推 TeemingVoid:我遇过类似的的怪问题，请先执行: 08/04 21:56
2F：→ TeemingVoid:show create table announcement_class; 08/04 21:56
3F：→ TeemingVoid:看看有没有针对同一栏位重复设定了foreign key。 08/04 21:57
您好
出现了这些...
| announcement_class | CREATE TABLE `announcement_class` (
`id` int(11) NOT NULL auto_increment,
`description` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `description` (`description`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
※ 编辑: herman602 来自: 219.68.58.62 (08/05 01:22)
已解决
肇因於前端SQL statement的问题...
导致让我觉得被删除了@@!
谢谢TeemingVoid大大~~~~!!
※ 编辑: herman602 来自: 219.68.58.62 (08/05 02:02)
4F：推 TeemingVoid:两三回合水球就发现问题点, 原PO绝顶聪明反应真快! ^^ 08/05 02:09