Ady,
NO ACTION actually means the same as RESTRICT. That is, the foreign key
constraint is still enforced. The name 'NO ACTION' is from the SQL standard.
I agree that the name is confusing, but I cannot help it, because it is in
the standard.
Regards,
Heikki
Oracle/Innobase
----- Original Message -----
From: "Ady Wicaksono" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Wednesday, October 26, 2005 9:25 AM
Subject: weird innodb foreign key feature
I'm using MySQL Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686) on
RedHat Linux 9
and found this weird thing
I create first table
CREATE TABLE `t_keycode` (
`keycode_id` int(11) NOT NULL auto_increment,
`keycode_val` varchar(255) NOT NULL default '',
`keycode_desc` varchar(255) NOT NULL default '',
`keycode_isactive` enum('Y','N') NOT NULL default 'N',
`keycode_tarif` int(11) NOT NULL default '2000',
PRIMARY KEY (`keycode_id`),
UNIQUE KEY `keycode_val` (`keycode_val`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
and my second table
CREATE TABLE `t_push_member_unsub` (
`push_member_id` int(11) NOT NULL auto_increment,
`push_keycode` varchar(15) NOT NULL default '',
`push_msisdn` varchar(16) NOT NULL default '',
`push_subscribe_at` datetime NOT NULL default '0000-00-00 00:00:00',
`push_unsubscribe_at` timestamp NOT NULL default CURRENT_TIMESTAMP,
`push_pending` enum('TRUE','FALSE') default 'FALSE',
`push_operator` varchar(30) NOT NULL default '',
`push_unsubscribe_reason` text NOT NULL,
PRIMARY KEY (`push_member_id`),
KEY `t_push_member_unsub_ibfk_1` (`push_keycode`),
CONSTRAINT `t_push_member_unsub_ibfk_1` FOREIGN KEY (`push_keycode`)
REFERENCES `t_keycode` (`keycode_val`) ON DELETE NO ACTION ON UPDATE
CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
when i delete rows on t_keycode EVEN if there's foreign key
(push_keycode) that refer to this row i hope
in t_push_member_unsub will do nothing, let it happened.
DELETE from t_keycode WHERE keycode_val='TEST'
Foreign key constraint fails for table `t_push_member_unsub`:
,
CONSTRAINT `t_push_member_unsub_ibfk_1` FOREIGN KEY (`push_keycode`)
REFERENCES `t_keycode` (`keycode_val`) ON DELETE NO ACTION ON UPDATE
CASCADE
Trying to delete in parent table, in index `keycode_val` tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 54455354; asc TEST;; 1: len 4; hex 80000090; asc ;;
But in child table `t_push_member_unsub`, in index
`t_push_member_unsub_ibfk_1`, there is a record:
PHYSICAL RECORD: n_fields 2; 1-byte offs TRUE; info bits 0
0: len 4; hex 54455354; asc TEST;; 1: len 4; hex 8000cb50; asc P;;
Any information?
Thx
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
