After install mysql-max 3.23.52 and enable innodb setting in /etc/my.cnf file, recreate table, this time show create table indicated it's innodb type, but tried update on cascade still not working. Any idea?
mysql> create table samDB.license_info ( -> licenseID integer(5) auto_increment primary key, -> vendorName VARCHAR(30) NOT NULL, -> featureName VARCHAR(30) NOT NULL, -> comment VARCHAR(100)) TYPE=INNODB; mysql> create table samDB.license_data( -> licenseID integer(5), index licID_index (licenseID), foreign key licID_index (licenseID) REFERENCES samDB.license_info (licenseID) match full on delete cascade on update cascade, -> logDate DATE NOT NULL, -> totalLic integer(5) NOT NULL, -> requestLic integer(5) NOT NULL, -> issuedLic integer(5) NOT NULL, -> queuedLic integer(5) NOT NULL, -> deniedLic integer(5) NOT NULL) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql> show create table samDB.license_data; +--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | license_data | CREATE TABLE `license_data` ( `licenseID` int(5) default NULL, `logDate` date NOT NULL default '0000-00-00', `totalLic` int(5) NOT NULL default '0', `requestLic` int(5) NOT NULL default '0', `issuedLic` int(5) NOT NULL default '0', `queuedLic` int(5) NOT NULL default '0', `deniedLic` int(5) NOT NULL default '0', KEY `licID_index` (`licenseID`) ) TYPE=InnoDB | +--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table samDB.license_info; +--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | license_info | CREATE TABLE `license_info` ( `licenseID` int(5) NOT NULL auto_increment, `vendorName` varchar(30) NOT NULL default '', `featureName` varchar(30) NOT NULL default '', `comment` varchar(100) default NULL, PRIMARY KEY (`licenseID`) ) TYPE=InnoDB | +--------------+---------------------------------------- mysql> insert into samDB.license_info values (NULL, "cadence", "32500", ""); Query OK, 1 row affected (0.03 sec) mysql> insert into samDB.license_data values (LAST_INSERT_ID(), "2002-01-11",1,1,1,1,1); Query OK, 1 row affected (0.01 sec) mysql> update samDB.license_info set licenseID=2 where licenseID=1; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from samDB.license_data; +-----------+------------+----------+------------+-----------+-----------+-----------+ | licenseID | logDate | totalLic | requestLic | issuedLic | queuedLic | deniedLic | +-----------+------------+----------+------------+-----------+-----------+-----------+ | 1 | 2002-01-11 | 1 | 1 | 1 | 1 | 1 | +-----------+------------+----------+------------+-----------+-----------+-----------+ 1 row in set (0.02 sec) mysql> select * from samDB.license_info; +-----------+------------+-------------+---------+ | licenseID | vendorName | featureName | comment | +-----------+------------+-------------+---------+ | 2 | cadence | 32500 | | +-----------+------------+-------------+---------+ 1 row in set (0.00 sec) mysql> ¬ Benjamin Pflugmann wrote: > Hello. > > On Tue 2002-12-10 at 16:05:05 -0800, [EMAIL PROTECTED] wrote: > > I am just working on mySQL and create two tables, defined licenseID is > > the primary key in license_info, and foreign key in license_data. > > But I tested it didn't work the way as like in Oracle delete cascade or > > update cascade. > > Did I do something incorrect? > > > > mysql> create table samDB.license_info ( > > -> licenseID integer(5) auto_increment primary key, > > -> vendorName VARCHAR(30) NOT NULL, > > -> featureName VARCHAR(30) NOT NULL); > > Query OK, 0 rows affected (0.01 sec) > > If you did not change your default table type, this will create a > table with the MYISAM handler. You can find that out by using SHOW > CREATE TABLE samDB.license_info. ON CASCADE is only supported with the > InnoDB handler. You can force the InnoDB handler by adding TYPE=InnoDB > before the semicolon in the query above. > > HTH, > > Benjamin. > > -- > [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php