Mysql and FK's Problem

Question

i'm having trouble with some tables here.

i have this table:

CREATE TABLE `smenuitem` (
    `nome` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `url` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `tipo` CHAR(4) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `ordemmenu` INT(10) NULL DEFAULT NULL,
    `codparent` INT(10) UNSIGNED NOT NULL,
    `codmenuitem` INT(10) UNSIGNED NOT NULL,
    `codmodulo` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`codmodulo`, `codmenuitem`, `codmenuitem2`),
    CONSTRAINT `FK_smenuitem_smodulos` FOREIGN KEY (`codmodulo`) REFERENCES `smodulos` (`codmodulo`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

And an second one:

CREATE TABLE `smenuitememp` (
    `codempresa` INT(10) UNSIGNED NOT NULL,
    `codmodulo` INT(10) UNSIGNED NOT NULL,
    `codmenuitem` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`codmenuitem`, `codempresa`, `codmodulo`)
)
COLLATE='utf8_unicode_ci'

My problem it's i need to make an FK between codmenuitem i have this sql command that are resulting on an error:

ALTER TABLE `smenuitememp`  ADD CONSTRAINT `FK_smenuitememp_smenuitem` FOREIGN KEY (`codmenuitem`) REFERENCES `smenuitem` (`codmenuitem`);

When i try to execute it's return this error:

Someone has an idea?

---

Update... i was trying to solve the problem, and got an new question... T_T

CREATE TABLE `smenuitem` (
    `nome` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `url` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `tipo` CHAR(4) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `ordemmenu` INT(10) NULL DEFAULT NULL,
    `codparent` INT(10) UNSIGNED NOT NULL,
    `codmenuitem` INT(10) UNSIGNED NOT NULL,
    `codmodulo` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`codmodulo`, `codmenuitem`),
    INDEX `codmenuitem` (`codmenuitem`),
    CONSTRAINT `FK_smenuitem_smodulos` FOREIGN KEY (`codmodulo`) REFERENCES `smodulos` (`codmodulo`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

I solved the problem creating an index at the main table. But i don't know why i was having trouble without this index. If someone could ask me i would apreciate!

Answer 1

The foreign key column(s) must reference column(s) comprising a left-most prefix of the primary key or a unique key in the parent table.

In other words, the following examples work in InnoDB:

CREATE TABLE Foo ( a INT, b INT, c INT, PRIMARY KEY (a,b,c) );
CREATE TABLE Bar ( x INT, y INT );

ALTER TABLE Bar ADD FOREIGN KEY (x,y) REFERENCES Foo(b,c); -- WRONG

ALTER TABLE Bar ADD FOREIGN KEY (x,y) REFERENCES Foo(a,c); -- WRONG

ALTER TABLE Bar ADD FOREIGN KEY (x,y) REFERENCES Foo(a,b); -- RIGHT

ALTER TABLE Bar ADD FOREIGN KEY (x) REFERENCES Foo(b); -- WRONG

ALTER TABLE Bar ADD FOREIGN KEY (x) REFERENCES Foo(a); -- RIGHT

You got an error because you're trying to do the equivalent of (x) references Foo(b).
Your column codmenuitem is the second of three columns in the primary key of the parent.

It would work if smenuitememp.codemenuitem were to reference smenuitem.codmodulo, because that column is the leftmost column in the parent table's primary key.

---

Re your followup question:

Keep in mind the way foreign keys work. Every time you insert or update a row in the child table, it needs to look up a row in the parent table to verify that the value exists in the referenced column. If the column isn't indexed, it'll have to do a table-scan to achieve this lookup, and that would be very expensive, assuming your parent table grows.

If you try to look up a row based on the middle column of a multi-column index, the index doesn't help you. By analogy, it's like searching a telephone book for all people with a certain middle name.

Standard ANSI SQL requires that the referenced column be part of a PRIMARY KEY or UNIQUE KEY, and it requires that the foreign key columns match all the columns of a primary or unique constraint in the parent.

But InnoDB is more permissive. It still requires that the referenced column in the parent table be indexed so the lookup can be efficient, and that the referenced columns be the leftmost in the index. But a non-unique index is okay; it's allowed for a foreign key to reference it.

This can lead to weird cases like a child row that references more than one row in the parent, but it's expected that you will handle such anomalies.

---

I feel the need to emphasize the last point. You will get anomalous data if you define foreign keys to non-uniquely indexed columns in the parent. This will probably cause your queries to report rows multiple time when you do joins. You should not use this behavior of InnoDB; you should define foreign keys only to parent columns that are unique.