Hi,
I am working on mysql 4.0.15, and I see FKs not quite working. First of all, I create respective tables with TYPE=InnoDB, but show create table <table_name> still shows TYPE=MyISAM. I don't get any error message while creating the tables. I test for FKs by updating the column of table A which references the column of table B with a value that doesn't even exist in table B. I don't see any error message or FK violation. Here are my SQL DDLs:
CREATE DATABASE IF NOT EXISTS `rma2` ;
USE `rma2`;
SET FOREIGN_KEY_CHECKS=1;
-- table B
DROP TABLE IF EXISTS `part_type`;
CREATE TABLE `part_type` (
`part_type_id` int(4) NOT NULL AUTO_INCREMENT,
`part_type` varchar(50) NOT NULL,
`part_type_desc` varchar(64),
PRIMARY KEY(`part_type_id`)
)
ENGINE=InnoDB
ROW_FORMAT=default;
-- table A
DROP TABLE IF EXISTS `part_num`;
CREATE TABLE `part_num` (
`part_num_id` int(4) NOT NULL AUTO_INCREMENT,
`part_num` varchar(50) NOT NULL,
`part_desc` varchar(128),
`part_type_id` int(4),
PRIMARY KEY(`part_num_id`),
INDEX `FKIndex1`(`part_type_id`),
FOREIGN KEY `is_of`(`part_type_id`)
REFERENCES `part_type`(`part_type_id`)
ON DELETE RESTRICT
ON UPDATE RESTRICT
)
TYPE=InnoDB
ROW_FORMAT=default;
mysql> show innodb status
-> ;
Query OK, 0 rows affected (0.00 sec)
Please guide. Thanks in advance.
RK.