Thanks for the info on the command line util. I am new and was unaware of it. But anyway, I have gone through the docs many more times, and I cannot figure out where my problem is. Of course the error is a foreign key problem, but I do not see where. I even am basically using a textbook example here that fails with the same error:
If someone see a problem, please let me know, I am creating a fresh database and just running those 30 or so lines below, and it errors with this: ERROR 1005: Can't create table '.\builder\menu_group_rel.frm' (errno: 150) mysql> Sincerely Scott CODE: GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'spurcell' WITH GRANT OPTION; 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', PRIMARY KEY (id), ) TYPE=InnoDB; CREATE TABLE MENU_GROUP_REL ( menu_type varchar(200), data_id int NOT NULL, display_name varchar(250), link varchar(250), FOREIGN KEY (data_id) REFERENCES MENU_GROUP(id) ON DELETE CASCADE ) TYPE=InnoDB; ERRORS -----Original Message----- From: V. M. Brasseur [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 11:06 AM To: mysql@lists.mysql.com Subject: Re: Cascade problem now error: Have you tried perror yet? [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > 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 [EMAIL PROTECTED] 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:[EMAIL PROTECTED] > Sent: Thursday, January 27, 2005 10:29 AM > To: Scott Purcell; mysql@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:[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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]