8 messages in com.mysql.lists.mysqlRe: Cascade problem now error:
FromSent OnAttachments
Scott Purcell27 Jan 2005 07:16 
Gleb Paharenko27 Jan 2005 08:13 
Artem Koltsov27 Jan 2005 08:28 
V. M. Brasseur27 Jan 2005 09:05 
Scott Purcell27 Jan 2005 09:09 
Scott Purcell27 Jan 2005 09:58 
Scott Purcell27 Jan 2005 10:05 
Artem Koltsov27 Jan 2005 11:25 
Subject:Re: Cascade problem now error:
From:V. M. Brasseur (bras@iii.com)
Date:01/27/2005 09:05:12 AM
List:com.mysql.lists.mysql

Have you tried perror yet?

brasseur@ping (ping-300) 120 > perror 150 MySQL error: 150 = Foreign key constraint is incorrectly formed

Cheers,

--V

Scott Purcell wrote:

Thanks, I updated my script and all looks good. But now I get an error when tryng to
issue this command.

Any ideas?

Thanks, Scott

mysql> ALTER TABLE MENU_GROUP_REL ADD CONSTRAINT PK_MENU_GROUP_REL FOREIGN KEY(d ata_id) REFERENCES MENU_GROUP(id) ON DELETE CASCADE; ERROR 1005: Can't create table '.\builder\#sql-260_d.frm' (errno: 150) mysql>

-> mysql \s

-------------- mysql Ver 12.21 Distrib 4.0.15, for Win95/Win98 (i32)

Connection id: 13 Current database: builder Current user: root@localhost SSL: Not in use Server version: 4.0.15-max-debug Protocol version: 10 Connection: localhost via TCP/IP Client characterset: latin1 Server characterset: latin1 TCP port: 3306 Uptime: 10 days 1 min 3 sec

Threads: 1 Questions: 364 Slow queries: 0 Opens: 115 Flush tables: 1 Open t ables: 0 Queries per second avg: 0.000 Memory in use: 8324K Max memory used: 8631K

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

UPDATED SCRIPT BELOW: DROP DATABASE builder;

GRANT ALL PRIVILEGES ON *.* TO spurcell@localhost IDENTIFIED BY 'spurcell' WITH
GRANT OPTION;

// sequence stuff // mysql> CREATE TABLE sequence (id INT NOT NULL); //mysql> INSERT INTO sequence VALUES (0);

//Use the table to generate sequence numbers like this: //mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1); //mysql> SELECT LAST_INSERT_ID();

CREATE DATABASE builder;

use builder;

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', ) type=INNODB; 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), ) type=INNODB;

ALTER TABLE MENU_GROUP_REL 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');

-----Original Message----- From: Artem Koltsov [mailto:Arte@wfinet.com] Sent: Thursday, January 27, 2005 10:29 AM To: Scott Purcell; mys@lists.mysql.com Subject: RE: cascade on delete problem

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:spur@vertisinc.com] Sent: Thursday, January 27, 2005 10:17 AM To: mys@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 spurcell@localhost 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.