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

Reply via email to