Symfony doctrine trying to insert a null primary key

Question

So, I just started studying symfony framework and I've been struggling to figure out how to work with some relationships I have in my database. To clarify, I'm making an admin panel for a pre-existing game database which is sort of messed up, but I can't change anything in this sql server database or else the game will have issues.

everything works just fine until I try to insert a new register into the database. If I remove the relationship, it works as expected, but if I keep it and attempt to register a new user, I get the following message:

SQLSTATE[23000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Cannot insert explicit value for identity column in table 'users' when IDENTITY_INSERT is set to OFF. I also notice it tries to insert LoginUID which is the auto increment index key of my table.

User Entity

class Users
{
/**
 * @var int
 *
 * @ORM\Column(name="LoginUID", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

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

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

/**
 * @var string
 *
 * @ORM\Column(name="Connecting", type="integer")
 */
private $connecting;

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

/**
 * @var integer
 *
 * @ORM\Column(name="sex", type="integer")
 */
private $sex;

/**
 * @var \DateTime
 *
 * @ORM\Column(name="firstlogin", type="datetime")
 */
private $firstlogin;

/**
 * @var \DateTime
 *
 * @ORM\Column(name="lastlogin", type="datetime")
 */
private $lastlogin;

/**
 * @ORM\OneToOne(targetEntity="UserAuth", fetch="EAGER")
 * @ORM\JoinColumn(name="LoginUID", referencedColumnName="LoginUID")
 */
private $userAuth = null;

/**
 *
 * @ORM\OneToOne(targetEntity="UserNickname", fetch="EAGER")
 * @ORM\JoinColumn(name="LoginUID", referencedColumnName="LoginUID")
 */
private $nickname = null;

/**
 * Get Id
 *
 * @return int
 */
public function getId()
{
    return $this->id;
}

User Auth Entity:

class UserAuth
{
/**
 * @var int
 *
 * @ORM\Column(name="LoginUID", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var int
 *
 * @ORM\Column(name="AuthLevel", type="integer")
 *
 * @ORM\OneToOne(targetEntity="Users")
 *
 * @ORM\JoinColumn(name="LoginUID", referencedColumnName="LoginUID")
 */
private $authLevel;

/**
 * @var bool
 *
 * @ORM\Column(name="IsReset", type="boolean")
 */
private $isReset;

/**
 *
 * @ORM\OneToOne(targetEntity="UserNickname", fetch="EAGER")
 *
 * @ORM\JoinColumn(name="LoginUID", referencedColumnName="LoginUID")
 */
private $nickname;

/**
 *
 * @ORM\OneToOne(targetEntity="UserAuthDate", fetch="EAGER")
 * @ORM\JoinColumn(name="LoginUID", referencedColumnName="LoginUID")
 */
private $limitDate;

/**
 * Get id
 *
 * @return int
 */
public function getId()
{
    return $this->id;
}

An user doesn't really need to have a register in UserAuth, this table is used to register banned users and game masters, I want to make this association so I can fetch the user auth together with the account data in order to display it in a table. Any help will be appreciated.

Edit: I noticed the problem seems to be with the JoinColumn annotation, figured it is actually trying to join the LoginUID from UserAuth with the dbo.users, is there any way to prevent it from happening? I just want to use this relation to retrieve the user auth.

posible duplicate https://stackoverflow.com/questions/1334012/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity — SilvioQ, Jan 29 '19 at 14:18
It's not duplicate, my problem is something else, it's related to the doctrine orm itself. — RedSaba, Jan 29 '19 at 14:26

Hakim · Answer 1 · 2019-01-30T08:05:19.300

0

I think you might have a problem in your UserAuth entity. Your id column is named LoginUID but you use the same name in your One-to-one relation. So I think Doctrine tries to set the nickname value for example, in the id field. You only need to bind one field from User in UserAuth.

{
/**
 * @var int
 * @ORM\Id
 * @ORM\OneToOne(targetEntity="Users", fetch="EAGER")
 * @ORM\JoinColumn(name="LoginUID", referencedColumnName="LoginUID") //from User
 */
private $id;

/**
 *
 * @ORM\Column(name="nickname")
 */
private $nickname;

EDIT : I modified the id property. This might work, the property name of the JoinColumn must refer to the column id name in your UserAuth Table.

edited Jan 30 '19 at 08:05

answered Jan 29 '19 at 15:14

Hakim

1,054
10
26

isn't this column used to refer to the column name in database? Now I get the message: SQLSTATE[42S22]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid column name 'user_id'. – RedSaba Jan 29 '19 at 15:27
My bad, I forgot you can't modify your SQL database. I'm updating my answer. Maybe you can map your `UserAuthId` to `UserId` – Hakim Jan 30 '19 at 07:59
Hmm, I keep getting the same error... Shouldn't not the join annotation be in the owning side of the relation though? This is how my database actually looks like https://imgur.com/a/TALvuqq – RedSaba Jan 30 '19 at 09:37
You can try to put it on the owning side, or even try a bi-directional, just to see if it works : https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/association-mapping.html#one-to-one-bidirectional – Hakim Jan 30 '19 at 09:54
But that's how it was before I touch the code. Sadly nothing seems to work, I'm starting to wonder if symfony was really a good choice. Since I have tried almost everything I could, and I already lost 1 week on it, I think a better solution will be making the query in plain SQL, I was just avoiding that to have a organized code, I think I can also call the query to get the auth and nickname inside the twig template (even tho I don't like it performance wise) but symfony and doctrine seem to be kind of limited when it comes to customization. – RedSaba Jan 30 '19 at 10:06

Symfony doctrine trying to insert a null primary key

1 Answers1