Hey ng
Databaseengine: innodb
In short terms:
When I made two foreign keys in one Table using innodb I cannot drop
index on that table.
ALTER TABLE TestMaster DROP INDEX F_TestOneID
I'll get the failure:

I don't know if the problem is lying in my creation of tables, therefore
I will explain from scratch:

TestOne
-----------
CREATE TABLE `TestOne` (
`OneID` char(4) NOT NULL,
`onefield1` varchar(50) NOT NULL,
PRIMARY KEY (`OneID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

INSERT INTO TestOne VALUES ('2100', 'test1s felt');

TestTwo
-------------
CREATE TABLE TestTwo
(
TwoID INT NOT NULL AUTO_INCREMENT
, twofield1 varchar(50) NOT NULL

, PRIMARY KEY (TwoID)
) ENGINE=INNODB;

INSERT INTO TestTwo (twofield1) VALUES ('2s felt');

TestMaster
-------------
CREATE TABLE TestMaster
(
TestMasterID INT NOT NULL AUTO_INCREMENT
, field1 varchar(50) NOT NULL
, F_TestOneID CHAR(4) NOT NULL
, F_TwoID INT NOT NULL

, INDEX (F_TestOneID)
, FOREIGN KEY (F_TestOneID) REFERENCES TestOne(OneID)

, INDEX (F_TwoID)
, FOREIGN KEY (F_TwoID) REFERENCES TestTwo(TwoID)
, PRIMARY KEY (TestMasterID)
) ENGINE=INNODB;

INSERT INTO TestMaster(field1, F_TestOneID, F_TwoID) VALUES ('mesterens
felt1', '2100', 1);

The database machine is making something extra (CONSTRAINT), when I use
the command SHOW CREATE TABLE TestMaster:
CREATE TABLE `TestMaster` (
`TestMasterID` int(11) NOT NULL AUTO_INCREMENT,
`field1` varchar(50) NOT NULL,
`F_TestOneID` char(4) NOT NULL,
`F_TwoID` int(11) NOT NULL,
PRIMARY KEY (`TestMasterID`),
KEY `F_TestOneID` (`F_TestOneID`),
KEY `F_TwoID` (`F_TwoID`),
CONSTRAINT `TestMaster_ibfk_1` FOREIGN KEY (`F_TestOneID`) REFERENCES
`TestOne` (`OneID`),
CONSTRAINT `TestMaster_ibfk_2` FOREIGN KEY (`F_TwoID`) REFERENCES
`TestTwo` (`TwoID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

Can anyone please help me?
/henrik

On 2011-01-06 21:08, Henrik Hartig wrote:
Drop the foreign key that uses the index first:

mysql> ALTER TABLE TestMaster DROP foreign key TestMaster_ibfk_1;
Query OK, 1 row affected (0.06 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE TestMaster DROP INDEX F_TestOneID;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

The name of the foreign key is in your case generated by mysql and you
will have to look it up in INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.
It is a whole lot easier to maintain the schema if you explicitly name
your constraints, see your SHOW CREATE TABLE TestMaster for an example
of the syntax.


/Lennart

[...]

Lennart Jonsson wrote:
And thanks for you're explanation. I completely understand. :-)
/henrik

Henrik Hartig <henrikhartig (AT) varmmail (DOT) dk> wrote:

For future reference:

Errno 150 refers to some problem inside InnoDB for DDL working on
foreign key constraints. Details about that problem can be seen
with SHOW ENGINE INNODB STATUS.


XL

Axel Schwenke wrote: