Cascade Not Working

Question

I have a couple tables that are weakly connected and I would like the delete to work across both of them when I delete from the main table.

Table 1:

CREATE TABLE Car(
cID INTEGER,
color VARCHAR(10),
primary key (cID)
);

CREATE TABLE Tags(
tID INTEGER,
expDate VARCHAR(10),
cID INTEGER,
primary key (tID, cID),
foreign key (cID) references Car(cID) on delete cascade
);

But when I delete a car from the Car table, it is not deleted from the Tags table. I've even tried adding the constraint after table creation but get the same result.

@vikramjain I tried again, same result, I tried the following: `DELETE FROM Car WHERE cID=1`, it worked successfully for the car table, but when I check the Tags table it is still there. — ZAX, Feb 06 '14 at 04:59

score 0 · Accepted Answer · answered Feb 06 '14 at 05:08

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


-- --------------------------------------------------------

--
-- Table structure for table `Car`
--

CREATE TABLE IF NOT EXISTS `Car` (
  `cID` int(11) NOT NULL AUTO_INCREMENT,
  `color` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`cID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `Car`
--

INSERT INTO `Car` (`cID`, `color`) VALUES
(2, 'B'),
(4, 'A');

-- --------------------------------------------------------

--
-- Table structure for table `Tags`
--

CREATE TABLE IF NOT EXISTS `Tags` (
  `tID` int(11) NOT NULL AUTO_INCREMENT,
  `expDate` varchar(10) DEFAULT NULL,
  `cID` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`tID`,`cID`),
  KEY `cID` (`cID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `Tags`
--

INSERT INTO `Tags` (`tID`, `expDate`, `cID`) VALUES
(3, 'AA', 2),
(4, 'BB', 2),
(5, '11', 4),
(6, '22', 4);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `Tags`
--
ALTER TABLE `Tags`
  ADD CONSTRAINT `Tags_ibfk_1` FOREIGN KEY (`cID`) REFERENCES `Car` (`cID`) ON DELETE CASCADE;

--Now you will delete a record ;

--DELETE FROM Car WHERE cID=4;

Cascade Not Working

1 Answers1