Hello Scott, Make sure your tables are InnoDB type:
CREATE TABLE table_name ( table_def ...) ENGINE=InnoDB; If you have default MyISAM tables, it won't work because they don't support foreign keys. > -----Original Message----- > From: Scott Purcell [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 27, 2005 10:17 AM > To: mysql@lists.mysql.com > Subject: cascade on delete problem > > > Hello, > > I apologize for a possible simple question, but I am having > trouble with the below code. > > I have three simple tables. In short, "menu_group" has an id, > that is referenced in the "menu_group_rel". When a user > deletes an id from the "menu_group", I wanted the entry in > "menu_group_rel" (data_id) to also be deleted. They act as > one piece of data. > > I have gone through the docs, but when I delete a line from > the menu_group, it does NOT delete the entry from the menu_group_rel? > > Does anyone see anything wrong with the following? > > Thanks, > > > SQL: ####### > > DROP DATABASE builder2; > > GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED > BY 'spurcell' WITH GRANT OPTION; > > CREATE DATABASE builder2; > > use builder2; > > CREATE TABLE menu_sequence (id INT NOT NULL); > insert into menu_sequence VALUES (0); > > > CREATE TABLE MENU_GROUP ( > id int NOT NULL, > parent_id int NOT NULL DEFAULT '0', > sort int, > visible VARCHAR(1) NOT NULL DEFAULT 'T', > ); > ALTER TABLE MENU_GROUP ADD CONSTRAINT PK_MENU_GROUP PRIMARY KEY(id); > > UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); > INSERT INTO MENU_GROUP values (last_insert_id(), 0, 1, 'T'); > UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); > INSERT INTO MENU_GROUP values (last_insert_id(), 0, 2, 'T'); > UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); > INSERT INTO MENU_GROUP values (last_insert_id(), 0, 3, 'T'); > UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); > INSERT INTO MENU_GROUP values (last_insert_id(), 0, 4, 'T'); > UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); > INSERT INTO MENU_GROUP values (last_insert_id(), 1, 1, 'T'); > UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); > INSERT INTO MENU_GROUP values (last_insert_id(), 3, 1, 'T'); > UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); > INSERT INTO MENU_GROUP values (last_insert_id(), 4, 1, 'T'); > > > CREATE TABLE MENU_TYPE ( > id INT NOT NULL AUTO_INCREMENT, > attribute_type varchar(200) NOT NULL, > primary key (id) > ); > INSERT INTO MENU_TYPE (attribute_type) values ('jsp'), > ('menu'), ('cat_name'); > > CREATE TABLE MENU_GROUP_REL ( > menu_type varchar(200), > data_id int NOT NULL, > display_name varchar(250), > link varchar(250), > ); > > ALTER TABLE MENU_GROUP ADD CONSTRAINT PK_MENU_GROUP_REL > FOREIGN KEY(data_id) REFERENCES MENU_GROUP(id) ON DELETE CASCADE; > > INSERT INTO MENU_GROUP_REL values (1, 1, 'Company', > 'companyInfo.jsp'); > INSERT INTO MENU_GROUP_REL values (3, 2, 'Communities', > 'communities'); > INSERT INTO MENU_GROUP_REL values (1, 3, 'Contact', 'contact.jsp'); > INSERT INTO MENU_GROUP_REL values (1, 4, 'Inventory Homes', > 'invHomes.jsp'); > INSERT INTO MENU_GROUP_REL values (1, 5, 'About Me', 'about.jsp'); > INSERT INTO MENU_GROUP_REL values (1, 6, 'Customer Service', > 'custService.jsp'); > INSERT INTO MENU_GROUP_REL values (1, 7, 'Open Sunday', > 'opensunday.jsp'); > > > > > > > Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
