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')