Problem somewhat solved:
I am somewhat thrown by what I stumbled across, but it has to do with the "_"
underscore character. If I run the same below script without the "_", it works
fine. But as soon as I put in the underscore "_" it fails with the error.
This sounds pretty insane? They are definitely different names?
Does anyone have input into this?
Thanks,
CREATE TABLE menu_sequence (id INT NOT NULL);
insert into menu_sequence VALUES (0);
CREATE TABLE MENU_GROUP(id INT NOT NULL,
PRIMARY KEY (id)
) TYPE=INNODB;
CREATE TABLE MENU_GROUPREL(id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
) TYPE=INNODB;
-----Original Message-----
From: Scott Purcell [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 27, 2005 11:59 AM
To: V. M. Brasseur; mysql@lists.mysql.com
Subject: RE: Cascade problem now error:
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
