| Author |
Topic |
|
nathanvale
Starting Member
1 Post |
 Posted - 2005-06-01 : 00:28:43
|
| Hi, I have two tables. Course and course name (for a restaurant menu) A courseNameID (cnmId) can belong to a course. For example cnmId 3 (Entree) can belong to many courses. I am learning how to create casading deletes with the use of foriegn keys. Could some please suggest to me how I can set a FK constraint so that where a course is deleted from the course table the courseName is also deleted from the coursename table IF ONLY the coursename does not exist more than once in the course table. Tables below Regards, Nathan CREATE TABLE `coursename` ( `cnmId` int(11) unsigned NOT NULL auto_increment, `cnmName` varchar(64) NOT NULL default '', PRIMARY KEY (`cnmId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `coursename` VALUES (1,'Desert'); INSERT INTO `coursename` VALUES (2,'Mains'); INSERT INTO `coursename` VALUES (3,'Entree'); INSERT INTO `coursename` VALUES (4,'Starters'); INSERT INTO `coursename` VALUES (8,'Vegetarian Dishes'); INSERT INTO `coursename` VALUES (9,'Soups'); INSERT INTO `coursename` VALUES (10,'FuTu Special Dishes'); INSERT INTO `coursename` VALUES (11,'Combination Dishes'); INSERT INTO `coursename` VALUES (12,'Stir Fried Noodle'); INSERT INTO `coursename` VALUES (13,'Noodle Soup'); INSERT INTO `coursename` VALUES (14,'Rice'); INSERT INTO `coursename` VALUES (15,'Test'); CREATE TABLE `course` ( `crsId` int(11) unsigned NOT NULL auto_increment, `cnmId` int(11) unsigned NOT NULL default '0', `crsRank` tinyint(3) unsigned NOT NULL default '0', `crsDescription` varchar(255) default NULL, PRIMARY KEY (`crsId`), KEY `crsId` (`crsId`), KEY `cnmId` (`cnmId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `course` VALUES (1,3,1,'Bloody tasty snacks!'); INSERT INTO `course` VALUES (2,2,2,NULL); INSERT INTO `course` VALUES (3,1,3,NULL); INSERT INTO `course` VALUES (41,8,0,NULL); INSERT INTO `course` VALUES (42,9,0,NULL); INSERT INTO `course` VALUES (43,3,0,NULL); INSERT INTO `course` VALUES (44,10,0,NULL); INSERT INTO `course` VALUES (45,11,0,NULL); INSERT INTO `course` VALUES (46,12,0,NULL); INSERT INTO `course` VALUES (50,13,0,NULL); INSERT INTO `course` VALUES (51,14,0,NULL); INSERT INTO `course` VALUES (52,15,0,NULL); INSERT INTO `course` VALUES (53,16,0,NULL); INSERT INTO `course` VALUES (54,3,0,'Test'); |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-06-01 : 00:52:05
|
| To my knowledge, no current SQL DBMS implements cardinality based cascading RI (Say that fast 4 times!!). ..at least you are using the InnoDB storage engine.. a small mercy...And by the way, this site specialises in SQL Server...DavidMA front-end is something that tries to violate a back-end. |
 |
|
|
Ex
Posting Yak Master
166 Posts |
Posted - 2005-06-01 : 00:54:07
|
| you could always create a little trigger to do it for you------------------------------------------------------------------Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. Rich Cook |
|
|
|
|
|
|
Foreign Keys- Casading Delete