Re: Cascade problem now error:

Thu, 27 Jan 2005 09:15:39 -0800 

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]

Reply via email to