Click here to Skip to main content
15,884,298 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have two tables music and music_details with foregn key relationship. When i delete records from parent table automatically delete the records from child table. So I applied on delete cascade on update cascade for child table. it works perfectly for 1st time.but when i open the database next day its set to on delete restrict. whats is the problem in my tables.

follwing are my tables..


SQL
Music:
---------
music | CREATE TABLE `music` (
  `music_id` int(11) NOT NULL AUTO_INCREMENT,
  `movie_name` varchar(100) DEFAULT NULL,
  `movie_image` varchar(100) DEFAULT NULL,
  `music_director` varchar(150) DEFAULT NULL,
  `cast` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`music_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Music_details
-------------
 music_details | CREATE TABLE `music_details` (
  `music_id` int(11) DEFAULT NULL,
  `songtitle` varchar(150) DEFAULT NULL,
  `duration` time DEFAULT NULL,
  `artist` varchar(100) DEFAULT NULL,<pre lang="SQL">

`lyricist` varchar(100) DEFAULT NULL,
`status` varchar(50) DEFAULT NULL,
KEY `music_id` (`music_id`),
CONSTRAINT `music_details_ibfk_1` FOREIGN KEY (`music_id`) REFERENCES `music`
(`music_id`) on delete cascade on update cascade
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

But when i open the database in next day the tables structure changed as

SQL
music_details | CREATE TABLE `music_details` (
  `music_id` int(11) DEFAULT NULL,
  `songtitle` varchar(150) DEFAULT NULL,
  `duration` time DEFAULT NULL,
  `artist` varchar(100) DEFAULT NULL,<pre lang="SQL">

`lyricist` varchar(100) DEFAULT NULL,
`status` varchar(50) DEFAULT NULL,
KEY `music_id` (`music_id`),
CONSTRAINT `music_details_ibfk_1` FOREIGN KEY (`music_id`) REFERENCES `music`
(`music_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |



Whats problem in my table structure.
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900