I'm trying to construct four entities using Doctrine Symfony 2.
One of the entities has "Overlapped or Intersected Non Primary Key Composite Foreign Key" - apologize for my bad English.
I've tried to modify doctrine object but I can't still persist all the entities to PostgreSQL.
Below the doctrine objects of four entities I've constructed:
<?php
namespace EntityBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
/**
* @ORM\Entity(repositoryClass="EntityBundle\EntityRepository\GeographyContinentRepository")
* @ORM\Table(
* name="geography_continent",
* uniqueConstraints={
* @ORM\UniqueConstraint(name="geography_continent_u1", columns={"continent_name"})
* }
* )
*/
class GeographyContinent
{
/**
* @ORM\Column(name="id", type="integer", nullable=false)
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
protected $id;
/**
*
* @ORM\Column(name="continent_name", type="string", nullable=false)
*/
protected $continentName;
/**
*
* @ORM\Column(name="description", type="string", nullable=true)
*/
protected $description;
/**
* Get id
*
* @return integer
*/
public function getId()
{
return $this->id;
}
/**
* Set continentName
*
* @param string $continentName
* @return GeographyContinent
*/
public function setContinentName($continentName)
{
$this->continentName = $continentName;
return $this;
}
/**
* Get continentName
*
* @return string
*/
public function getContinentName()
{
return $this->continentName;
}
/**
* Set description
*
* @param string $description
* @return GeographyContinent
*/
public function setDescription($description)
{
$this->description = $description;
return $this;
}
/**
* Get description
*
* @return string
*/
public function getDescription()
{
return $this->description;
}
}
?>
<?php
namespace EntityBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
/**
* @ORM\Entity(repositoryClass="EntityBundle\EntityRepository\GeographyCountryRepository")
* @ORM\Table(
* name="geography_country",
* uniqueConstraints={
* @ORM\UniqueConstraint(name="geography_country_u1", columns={"country_name"}),
* @ORM\UniqueConstraint(name="geography_country_u2", columns={"telephone_code"}),
* @ORM\UniqueConstraint(name="geography_country_u3", columns={"currency_name"}),
* @ORM\UniqueConstraint(name="geography_country_u4", columns={"currency_symbol"})
* }
* )
*/
class GeographyCountry
{
/**
* @ORM\Column(name="id", type="integer", nullable=false)
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
protected $id;
/**
*
* @ORM\Column(name="country_name", type="string", nullable=false)
*/
protected $countryName;
/**
*
* @ORM\Column(name="telephone_code", type="string", nullable=true)
*/
protected $telephoneCode;
/**
*
* @ORM\Column(name="currency_name", type="string", nullable=true)
*/
protected $currencyName;
/**
*
* @ORM\Column(name="currency_symbol", type="string", nullable=true)
*/
protected $currencySymbol;
/**
*
* @ORM\Column(name="continent_id", type="integer", nullable=false)
*/
protected $continentId;
/**
*
* @ORM\ManyToOne(targetEntity="GeographyContinent", cascade={"persist", "remove"})
* @ORM\JoinColumn(name="continent_id", referencedColumnName="id")
*/
protected $fkContinent;
/**
*
* @ORM\Column(name="description", type="string", nullable=true)
*/
protected $description;
/**
* Get id
*
* @return integer
*/
public function getId()
{
return $this->id;
}
/**
* Set countryName
*
* @param string $countryName
* @return GeographyCountry
*/
public function setCountryName($countryName)
{
$this->countryName = $countryName;
return $this;
}
/**
* Get countryName
*
* @return string
*/
public function getCountryName()
{
return $this->countryName;
}
/**
* Set telephoneCode
*
* @param string $telephoneCode
* @return GeographyCountry
*/
public function setTelephoneCode($telephoneCode)
{
$this->telephoneCode = $telephoneCode;
return $this;
}
/**
* Get telephoneCode
*
* @return string
*/
public function getTelephoneCode()
{
return $this->telephoneCode;
}
/**
* Set currencyName
*
* @param string $currencyName
* @return GeographyCountry
*/
public function setCurrencyName($currencyName)
{
$this->currencyName = $currencyName;
return $this;
}
/**
* Get currencyName
*
* @return string
*/
public function getCurrencyName()
{
return $this->currencyName;
}
/**
* Set currencySymbol
*
* @param string $currencySymbol
* @return GeographyCountry
*/
public function setCurrencySymbol($currencySymbol)
{
$this->currencySymbol = $currencySymbol;
return $this;
}
/**
* Get currencySymbol
*
* @return string
*/
public function getCurrencySymbol()
{
return $this->currencySymbol;
}
/**
* Set continentId
*
* @param integer $continentId
* @return GeographyCountry
*/
public function setContinentId($continentId)
{
$this->continentId = $continentId;
return $this;
}
/**
* Get continentId
*
* @return integer
*/
public function getContinentId()
{
return $this->continentId;
}
/**
* Set description
*
* @param string $description
* @return GeographyCountry
*/
public function setDescription($description)
{
$this->description = $description;
return $this;
}
/**
* Get description
*
* @return string
*/
public function getDescription()
{
return $this->description;
}
/**
* Set fkContinent
*
* @param \EntityBundle\Entity\GeographyContinent $fkContinent
* @return GeographyCountry
*/
public function setFkContinent(\EntityBundle\Entity\GeographyContinent $fkContinent = null)
{
$this->fkContinent = $fkContinent;
return $this;
}
/**
* Get fkContinent
*
* @return \EntityBundle\Entity\GeographyContinent
*/
public function getFkContinent()
{
return $this->fkContinent;
}
}
?>
<?php
namespace EntityBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
/**
* @ORM\Entity(repositoryClass="EntityBundle\EntityRepository\GeographyProvinceRepository")
* @ORM\Table(
* name="geography_province",
* uniqueConstraints={
* @ORM\UniqueConstraint(name="geography_province_u1", columns={"country_id", "id"}),
* @ORM\UniqueConstraint(name="geography_province_u2", columns={"country_id", "province_name"})
* }
* )
*/
class GeographyProvince
{
/**
* @ORM\Column(name="id", type="integer", nullable=false)
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
protected $id;
/**
*
* @ORM\Column(name="province_name", type="string", nullable=false)
*/
protected $provinceName;
/**
* @ORM\Column(name="country_id", type="integer", nullable=false)
*/
protected $countryId;
/**
*
* @ORM\ManyToOne(targetEntity="GeographyCountry", cascade={"persist", "remove"})
* @ORM\JoinColumn(name="country_id", referencedColumnName="id")
*/
protected $fkCountry;
/**
*
* @ORM\Column(name="description", type="string", nullable=true)
*/
protected $description;
/**
* Get id
*
* @return integer
*/
public function getId()
{
return $this->id;
}
/**
* Set provinceName
*
* @param string $provinceName
* @return GeographyProvince
*/
public function setProvinceName($provinceName)
{
$this->provinceName = $provinceName;
return $this;
}
/**
* Get provinceName
*
* @return string
*/
public function getProvinceName()
{
return $this->provinceName;
}
/**
* Set countryId
*
* @param integer $countryId
* @return GeographyProvince
*/
public function setCountryId($countryId)
{
$this->countryId = $countryId;
return $this;
}
/**
* Get countryId
*
* @return integer
*/
public function getCountryId()
{
return $this->countryId;
}
/**
* Set description
*
* @param string $description
* @return GeographyProvince
*/
public function setDescription($description)
{
$this->description = $description;
return $this;
}
/**
* Get description
*
* @return string
*/
public function getDescription()
{
return $this->description;
}
/**
* Set fkCountry
*
* @param \EntityBundle\Entity\GeographyCountry $fkCountry
* @return GeographyProvince
*/
public function setFkCountry(\EntityBundle\Entity\GeographyCountry $fkCountry = null)
{
$this->fkCountry = $fkCountry;
return $this;
}
/**
* Get fkCountry
*
* @return \EntityBundle\Entity\GeographyCountry
*/
public function getFkCountry()
{
return $this->fkCountry;
}
}
?>
<?php
namespace EntityBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
/**
* @ORM\Entity(repositoryClass="EntityBundle\EntityRepository\GeographyCityRepository")
* @ORM\Table(
* name="geography_city",
* uniqueConstraints={
* @ORM\UniqueConstraint(name="geography_city_u1", columns={"province_id", "is_municipality", "city_name"})
* }
* )
*/
class GeographyCity
{
/**
* @ORM\Column(name="id", type="integer", nullable=false)
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
protected $id;
/**
*
* @ORM\Column(name="city_name", type="string", nullable=false)
*/
protected $cityName;
/**
*
* @ORM\Column(name="is_municipality", type="boolean", nullable=true)
*/
protected $isMunicipality;
/**
*
* @ORM\Column(name="province_id", type="integer", nullable=true)
*/
protected $provinceId;
/**
*
* @ORM\Column(name="country_id", type="integer", nullable=false)
*/
protected $countryId;
/**
*
* @ORM\ManyToOne(targetEntity="GeographyCountry", cascade={"persist", "remove"})
* @ORM\JoinColumn(name="country_id", referencedColumnName="id", nullable=false)
*/
protected $fkCountry;
/**
*
* @ORM\ManyToOne(targetEntity="GeographyProvince", cascade={"persist", "remove"})
* @ORM\JoinColumns
* (
* {
* @ORM\JoinColumn(name="country_id", referencedColumnName="country_id", nullable=false),
* @ORM\JoinColumn(name="province_id", referencedColumnName="id", nullable=true)
* }
* )
*/
protected $fkProvince;
/**
*
* @ORM\Column(name="description", type="string", nullable=true)
*/
protected $description;
/**
* Get id
*
* @return integer
*/
public function getId()
{
return $this->id;
}
/**
* Set cityName
*
* @param string $cityName
* @return GeographyCity
*/
public function setCityName($cityName)
{
$this->cityName = $cityName;
return $this;
}
/**
* Get cityName
*
* @return string
*/
public function getCityName()
{
return $this->cityName;
}
/**
* Set isMunicipality
*
* @param boolean $isMunicipality
* @return GeographyCity
*/
public function setIsMunicipality($isMunicipality)
{
$this->isMunicipality = $isMunicipality;
return $this;
}
/**
* Get isMunicipality
*
* @return boolean
*/
public function getIsMunicipality()
{
return $this->isMunicipality;
}
/**
* Set provinceId
*
* @param integer $provinceId
* @return GeographyCity
*/
public function setProvinceId($provinceId)
{
$this->provinceId = $provinceId;
return $this;
}
/**
* Get provinceId
*
* @return integer
*/
public function getProvinceId()
{
return $this->provinceId;
}
/**
* Set countryId
*
* @param integer $countryId
* @return GeographyCity
*/
public function setCountryId($countryId)
{
$this->countryId = $countryId;
return $this;
}
/**
* Get countryId
*
* @return integer
*/
public function getCountryId()
{
return $this->countryId;
}
/**
* Set description
*
* @param string $description
* @return GeographyCity
*/
public function setDescription($description)
{
$this->description = $description;
return $this;
}
/**
* Get description
*
* @return string
*/
public function getDescription()
{
return $this->description;
}
/**
* Set fkCountry
*
* @param \EntityBundle\Entity\GeographyCountry $fkCountry
* @return GeographyCity
*/
public function setFkCountry(\EntityBundle\Entity\GeographyCountry $fkCountry)
{
$this->fkCountry = $fkCountry;
return $this;
}
/**
* Get fkCountry
*
* @return \EntityBundle\Entity\GeographyCountry
*/
public function getFkCountry()
{
return $this->fkCountry;
}
/**
* Set fkProvince
*
* @param \EntityBundle\Entity\GeographyProvince $fkProvince
* @return GeographyCity
*/
public function setFkProvince(\EntityBundle\Entity\GeographyProvince $fkProvince)
{
$this->fkProvince = $fkProvince;
return $this;
}
/**
* Get fkProvince
*
* @return \EntityBundle\Entity\GeographyProvince
*/
public function getFkProvince()
{
return $this->fkProvince;
}
}
?>
The four entities above able to generate SQL DDL PostgreSQL properly.
Below the generated sql (with few modifications):
CREATE TABLE geography_continent
(
id INT NOT NULL,
continent_name VARCHAR(255) NOT NULL,
description VARCHAR(255) DEFAULT NULL,
PRIMARY KEY(id)
);
CREATE TABLE geography_country
(
id INT NOT NULL,
continent_id INT NOT NULL,
country_name VARCHAR(255) NOT NULL,
telephone_code VARCHAR(255) DEFAULT NULL,
currency_name VARCHAR(255) DEFAULT NULL,
currency_symbol VARCHAR(255) DEFAULT NULL,
description VARCHAR(255) DEFAULT NULL,
PRIMARY KEY(id)
);
CREATE TABLE geography_province
(
id INT NOT NULL,
country_id INT NOT NULL,
province_name VARCHAR(255) NOT NULL,
description VARCHAR(255) DEFAULT NULL,
PRIMARY KEY(id)
);
CREATE TABLE geography_city
(
id INT NOT NULL,
country_id INT NOT NULL,
province_id INT DEFAULT NULL,
city_name VARCHAR(255) NOT NULL,
is_municipality BOOLEAN DEFAULT NULL,
description VARCHAR(255) DEFAULT NULL,
PRIMARY KEY(id)
);
CREATE SEQUENCE geography_continent_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE geography_country_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE geography_province_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE SEQUENCE geography_city_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE UNIQUE INDEX geography_continent_u1 ON geography_continent (continent_name);
CREATE INDEX IDX_6D7254DD921F4C77 ON geography_country (continent_id);
CREATE UNIQUE INDEX geography_country_u1 ON geography_country (country_name);
CREATE UNIQUE INDEX geography_country_u2 ON geography_country (telephone_code);
CREATE UNIQUE INDEX geography_country_u3 ON geography_country (currency_name);
CREATE UNIQUE INDEX geography_country_u4 ON geography_country (currency_symbol);
CREATE INDEX IDX_1657BF92F92F3E70 ON geography_province (country_id);
CREATE UNIQUE INDEX geography_province_u1 ON geography_province (country_id, id);
CREATE UNIQUE INDEX geography_province_u2 ON geography_province (country_id, province_name);
CREATE INDEX IDX_3F82CFCAF92F3E70 ON geography_city (country_id);
CREATE INDEX IDX_3F82CFCAF92F3E70E946114A ON geography_city (country_id, province_id);
CREATE UNIQUE INDEX geography_city_u1 ON geography_city (province_id, is_municipality, city_name);
ALTER TABLE geography_country ADD CONSTRAINT FK_6D7254DD921F4C77 FOREIGN KEY (continent_id) REFERENCES geography_continent (id) MATCH FULL ON UPDATE CASCADE ON DELETE RESTRICT NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE geography_province ADD CONSTRAINT FK_1657BF92F92F3E70 FOREIGN KEY (country_id) REFERENCES geography_country (id) MATCH FULL ON UPDATE CASCADE ON DELETE RESTRICT NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE geography_city ADD CONSTRAINT FK_3F82CFCAF92F3E70 FOREIGN KEY (country_id) REFERENCES geography_country (id) MATCH FULL ON UPDATE CASCADE ON DELETE RESTRICT NOT DEFERRABLE INITIALLY IMMEDIATE;
-- MATCH SIMPLE FOREIGN KEY
ALTER TABLE geography_city ADD CONSTRAINT FK_3F82CFCAF92F3E70E946114A FOREIGN KEY (country_id, province_id) REFERENCES geography_province (country_id, id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT NOT DEFERRABLE INITIALLY IMMEDIATE;
Table "geography_city" have two foreign keys.
One refers to table "geography_country" with column "country_id".
The other refers to table "geography_province" with two columns "country_id" and "province_id", column "province_id" is optional and may NULL (signed as "MATCH SIMPLE" FOREIGN KEY) because perhaps A City of A Country does not have province.
Below main php code of the controller to persist the four entities above:
<?php
$geographyContinentName = "A Continent Name";
$geographyContinent = new GeographyContinent();
$geographyContinent->setContinentName($geographyContinentName);
$geographyCountryName = "A Country Name";
$geographyCountry = new GeographyCountry();
$geographyCountry->setCountryName($geographyCountryName);
$geographyCountry->setFkContinent($geographyContinent);
$geographyProvinceName = "A Province Name";
$geographyProvince = new GeographyProvince();
$geographyProvince->setProvinceName($geographyProvinceName);
$geographyProvince->setFkCountry($geographyCountry);
$geographyCityName = "A City Name";
$geographyCity = new GeographyCity();
$geographyCity->setCityName($geographyCityName);
$geographyCity->setFkCountry($geographyCountry);
$geographyCity->setFkProvince($geographyProvince);
$entityManager = $this->getDoctrine()->getManager();
$entityManager->persist($geographyContinent);
$entityManager->persist($geographyCountry);
$entityManager->persist($geographyProvince);
$entityManager->persist($geographyCity);
$entityManager->flush();
?>
Below the errors produced after running the controller above:
[2013-12-15 06:41:38] request.INFO:
Matched route "entity_geography_create"
(
parameters:
"_controller": "Entity\GeographyBundle\Controller\CreateController::indexAction",
"_route": "entity_geography_create"
)
[2013-12-15 06:41:38] app.ERROR:
Doctrine\DBAL\DBALException:
An exception occurred while executing
'INSERT INTO geography_city (id, city_name, is_municipality, province_id, country_id, description) VALUES (?, ?, ?, ?, ?, ?)'
with params [1, "A City Name", null, 1, null, null]
SQLSTATE[23502]: Not null violation
ERROR: null value in column "country_id" violates not-null constraint
(uncaught exception) at D:\server\htdocs\application\vendor\doctrine\dbal\lib\Doctrine\DBAL\DBALException.php line 47
I expect that value of column "country_id" will be provided automatically when inserting into table "geography_city", but based on the errors above it does not.
Any help I would really appreciate.
Thank you very much.
Best Regards.
country_id text NOT NULL,This foreign key should be an integer, not a string. – A.L