使用doctrine2 / symfony2与外键的1-N关系不是主键（主键是auto_increment'id'）

I have the following two tables:

我有以下两个表:

CREATE TABLE IF NOT EXISTS `skills` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `skill_category` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `skill` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `icon_filename` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `display_priority` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `skill_category` (`skill_category`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `skills_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `skill_category` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `display_priority` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_95FF1C8D47E90E27` (`skill_category`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

ALTER TABLE `skills`
  ADD CONSTRAINT `skills_ibfk_1` FOREIGN KEY (`skill_category`) REFERENCES `skills_categories` (`skill_category`);

Note the foreign key relation between the two skill_category columns.

注意两个skill_category列之间的外键关系。

I am trying to create a manytoone/onetomany relation between these tables. A one unique skill_category should have many skills. This structure seems to work fine, except when I try and verify the mapping I get the error:

我试图在这些表之间创建manytoone / onetomany关系。一个独特的skill_category应该有很多技能。这个结构似乎工作正常,除非我尝试验证映射我得到错误:

The referenced column name 'skill_category' has to be a primary key column on the target entity class 'Resume\ResumeBundle\Entity\SkillsCategories'.

引用的列名称'skill_category'必须是目标实体类'Resume \ ResumeBundle \ Entity \ SkillsCategories'上的主键列。

There is a requirement by doctrine that the referenced column be a primary key. If I make this key a primary key, my id column is no longer auto incremented, which I want. So mySQL seems to be ok with the fact that the foreign key is a primary key, however Doctrine complains about this case. Someone said they fixed it by just recreating the columns:

学说要求引用的列是主键。如果我将此键设为主键,则我的id列不再自动递增,这是我想要的。所以mySQL似乎可以正确使用外键是主键,但是Doctrine抱怨这种情况。有人说他们只是通过重新创建列来修复它:

http://goo.gl/vvq0tu

i've tried this, and it didn't seem to help for me. So either this is some sort of bug or I have a fundamental misunderstanding of RDBMS (completely possible). Is my problem that I shouldn't be using an 'id' column at all? That if I expect my 'skills_category' column to be unique that I should make THAT the primary key and just remove the 'id' column? That would solve my problems, but is there any problems with making a VARCHAR the primary key? Google's answer seems to be 'not really', but I would appreciate some perspective from someone else.

我试过这个,对我来说似乎没什么帮助。所以要么这是某种错误,要么我对RDBMS有一个根本的误解(完全可能)。我的问题是我不应该使用'id'列吗?如果我希望我的'skills_category'列是唯一的,我应该把它作为主键并删除'id'列?这可以解决我的问题,但是将VARCHAR作为主键是否存在任何问题?谷歌的回答似乎是“不是真的”,但我会欣赏别人的一些观点。

2 个解决方案

#1

This is exactly the same issue I had when I first started using Doctrine, and yes, it comes from a slight misunderstanding about how Doctrine maps objects to relational databases.

这与我第一次使用Doctrine时遇到的问题完全相同,是的,它来自对Doctrine如何将对象映射到关系数据库的轻微误解。

Let's start by stepping out of the "relational" world, and treating your entities like the objects they are. In your case, there are two classes of objects here: You have a Skill, and you have a Category.

让我们从走出“关系”世界开始,把你的实体当作对象来对待。在您的情况下,这里有两类对象:您有一个技能,并且您有一个类别。

Those are the only two objects at play. There is no such thing as a SkillsCategories entity... it doesn't make sense as an object -- it's a relationship. And as such, it doesn't really mean much to give it an ID.

这是发挥作用的唯一两个对象。没有SkillsCategories实体......它作为一个对象没有意义 - 它是一种关系。因此,给它一个ID并不是很重要。

So what do these two entities actually look like?

那么这两个实体究竟是什么样子呢?

Skill:

• An ID
• A Name
• An icon filename

图标文件名

• A display priority

显示优先级

• The Category entity to which it belongs

它所属的Category实体

Category:

• An ID
• A Name
• A list of Skill entities that belong to it

属于它的技能实体列表

And when you put these entities in the database, it looks just like you'd expect (except that we don't map the list of Skills belonging to a Category -- that'll be handled by Doctrine later on). I'd recommend you update your schema to reflect this change:

当你将这些实体放在数据库中时,它看起来就像你期望的那样(除了我们没有映射属于类别的技能列表 - 稍后将由Doctrine处理)。我建议您更新架构以反映此更改:

CREATE TABLE IF NOT EXISTS `Skill` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `categoryId` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `iconFilename` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `displayPriority` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `skill_category` (`categoryId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `Category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `displayPriority` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_95FF1C8D47E90E27` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Now you can define the relationship through Doctrine. Here's what it might look like with YAML:

现在您可以通过Doctrine定义关系。以下是YAML的外观:

Path\To\Entity\Skill:
    type: entity
    table: Skill
    fields:
        id:
            id: true
            type: integer
            generator
                strategy: AUTO
        name:
            type: string
            length: 255
        iconFilename:
            type: string
            length: 50
        displayPriority:
            type: integer
    manyToOne:
        category:
            targetEntity: Category
            inversedBy: skills
            joinColumn:
                name: categoryId
                referencedColumnName: id

Path\To\Entity\Category:
    type: entity
    table: Category
    fields:
        id:
            id: true
            type: integer
            generator
                strategy: AUTO
        name:
            type: string
            length: 255
        displayPriority:
            type: integer
    oneToMany:
        skills:
            targetEntity: Skill
            mappedBy: category

And now you have a fully working model!

现在你有一个完全工作的模型!

Some examples...

Say you wanted to get the displayPriority of a Skill's Category:

假设您想获得技能类别的displayPriority:

$skill->getCategory()->getDisplayPriority();

...or if you wanted to get a list of Skill names under a given Category:

...或者如果您想获得给定类别下的技能名称列表:

foreach ($category->getSkills() as $skill) {
    echo $skill->getName();
}

Hope this helps clear things up a little...

希望这有助于清理一点......

#2

There is no problem making a varchar a primary key. If you really have a space or key size requirement you can set up a lookup code table, but it will make your database and code more complicated than it needs to be.

将varchar作为主键是没有问题的。如果您确实有空间或密钥大小要求,则可以设置查找代码表,但这会使您的数据库和代码变得比它需要的更复杂。

I personally use auto increment keys only for input from processes I don't control: orders, tickets, events, etc. Generally the things are you are tracking from a web process.

我个人使用自动增量键仅用于来自我无法控制的进程的输入:订单,票证,事件等。通常情况下,您正在从Web进程进行跟踪。