1

I'm modeling a vending machine where I got 3 Entities (Milk, Sugar, Coffee) and one table that lists all my coffee machines.

Now I want to insert a new value for the amount of milk left. The coffee machines are foreign keys in the resources tables.

However, I get this error, which does not seem to make sense, as I am just setting a foreign key field:

An exception occurred while executing a query: SQLSTATE[428C9]: <<Unknown error>>: 7 ERROR: cannot insert into column "id" DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS. HINT: Use OVERRIDING SYSTEM VALUE to override. 

Here are the tables (Postgres) (only coffee listed - milk and sugar have the same architecture):

create table coffee ( id integer not null generated always as identity primary key, request_id bigint, logdate timestamp(0) without time zone not null, vmc_no integer, amount integer not null, constraint fk_request foreign key(request_id) references energyomat.request(id), constraint fk_vmc_company foreign key(vmc_no) references energyomat.vmc_company(vmc_no) ); create table vmc_company ( vmc_no integer not null unique primary key, register_date timestamp(0) without time zone not null ); 

I've set the primitive fields and got the Entity of VmcCompany and set it as the foreign value (setVmcNo).

public function updateResources($vmcNo, $milkAmount, $sugarAmount, $coffeeAmount) { $milk = new Milk(); $sugar = new Sugar(); $coffee = new Coffee(); if($milkAmount != null) { $milk->setAmount($milkAmount); $milk->setLogdate(new \DateTime()); $vmc = $this->doctrine->getRepository(VmcCompany::class)->find($vmcNo); $milk->setVmcNo($vmc); $manager = $this->doctrine->getManager(); $manager->persist($milk); } if($sugarAmount != null) { $sugar->setAmount($sugarAmount); $sugar->setLogdate(new \DateTime()); $vmc = $this->doctrine->getRepository(VmcCompany::class)->find($vmcNo); $sugar->setVmcNo($vmc); $manager = $this->doctrine->getManager(); $manager->persist($sugar); } if($coffeeAmount != null) { $coffee->setAmount($coffeeAmount); $coffee->setLogdate(new \DateTime()); $vmc = $this->doctrine->getRepository(VmcCompany::class)->find($vmcNo); $coffee->setVmcNo($vmc); $manager = $this->doctrine->getManager(); $manager->persist($coffee); } $manager->flush(); } 

Here are the Entity classes: Coffee:

<?php namespace App\Entity; use Doctrine\ORM\Mapping as ORM; /** * Coffee * * @ORM\Table(name="coffee", indexes={@ORM\Index(name="IDX_538529B3427EB8A5", columns={"request_id"}), @ORM\Index(name="IDX_538529B3651BF703", columns={"vmc_no"})}) * @ORM\Entity */ class Coffee { /** * @var int * * @ORM\Column(name="id", type="integer", nullable=false) * @ORM\Id * @ORM\GeneratedValue(strategy="SEQUENCE") * @ORM\SequenceGenerator(sequenceName="coffee_id_seq", allocationSize=1, initialValue=1) */ private $id; /** * @var \DateTime * * @ORM\Column(name="logdate", type="datetime", nullable=false) */ private $logdate; /** * @var int * * @ORM\Column(name="amount", type="integer", nullable=false) */ private $amount; /** * @var \Request * * @ORM\ManyToOne(targetEntity="Request") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="request_id", referencedColumnName="id") * }) */ private $request; /** * @var \VmcCompany * * @ORM\ManyToOne(targetEntity="VmcCompany") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="vmc_no", referencedColumnName="vmc_no") * }) */ private $vmcNo; public function getId(): ?int { return $this->id; } public function getLogdate(): ?\DateTimeInterface { return $this->logdate; } public function setLogdate(\DateTimeInterface $logdate): self { $this->logdate = $logdate; return $this; } public function getAmount(): ?int { return $this->amount; } public function setAmount(int $amount): self { $this->amount = $amount; return $this; } public function getRequest(): ?Request { return $this->request; } public function setRequest(?Request $request): self { $this->request = $request; return $this; } public function getVmcNo(): ?VmcCompany { return $this->vmcNo; } public function setVmcNo(?VmcCompany $vmcNo): self { $this->vmcNo = $vmcNo; return $this; } } 

VmcCompany:

<?php namespace App\Entity; use Doctrine\ORM\Mapping as ORM; /** * VmcCompany * * @ORM\Table(name="vmc_company") * @ORM\Entity */ class VmcCompany { /** * @var int * * @ORM\Column(name="vmc_no", type="integer", nullable=false) * @ORM\Id * @ORM\GeneratedValue(strategy="SEQUENCE") * @ORM\SequenceGenerator(sequenceName="vmc_company_vmc_no_seq", allocationSize=1, initialValue=1) */ private $vmcNo; /** * @var \DateTime * * @ORM\Column(name="register_date", type="datetime", nullable=false) */ private $registerDate; public function getVmcNo(): ?int { return $this->vmcNo; } public function getRegisterDate(): ?\DateTimeInterface { return $this->registerDate; } public function setRegisterDate(\DateTimeInterface $registerDate): self { $this->registerDate = $registerDate; return $this; } } 

I'm new to Symfony / Doctrine and so I guess I'm overseeing something, but just cannot find out what it is. Thanks for your help in advance.

1
  • I think the problem is a conflict with the ID generation via GENERATED ALWAYS AS IDENTITY in Postgres. It seems like Doctrine wants to provide a value but Postgres does throw an arrow as it does not allow assignment. Is there a way to solve that? Commented May 23, 2022 at 13:15

1 Answer 1

2

If someone else runs into this problem:

You need to declare the id field in the database table as follows:

id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 

The emphasis lies on BY DEFAULT instead of ALWAYS.

Otherwise Symfony runs into a conflict with Postgres while auto-incrementing.

Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.