1-N relation with foreign key which is NOT a primary key (primary is auto_increment 'id') with doctrine2/symfony2

Question

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.

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:

The referenced column name 'skill_category' has to be a primary key column on the target entity class '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:

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.

Answer 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.

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.

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:

• 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:

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:

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:

$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...

Answer 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.

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.