What am I missing - Symfony 3 Doctrine Entity ManyToMany?

Question

So both my entities have the ID property on them, yet I am getting a validation error about said IDs for both tables described below. I have seen a few other example of this issue here on SO, and have followed their solutions but still have yet to get passed this validation error.

I have followed Doctrines docs too and I guess I am just missing something. I have only been working with Symfony/Doctrine for a couple months now so maybe I am missing a concept.

I run php bin/console doctrine:schema:validate

[Mapping]  FAIL - The entity-class 'FacetCoreBundle\Entity\FacetUser' mapping is invalid:
* The referenced column name 'id' has to be a primary key column on the target entity class 'FacetCoreBundle\Entity\FacetUser'.
* The referenced column name 'id' has to be a primary key column on the target entity class 'FacetCoreBundle\Entity\FacetRole'.

[Database] OK - The database schema is in sync with the mapping files.

Ok well My FacetUser entity has the ID column and it is a primary key, same with the FacetRole entity.

FacetUser.php

/**
 * @ORM\Entity()
 * @ORM\Table(name="FACET_USER")
 */
class FacetUser implements LdapUserInterface, UserInterface
{
    /**
     * @var integer
     *
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     * @ORM\Column(name="ID", type="integer")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="LDAP_GUID", type="string", length=100, unique=true)
     * LdapAttribute objectguid
     */
    private $ldapGuid;

    /**
     * @var string
     *
     * @ORM\Column(name="USERNAME", type="string", length=20)
     * LdapAttribute samaccountname
     */
    private $username;

    /**
     * @var string
     *
     * @ORM\Column(name="FIRST_NAME", type="string", length=100)
     * LdapAttribute givenname
     */
    private $firstName;

    /**
     * @var string
     *
     * @ORM\Column(name="LAST_NAME",type="string", length=100)
     * LdapAttribute sn
     */
    private $lastName;

    /**
     * @var string
     *
     * @ORM\Column(name="DISPLAY_NAME",type="string", length=255)
     * LdapAttribute displayname
     */
    private $displayName;

    /**
     * @var string
     *
     * @ORM\Column(name="TITLE",type="string", length=255)
     * LdapAttribute title
     */
    private $title;

    /**
     * @var string
     *
     * @ORM\Column(name="DEPARTMENT",type="string", length=100)
     * LdapAttribute department
     */
    private $department;

    /**
     * @var integer
     *
     * @ORM\Column(name="EMPLOYEE_ID",type="integer", length=10)
     * LdapAttribute employeeid
     */
    private $employeeId;

    /**
     * @var string
     *
     * @ORM\Column(name="MANAGER_USERNAME",type="string", length=7, nullable=true)
     */
    private $managerUsername;

    /**
     * @var string
     *
     * @ORM\Column(name="MANAGER_DISPLAY_NAME",type="string", length=255, nullable=true)
     */
    private $managerDisplayName;

    /**
     * @var string
     *
     * @ORM\Column(name="MANAGER_LDAP_GUID",type="string", length=100, nullable=true)
     */
    private $managerLdapGuid;

    /**
     * @var integer
     *
     * @ORM\Column(name="MANAGER_EMPLOYEE_ID",type="integer", length=7, nullable=true)
     */
    private $managerEmployeeId;

    /**
     * @var string
     *
     * @ORM\Column(name="EMAIL",type="string", nullable=true)
     * LdapAttribute mail
     */
    private $email;

    /**
     * @var string
     *
     * @ORM\Column(name="MOBILE",type="string", nullable=true)
     * LdapAttribute mobile
     */
    private $mobile;

    /**
     * @var array
     *
     * @ORM\Column(name="LDAP_MEMBERSHIPS",type="array", nullable=true)
     * LdapAttribute memberof
     */
    private $ldapMemberships = [];

    /**
     *
     * User can have many roles
     * @var array
     *
     * @ORM\ManyToMany(targetEntity="FacetCoreBundle\Entity\FacetRole")
     * @ORM\JoinTable(name="USER_ROLES",
     *     joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")},
     *     inverseJoinColumns={@ORM\JoinColumn(name="role_id", referencedColumnName="id")}
     *     )
     *
     */
    private $roles;

    public function __construct()
    {
        $this->roles = new ArrayCollection();
    }

    ....

FacetRole.php

/**
 * FacetRole
 *
 * @ORM\Table(name="FACET_ROLE")
 * @ORM\Entity
 */
class FacetRole
{
    /**
     * @var integer
     *
     * @ORM\Column(name="ID", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="SEQUENCE")
     */
    private $id;

    /**
     * @var string
     *
     * @Assert\NotBlank()
     * @Assert\Length(
     *      min = 9,
     *      max = 100,
     *      minMessage = "Role name must be at least {{ limit }} characters long",
     *      maxMessage = "Role name cannot be longer than {{ limit }} characters"
     * )
     * @ORM\Column(name="ROLE_NAME", type="string", length=255, nullable=false, unique=true)
     *
     */
    private $roleName;

    /**
     * @var string
     *
     * @Assert\NotBlank()
     * @Assert\Choice({"Y","N"})
     *
     * @ORM\Column(name="GRANTABLE", type="string", length=1, nullable=false)
     */
    private $grantable;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="CREATED_ON", type="date", nullable=false)
     */
    private $createdOn;

    /**
     * @var string
     *
     * @Assert\NotBlank()
     * @Assert\Length(
     *     min="10",
     *     max="255",
     *     minMessage="Description needs to be at least {{ limit }} characters long.",
     *     maxMessage="Description needs to be less then {{ limit }} characters long."
     * )
     *
     * @ORM\Column(name="DESCRIPTION", type="string", length=255, nullable=true)
     */
    private $description;

    /**
     * @var FacetUser
     *
     * @ORM\ManyToOne(targetEntity="FacetUser")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="CREATED_BY", referencedColumnName="ID", nullable=false)
     * })
     */
    private $createdBy;

    ....

This creates the USER_ROLES table which stores the roles a user has been granted.

Answer 1

Per a comment by Greg, be sure to check character case! Refer to the 3rd bullet of the Doctrine Portability docs

Case-sensitivity of column keys is handled differently in all databases, even depending on identifier quoting or not. You either need to know all the rules or fix the cases to lower/upper-case only.