drop_constraint not working for primary key sqlite3

[sqlalchemy-bot]

Migrated issue, originally created by Anthony Ryan (@anthonyryan1)

Tested on sqlite 3.15.2 with alembic git master.

Minimal test case

Prepare test table

def upgrade():
    op.create_table(
      'test',
      sa.Column('integer', sa.Integer, nullable=False, primary_key=True),
      sa.Column('string', sa.Text, nullable=False),
    )

Drop primary key

def upgrade():
    with op.batch_alter_table('test') as batch_op:
        batch_op.drop_constraint('integer', type_='primary')

Expected result

CREATE TABLE test (
        integer INTEGER NOT NULL, 
        string TEXT NOT NULL
);

Actual result

ValueError: No such constraint: 'integer' and schema stays the same:

CREATE TABLE test (
        integer INTEGER NOT NULL, 
        string TEXT NOT NULL, 
        PRIMARY KEY (integer)
);

[sqlalchemy-bot]

Changes by Anthony Ryan (@anthonyryan1):

• edited description

[sqlalchemy-bot]

Anthony Ryan (@anthonyryan1) wrote:

Just to elaborate on this issue, I also attempted the following without success:

op.drop_constraint('PRIMARY', 'integer', type_='primary')

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

there's a bug here however your usage is invalid. drop_constraint() accepts the name of the constraint, not the name of the first column that's part of that constraint. Works as follows:

def upgrade():
    op.create_table(
      'test',
      sa.Column('integer', sa.Integer, nullable=False),
      sa.Column('string', sa.Text, nullable=False),
      sa.PrimaryKeyConstraint("integer", name="my_pk")
    )

    with op.batch_alter_table('test') as batch_op:
        batch_op.drop_constraint('my_pk')

the primary key is still being regenerated here due to a different issue, fix working through at https://gerrit.sqlalchemy.org/262 .

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Ensure primary_key flag unset for PK batch drop

Fixed bug where doing batch_op.drop_constraint() against the
primary key constraint would fail to remove the "primary_key" flag
from the column, resulting in the constraint being recreated.

Change-Id: I20c04860b151ac86466337f0522018be06c6feec
Fixes: #402

→ a8b292e

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Joel Tio (@joeltio) wrote:

This still seems to be a problem in alembic 0.9.10, tested on SQLite 3.23.1.

Minimal Example:

def upgrade():
    op.create_table(
        "test_table",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("some_column", sa.String),
    )

    with op.batch_alter_table("test_table", schema=None) as batch_op:
        batch_op.drop_column("id")
        batch_op.add_column(sa.Column("id", sa.Integer))


def downgrade():
    op.drop_table("test_table")

When I .dump the table after executing alembic upgrade head, I get the following output:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "test_table" (
        some_column VARCHAR,
        id INTEGER NOT NULL,
        PRIMARY KEY (id)
);
COMMIT;

Notice that the primary key id is retained.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

please repost to a new bug

[sqlalchemy-bot]

Joel Tio (@joeltio) wrote:

Reposted to a new issue: https://bitbucket.org/zzzeek/alembic/issues/502/sqlite3-retains-primary-key-after-dropping