Columns with primary_key=True and a ForeignKey should have autoincrement=False by default

[Former user]

(original reporter: holm) When creating columns that are both integer, part of the primary key and a foreign key, should have autoincrement set to False by default. Currently it is easy to forget to set autoincrement=False, which causes the column to have an auto incrementer set. This rarely (ever?) makes sense for columns that have a foreign key, so the default could be changed.

Having the autoincrement set without knowing, could lead to very subtle bugs, where the value could not be set, and instead an auto-generated value used.

[Mike Bayer]

this already is the behavior of autoincrement. Here's a test:

from sqlalchemy import *

m = MetaData()

a = Table('a', m,
    Column('id', Integer, primary_key=True)
)

b = Table('b', m,
    Column('id', Integer, ForeignKey('a.id'), primary_key=True)
)


for url in (
        "sqlite://",
        "postgresql://scott:tiger@localhost/test",
        "mysql://scott:tiger@localhost/test"):
    e = create_engine(url, echo=True)
    m.create_all(e)
    m.drop_all(e)

Here's the output of the actual CREATE statements when run on 0.7:

-- sqlite

CREATE TABLE a (
    id INTEGER NOT NULL, 
    PRIMARY KEY (id)
)


CREATE TABLE b (
    id INTEGER NOT NULL, 
    PRIMARY KEY (id), 
    FOREIGN KEY(id) REFERENCES a (id)
)


-- postgresql

CREATE TABLE a (
    id SERIAL NOT NULL, 
    PRIMARY KEY (id)
)

CREATE TABLE b (
    id INTEGER NOT NULL, 
    PRIMARY KEY (id), 
    FOREIGN KEY(id) REFERENCES a (id)
)

-- mysql

CREATE TABLE a (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY (id)
)

CREATE TABLE b (
    id INTEGER NOT NULL, 
    PRIMARY KEY (id), 
    FOREIGN KEY(id) REFERENCES a (id)
)

as you can see, on postgresql only "a" gets SERIAL, and on MySQL only "a" gets "AUTO_INCREMENT". "b.id" is not considered to be an autoincrement column due to the foreign key.

The docs (http://www.sqlalchemy.org/docs/core/schema.html#sqlalchemy.schema.Column) try very hard to make this clear:

The setting only has an effect for columns which are:

- Integer derived (i.e. INT, SMALLINT, BIGINT).
- Part of the primary key
- **Are not referenced by any foreign keys, unless the value is specified as 'ignore_fk**'

If you can clarify if you're seeing something different that would be great else I'll soon resolve this as "worksforme".

[Former user]

(original author: holm) Hi Mike

Terribly sorry about this. I seem to have gotten confused about some error messages we where seing for our CI builds.

It does seem to work as you described (and as documented). Sorry to waste your time.

Christian

[Mike Bayer]

hey no problem at all, glad it's working out for you.

mike

[Mike Bayer]

Removing milestone: 0.8.0final (automated comment)