DDC-917: using generator strategy AUTO leads to duplicate squences drops (on PostgreSQL) when using inheritance #1138

New Issue

admin · 2026-01-22T13:03:28+01:00

admin commented

2026-01-22 13:03:28 +01:00

Originally created by @doctrinebot on GitHub (Dec 7, 2010).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user @lsmith77:

This is the base definition:
https://github.com/knplabs/DoctrineUserBundle/blob/symfony2Security/Resources/config/doctrine/metadata/orm/Bundle.DoctrineUserBundle.Entity.User.dcm.xml

It uses

       <id name="id" type="integer" column="id">
           <generator strategy="AUTO" />
       </id>

This class is inherited 2 times.
When generating the drop sql it gives the following (note the user_user_like and user_user_interest entities use generator strategy IDENTITY):

Soitgoes:foo lsmith$ app/main/console_dev doctrine:schema:drop --dump-sql
DROP SEQUENCE doctrine*user_user_id*seq;
DROP SEQUENCE doctrine*user_user_id*seq;
DROP SEQUENCE doctrine*user_user_id*seq;
DROP SEQUENCE doctrine*user_user_id*seq;
ALTER TABLE user*user_like DROP CONSTRAINT user_user_like_user_id*fkey;
ALTER TABLE user*user_like DROP CONSTRAINT user_user_like_user_like_id*fkey;
ALTER TABLE user*user_interest DROP CONSTRAINT user_user_interest_user_id*fkey;
ALTER TABLE user*user_interest DROP CONSTRAINT user_user_interest_user_interest_id*fkey;
DROP TABLE user*user*like;
DROP TABLE user*user*interest;
DROP TABLE doctrine*user*user;
DROP TABLE user_like;
DROP TABLE user_interest

Soitgoes:foo lsmith$ app/main/console_dev doctrine:schema:create --dump-sql
ATTENTION: This operation should not be executed in an production enviroment.

CREATE TABLE doctrine*user_user (id INT NOT NULL, username VARCHAR(255) NOT NULL, username_lower VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, is_active BOOLEAN NOT NULL, is_super_admin BOOLEAN NOT NULL, password VARCHAR(255) NOT NULL, algorithm VARCHAR(127) NOT NULL, salt VARCHAR(127) NOT NULL, created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, updated_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, last_login TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, confirmation_token VARCHAR(127) DEFAULT NULL, remember_me_token VARCHAR(127) DEFAULT NULL, firstname VARCHAR(255) NOT NULL, lastname VARCHAR(255) NOT NULL, profile_token VARCHAR(10) NOT NULL, has_accepted_terms BOOLEAN NOT NULL, facebookID VARCHAR(50) DEFAULT NULL, city VARCHAR(255) DEFAULT NULL, gender VARCHAR(1) DEFAULT NULL, age INT DEFAULT NULL, phone*number VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX doctrine*user_user_username_uniq ON doctrine_user*user (username);
CREATE UNIQUE INDEX doctrine*user_user_username_lower_uniq ON doctrine_user_user (username*lower);
CREATE UNIQUE INDEX doctrine*user_user_email_uniq ON doctrine_user*user (email);
CREATE UNIQUE INDEX doctrine*user_user_facebookID_uniq ON doctrine_user*user (facebookID);
CREATE TABLE user*user_like (user_id INT NOT NULL, user_like_id INT NULL, PRIMARY KEY(user_id, user_like*id));
CREATE INDEX user*user_like_user_like_id_idx ON user_user_like (user_like*id);
CREATE UNIQUE INDEX user*user_like_user_id_uniq ON user_user_like (user*id);
CREATE TABLE user*user_interest (user_id INT NOT NULL, user_interest_id INT NULL, PRIMARY KEY(user_id, user_interest*id));
CREATE INDEX user*user_interest_user_interest_id_idx ON user_user_interest (user_interest*id);
CREATE UNIQUE INDEX user*user_interest_user_id_uniq ON user_user_interest (user*id);
CREATE TABLE user_interest (id SERIAL NOT NULL, name VARCHAR(255) NOT NULL, PRIMARY KEY(id));
CREATE TABLE user_like (id SERIAL NOT NULL, name VARCHAR(255) NOT NULL, PRIMARY KEY(id));
CREATE SEQUENCE doctrine*user_user_id*seq INCREMENT BY 1 MINVALUE 1 START 1;
ALTER TABLE user*user_like ADD FOREIGN KEY (user_id) REFERENCES doctrine_user*user(id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE user*user_like ADD FOREIGN KEY (user_like_id) REFERENCES user*like(id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE user*user_interest ADD FOREIGN KEY (user_id) REFERENCES doctrine_user*user(id) NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE user*user_interest ADD FOREIGN KEY (user_interest_id) REFERENCES user*interest(id) NOT DEFERRABLE INITIALLY IMMEDIATE

Originally created by @doctrinebot on GitHub (Dec 7, 2010). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user @lsmith77: This is the base definition: https://github.com/knplabs/DoctrineUserBundle/blob/symfony2Security/Resources/config/doctrine/metadata/orm/Bundle.DoctrineUserBundle.Entity.User.dcm.xml It uses ``` <id name="id" type="integer" column="id"> <generator strategy="AUTO" /> </id> ``` This class is inherited 2 times. When generating the drop sql it gives the following (note the user_user_like and user_user_interest entities use generator strategy IDENTITY): ``` Soitgoes:foo lsmith$ app/main/console_dev doctrine:schema:drop --dump-sql DROP SEQUENCE doctrine*user_user_id*seq; DROP SEQUENCE doctrine*user_user_id*seq; DROP SEQUENCE doctrine*user_user_id*seq; DROP SEQUENCE doctrine*user_user_id*seq; ALTER TABLE user*user_like DROP CONSTRAINT user_user_like_user_id*fkey; ALTER TABLE user*user_like DROP CONSTRAINT user_user_like_user_like_id*fkey; ALTER TABLE user*user_interest DROP CONSTRAINT user_user_interest_user_id*fkey; ALTER TABLE user*user_interest DROP CONSTRAINT user_user_interest_user_interest_id*fkey; DROP TABLE user*user*like; DROP TABLE user*user*interest; DROP TABLE doctrine*user*user; DROP TABLE user_like; DROP TABLE user_interest ``` Soitgoes:foo lsmith$ app/main/console_dev doctrine:schema:create --dump-sql ATTENTION: This operation should not be executed in an production enviroment. ``` CREATE TABLE doctrine*user_user (id INT NOT NULL, username VARCHAR(255) NOT NULL, username_lower VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, is_active BOOLEAN NOT NULL, is_super_admin BOOLEAN NOT NULL, password VARCHAR(255) NOT NULL, algorithm VARCHAR(127) NOT NULL, salt VARCHAR(127) NOT NULL, created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, updated_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL, last_login TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, confirmation_token VARCHAR(127) DEFAULT NULL, remember_me_token VARCHAR(127) DEFAULT NULL, firstname VARCHAR(255) NOT NULL, lastname VARCHAR(255) NOT NULL, profile_token VARCHAR(10) NOT NULL, has_accepted_terms BOOLEAN NOT NULL, facebookID VARCHAR(50) DEFAULT NULL, city VARCHAR(255) DEFAULT NULL, gender VARCHAR(1) DEFAULT NULL, age INT DEFAULT NULL, phone*number VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id)); CREATE UNIQUE INDEX doctrine*user_user_username_uniq ON doctrine_user*user (username); CREATE UNIQUE INDEX doctrine*user_user_username_lower_uniq ON doctrine_user_user (username*lower); CREATE UNIQUE INDEX doctrine*user_user_email_uniq ON doctrine_user*user (email); CREATE UNIQUE INDEX doctrine*user_user_facebookID_uniq ON doctrine_user*user (facebookID); CREATE TABLE user*user_like (user_id INT NOT NULL, user_like_id INT NULL, PRIMARY KEY(user_id, user_like*id)); CREATE INDEX user*user_like_user_like_id_idx ON user_user_like (user_like*id); CREATE UNIQUE INDEX user*user_like_user_id_uniq ON user_user_like (user*id); CREATE TABLE user*user_interest (user_id INT NOT NULL, user_interest_id INT NULL, PRIMARY KEY(user_id, user_interest*id)); CREATE INDEX user*user_interest_user_interest_id_idx ON user_user_interest (user_interest*id); CREATE UNIQUE INDEX user*user_interest_user_id_uniq ON user_user_interest (user*id); CREATE TABLE user_interest (id SERIAL NOT NULL, name VARCHAR(255) NOT NULL, PRIMARY KEY(id)); CREATE TABLE user_like (id SERIAL NOT NULL, name VARCHAR(255) NOT NULL, PRIMARY KEY(id)); CREATE SEQUENCE doctrine*user_user_id*seq INCREMENT BY 1 MINVALUE 1 START 1; ALTER TABLE user*user_like ADD FOREIGN KEY (user_id) REFERENCES doctrine_user*user(id) NOT DEFERRABLE INITIALLY IMMEDIATE; ALTER TABLE user*user_like ADD FOREIGN KEY (user_like_id) REFERENCES user*like(id) NOT DEFERRABLE INITIALLY IMMEDIATE; ALTER TABLE user*user_interest ADD FOREIGN KEY (user_id) REFERENCES doctrine_user*user(id) NOT DEFERRABLE INITIALLY IMMEDIATE; ALTER TABLE user*user_interest ADD FOREIGN KEY (user_interest_id) REFERENCES user*interest(id) NOT DEFERRABLE INITIALLY IMMEDIATE ```

admin added the Bug label 2026-01-22 13:03:28 +01:00

admin closed this issue

2026-01-22 13:03:28 +01:00

admin commented

2026-01-22 13:03:30 +01:00

@doctrinebot commented on GitHub (Dec 8, 2010):

Comment created by @beberlei:

Fixed

@doctrinebot commented on GitHub (Dec 8, 2010): Comment created by @beberlei: Fixed

admin commented

2026-01-22 13:03:30 +01:00

@doctrinebot commented on GitHub (Dec 8, 2010):