Hi, You have an error in the statement. See below. If you remove the comma after primary key def everything should work.
[skip] > > 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), *************************^ this genereate error ********************* > ) 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] 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]
