Revision history [back]

In SQLAlchemy, only primary keys can be set to auto-increment using the autoincrement=True parameter while defining the column. Non-primary keys cannot be directly set to auto-increment.

However, there are several workarounds that can be used to achieve a similar result:

1. Use a database-level sequence: Create a database sequence that generates unique integer values and link the non-primary key column to the sequence using a trigger. SQLAlchemy provides a DDL object that can be used to execute raw SQL statements. Here's an example:

from sqlalchemy import DDL

# Create a sequence in the database
create_seq = DDL("""
    CREATE SEQUENCE my_seq START 1;
""")
db.session.execute(create_seq)

# Define the non-primary key column with a default value from the sequence
my_table = Table('my_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('my_column', Integer, server_default=text('nextval(\'my_seq\'::regclass)')),
)

1. Use a trigger function: Define a function that generates unique integer values and link the non-primary key column to the function using a trigger. This is similar to the database-level sequence approach, but the function provides more flexibility in generating the values. Here's an example:

# Define a trigger function in the database
create_function = DDL("""
    CREATE OR REPLACE FUNCTION my_func() RETURNS TRIGGER AS $$
        BEGIN
            NEW.my_column = (SELECT COALESCE(MAX(my_column), 0) + 1 FROM my_table);
            RETURN NEW;
        END;
    $$ LANGUAGE plpgsql;
""")
db.session.execute(create_function)

# Define the non-primary key column with a default value from the trigger function
my_table = Table('my_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('my_column', Integer),
)
# Create a trigger on insert that calls the trigger function
my_trigger = DDL("""
    CREATE TRIGGER my_trigger
      BEFORE INSERT ON my_table
      FOR EACH ROW
      EXECUTE FUNCTION my_func();
""")
db.session.execute(my_trigger)

Note that both of these approaches require executing raw SQL statements and may not be compatible with all database backends. Be sure to test thoroughly and consult the database documentation for details.