DBAL-1170: self-referential column fails with sqlite and InheritanceType("JOINED") #4438

New Issue

admin · 2026-01-22T14:41:39+01:00

admin commented

2026-01-22 14:41:39 +01:00

Originally created by @doctrinebot on GitHub (Mar 11, 2015).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user jwarnica:

Per summary, if I have a base abstract class with a property referencing its own ID, and InheritanceType("JOINED"), the generated sqlite DDL will cause runtime constraint errors.

trimmed down example of what I'm doing, seeing

/****
 * @Entity
 * @InheritanceType("JOINED")
 * @DiscriminatorColumn(name="discr", type="string")
 */

abstract class GraphElement  {
    /****
     * @Id 
     * @Column(type="integer")
     * @GeneratedValue(strategy="AUTO")
     * @var int
     ** **/
    public $id;

    /****
     * @OneToOne(targetEntity="GraphElement")
     * @JoinColumn(name="owningProcessDefinition_id", referencedColumnName="id")
     * @var ProcessDefinition
     */
    protected $processDefinition = null;
......
}
/*** @Entity ***/
class ProcessDefinition extends GraphElement {}

Generates:
CREATE TABLE GraphElement (id INTEGER NOT NULL, name VARCHAR(255) NOT NULL, owningProcessDefinition_id INTEGER DEFAULT NULL, discr VARCHAR(255) NOT NULL, PRIMARY KEY(id), CONSTRAINT FK_4779DC6C1693DAAD FOREIGN KEY (owningProcessDefinition_id) REFERENCES GraphElement (id) NOT DEFERRABLE INITIALLY IMMEDIATE)

and then:

2015-03-11T15:46:1500:00 [sql] "START TRANSACTION"
2015-03-11T15:46:1500:00 [sql] INSERT INTO GraphElement (name, owningProcessDefinition_id, discr) VALUES (?, ?, ?)
2015-03-11T15:46:1500:00 [sql] INSERT INTO ProcessDefinition (id, startState_id) VALUES (?, ?)
.....
2015-03-11T15:46:1500:00 [sql] INSERT INTO GraphElement (name, owningProcessDefinition_id, discr) VALUES (?, ?, ?)
....
2015-03-11T15:46:1500:00 [sql] UPDATE GraphElement SET owningProcessDefinition_id = ? WHERE id = ?
2015-03-11T15:46:1500:00 [sql] "ROLLBACK"

will ultimately give:

Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing 'UPDATE GraphElement SET owningProcessDefinition_id = ? WHERE id = ?' with params [1, 1]:
SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: GraphElement.owningProcessDefinition_id' in /home/jwarnica/workspace/azBPM/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractSQLiteDriver.php:48

because of that "NOT DEFERRABLE INITIALLY IMMEDIATE"

Note: Everything just works with @InheritanceType("SINGLE_TABLE")

It does look like SqlitePlatform.php has the ability to not create this constraint, but I wasn't able to find any documentation on how (or if possible) to apply (loosen) FK constraints.

In any case, if SINGLE_TABLE just works, JOINED should trigger the right (no) constraints on self-referential OneToOne. Or at least have what one needs to do documented.

Originally created by @doctrinebot on GitHub (Mar 11, 2015). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user jwarnica: Per summary, if I have a base abstract class with a property referencing its own ID, and InheritanceType("JOINED"), the generated sqlite DDL will cause runtime constraint errors. trimmed down example of what I'm doing, seeing ``` /**** * @Entity * @InheritanceType("JOINED") * @DiscriminatorColumn(name="discr", type="string") */ abstract class GraphElement { /**** * @Id * @Column(type="integer") * @GeneratedValue(strategy="AUTO") * @var int ** **/ public $id; /**** * @OneToOne(targetEntity="GraphElement") * @JoinColumn(name="owningProcessDefinition_id", referencedColumnName="id") * @var ProcessDefinition */ protected $processDefinition = null; ...... } /*** @Entity ***/ class ProcessDefinition extends GraphElement {} ``` Generates: CREATE TABLE GraphElement (id INTEGER NOT NULL, name VARCHAR(255) NOT NULL, owningProcessDefinition_id INTEGER DEFAULT NULL, discr VARCHAR(255) NOT NULL, PRIMARY KEY(id), CONSTRAINT FK_4779DC6C1693DAAD FOREIGN KEY (owningProcessDefinition_id) REFERENCES GraphElement (id) NOT DEFERRABLE INITIALLY IMMEDIATE) and then: 2015-03-11T15:46:15<ins>00:00 [sql] "START TRANSACTION" 2015-03-11T15:46:15</ins>00:00 [sql] INSERT INTO GraphElement (name, owningProcessDefinition_id, discr) VALUES (?, ?, ?) 2015-03-11T15:46:15<ins>00:00 [sql] INSERT INTO ProcessDefinition (id, startState_id) VALUES (?, ?) ..... 2015-03-11T15:46:15</ins>00:00 [sql] INSERT INTO GraphElement (name, owningProcessDefinition_id, discr) VALUES (?, ?, ?) .... 2015-03-11T15:46:15<ins>00:00 [sql] UPDATE GraphElement SET owningProcessDefinition_id = ? WHERE id = ? 2015-03-11T15:46:15</ins>00:00 [sql] "ROLLBACK" will ultimately give: Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing 'UPDATE GraphElement SET owningProcessDefinition_id = ? WHERE id = ?' with params [1, 1]: SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: GraphElement.owningProcessDefinition_id' in /home/jwarnica/workspace/azBPM/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractSQLiteDriver.php:48 because of that "NOT DEFERRABLE INITIALLY IMMEDIATE" Note: Everything just works with @InheritanceType("SINGLE_TABLE") It does look like SqlitePlatform.php has the ability to **not** create this constraint, but I wasn't able to find any documentation on how (or if possible) to apply (loosen) FK constraints. In any case, if SINGLE_TABLE just works, JOINED should trigger the right (no) constraints on self-referential OneToOne. Or at least have what one needs to do documented.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#4438