Referential Integrity not working

[Vikram Malhotra]

Hello all,

I was just trying to implement a simple foreign key contraint, but it doesn't work.

-- -----------------------------------------------------
-- Table `tbl_customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS tbl_customer (
cust_id MEDIUMINT NOT NULL AUTO_INCREMENT ,
cust_lastname VARCHAR(45) NULL ,
PRIMARY KEY (cust_id) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `tbl_order`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS tbl_order (
ordr_id MEDIUMINT NOT NULL AUTO_INCREMENT ,
ordr_name VARCHAR2(100) NOT NULL ,
cust_id MEDIUMINT NOT NULL ,
PRIMARY KEY (ordr_id) ,
INDEX (cust_id) ,
FOREIGN KEY (cust_id )
REFERENCES mydb.tbl_customer ( cust_id ) )
ENGINE = InnoDB;

Whenever I insert a record in table for orders(FK), without entering a record in customer's table(PK), it does not hold the foreign key constraint. What could be going wrong?

Thanks for any help
Vikram



Edited 2 time(s). Last edit at 10/30/2008 08:40PM by Vikram Malhotra.