Mysql和FK的问题 [英] Mysql and FK's Problem

问题描述

我在这里有些表格有问题。

我有这个表格：

  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`）参考`smodulos`（`codmodulo`）
）
 COLLATE ='utf8_unicode_ci'
 ENGINE = InnoDB 
 ROW_FORMAT = DEFAULT 
  

第二个：

  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'
  

我的问题是我需要在代码之间做一个FK
i有这个sql命令，导致一个错误： / p>



  ALTER TABLE`smenuitememp`ADD CONSTRAINT`FK_smenuitememp_smenuitem`FOREIGN KEY（`codmenuitem`）参考`smenuitem`（`codmenuitem`）; 
  

当我尝试执行它时返回此错误：

有人有想法？

---

更新...我试图解决问题，并得到一个新问题... （
`nome`）VARCHAR（150）NULL DEFAULT NULL COLLATE'utf8_unicode_ci'，$ t

$ b

 
`url` VARCHAR（150）NULL DEFAULT NULL COLLATE'utf8_unicode_ci'，
`tipo` CHAR（4）NULL DEFAULT NULL COLLATE'utf8_unicode_ci'，
`ordemmenu` INT DEFAULT NULL，
`codparent` INT（10）UNSIGNED NOT NULL，
`codmenuitem` INT（10）UNSIGNED NOT NULL，
`codmodulo` INT $ b PRIMARY KEY（`codmodulo`，`codmenuitem`），
 INDEX`codmenuitem`（`codmenuitem`），
 CONSTRAINT`FK_smenuitem_smodulos` FOREIGN KEY（`codmodulo`）参考`smodulos` `）
）
 COLLATE ='utf8_unicode_ci'
 ENGINE = InnoDB 
 ROW_FORMAT = DEFAULT 
  

我解决了在主表创建索引的问题。但我不知道为什么我有麻烦没有这个索引。如果有人可以问我，我会apreciate！

解决方案

外键列必须引用列最主要的前缀或父表中的唯一键。

换句话说，以下示例在InnoDB中使用：

  CREATE TABLE Foo（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）参考Foo（b，c）; -  WRONG 
 
 ALTER TABLE条添加外部键（x，y）参考Foo（a，c）; -  WRONG 
 
 ALTER TABLE条添加外部键（x，y）参考Foo（a，b）; -  RIGHT 
 
 ALTER TABLE条添加外部键（x）参考Foo（b）; -  WRONG 
 
 ALTER TABLE条添加外键（x）参考Foo（a）; -  RIGHT 
  

您收到一个错误，因为您试图做相当于Foo（b）。

您的列codmenuitem是父级主键中三列的第二列。

如果 smenuitememp.codemenuitem 引用 smenuitem.codmodulo ，因为该列是父表的主键中最左侧的列。 p>

---

重复您的后续问题：

记住外国人的方式键工作。每次在子表中插入或更新行时，它都需要在父表中查找一行，以验证引用列中是否存在该值。如果列没有索引，它将必须做一个表扫描来实现这个查找，这将是非常昂贵的，假设你的父表增长。

如果您尝试基于多列索引的中间列查找一行，则该索引不会帮助您。类似地，它类似于在电话簿中搜索具有特定中间名的所有人。

标准ANSI SQL要求引用的列是PRIMARY KEY或UNIQUE KEY的一部分，并且它要求外键列匹配全部父项中的主要或唯一约束的列。

但是InnoDB更容许。它仍然需要对父表中的引用列进行索引，以便查找可以是有效的，和表明引用的列是索引中最左侧的列。但是非唯一索引是可以的;它允许外键来引用它。

这可能会导致一些奇怪的情况，例如一个子行引用父行中多个行，但预计将处理此类异常。

---

我觉得需要强调最后一点。如果您在父级中将外键定义为非唯一索引列，则您会获取异常数据。这可能会导致您的查询在您加入时多次报告行。你不应该使用InnoDB的这种行为;您应该仅将外键定义为唯一的父列。

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!

解决方案

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.

这篇关于Mysql和FK的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！