Monday, January 24, 2011

Foreign key not working in MySQL.

Why can I INSERT a value that's not in the foreign column?

I have created two tables on MYSQL using phpMyAdmin and assigned primary and foreign key constraint( as in below queries). I didn't got any error but still I was able to insert wrong values in it. This frustrated me quite a lot. I tried my queries several times on phpMyAdmin, I also tried those queries on SQL SERVER and they worked as they should do.

After googling sometime for this problem I got the reason and the culprit was 'storage engine MyISAM'. I altered it to InnoDB and it worked like a magic immediately.

Query used for changing storage engine:

ALTER TABLE actions ENGINE=InnoDB;
 
Create table queries I used:

CREATE TABLE IF NOT EXISTS `emp` (
  `id` int(11) NOT NULL,
  `emp_name` varchar(255) NOT NULL,
  `role` bit(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

CREATE TABLE IF NOT EXISTS `emp_expertise` (
  `emp_id` int(11) NOT NULL,
  `speciality` varchar(50) NOT NULL,
  KEY `emp_id` (`emp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `emp_expertise`
  ADD CONSTRAINT `emp_expertise_ibfk_1` FOREIGN KEY (`emp_id`) 
  REFERENCES `emp` (`id`) 
  ON DELETE CASCADE 
  ON UPDATE CASCADE;

INSERT INTO `emp` (`id`, `emp_name`, `role`) VALUES
(1, 'Ravi', b'1'),
(2, 'Vishwesh', b'1'),
(3, 'Ajay', b'0'),
(4, 'Ramesh', b'0');

Below query was previously inserting but after I fired above 
ALTER query it stopped.
 
INSERT INTO emp_expertise (emp_id, speciality)
VALUES (55,'XYZ')

About Me

My photo
Delhi, India
Fun, music, travel and nature loving, always smiling, computer addict!!