From: Ady Wicaksono (adywicaksonoregionalinvestment.com)
Date: Thu Oct 27 2005 - 01:13:41 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

I see,

Thanks for information, yes correct this NO ACTION is confusing me.
BTW, congratulation for your new email oracle.com :D

Heikki Tuuri wrote:

> 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"
> <ady.wicaksonoinfokom.net>
> 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/mysql?unsub=myodbcfreebsd.csie.nctu.edu.tw
>>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]