Flask-Sqlalchemy: Table with 3 primary keys that are also foreign keys

Question

I am trying to translate a table from pure SQL to Flask-Sqlalchemy, but the documentation available is not clear about how to do this specific scenario - Primary keys that are also foreign keys.

The SQL to build the table is the following and it works fine:

CREATE TABLE IF NOT EXISTS `ws`.`Perfil_Plano_Transacao` (
    `pptr_perf_id` INT NOT NULL,
    `pptr_tran_id` INT NOT NULL,
    `pptr_plan_id` INT NOT NULL,
    `pptr_dt_incluscao` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `pptr_dt_atualizacao` TIMESTAMP NULL,
PRIMARY KEY (`pptr_perf_id`, `pptr_tran_id`, `pptr_plan_id`),
INDEX `fk_Perfil_Plano_Transacao_Transacao1_idx` (`pptr_tran_id` ASC),
INDEX `fk_Perfil_Plano_Transacao_Plano1_idx` (`pptr_plan_id` ASC),
CONSTRAINT `fk_Perfil_Plano_Transacao_Perfil1`
    FOREIGN KEY (`pptr_perf_id`)
    REFERENCES `ws`.`Perfil` (`perf_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
CONSTRAINT `fk_Perfil_Plano_Transacao_Transacao1`
    FOREIGN KEY (`pptr_tran_id`)
    REFERENCES `ws`.`Transacao` (`tran_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
CONSTRAINT `fk_Perfil_Plano_Transacao_Plano1`
    FOREIGN KEY (`pptr_plan_id`)
    REFERENCES `ws`.`Plano` (`plan_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

And the Python code I've come until here is:

class PerfilPlanoTransacaoModel(db.Model):

    __tablename__ = 'perfil_plano_transacao'

    pptr_perf_id = db.Column(db.Integer, primary_key=True, autoincrement=False)
    pptr_plan_id = db.Column(db.Integer, primary_key=True, autoincrement=False)
    pptr_tran_id = db.Column(db.Integer, primary_key=True, autoincrement=False)
    pptr_dt_inclusao = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    pptr_dt_atualizacao = db.Column(db.DateTime, nullable=True)

    __table_args__ = (
        db.ForeignKeyConstraint(
            ['pptr_perf_id', 'pptr_plan_id', 'pptr_tran_id'],
            ['perfil.perf_id', 'plano.plan_id', 'transacao.tran_id'],
            ['fk_Perfil_Plano_Transacao_Perfil1', 'fk_Perfil_Plano_Transacao_Plano1', 'fk_Perfil_Plano_Transacao_Transacao1']
        ),
    )

I would like to know if I am going toward the right way. I dind't find, for example, how to declare the name of the foreign key constraint and how to set the INDEX. Is there a more Flaks-Sqlalchemy way to do all this?

Answer 1

The answer by @Halvor is right, but I'll just add that you have a composite primary key but not a composite foreign key, you have three single column foreign keys pointing to different tables which means you can declare the foreign key in your column definition too:

from sqlalchemy import ForeignKey

class PerfilPlanoTransacaoModel(db.Model):

    __tablename__ = 'perfil_plano_transacao'

    pptr_perf_id = db.Column(
        db.Integer, 
        ForeignKey('perfil.perf_id'),
        primary_key=True, 
        autoincrement=False,
    )
    pptr_plan_id = db.Column(
        db.Integer, 
        ForeignKey('plano.plan_id'),
        primary_key=True, 
        autoincrement=False,
    )
    pptr_tran_id = db.Column(
        db.Integer, 
        ForeignKey('transacao.tran_id'),
        primary_key=True, 
        autoincrement=False,
    )
    pptr_dt_inclusao = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    pptr_dt_atualizacao = db.Column(db.DateTime, nullable=True)

Using ForeignKeyConstraint is a little bit more verbose than using ForeignKey and the ForeignKey objects we create in your column definitions in this example are eventually converted to ForeignKeyConstraint objects anyway, its just easier to use the ForeignKey object when you are dealing with single column keys. The ForeignKeyConstraint object defined in __table_args__ is usually only used directly when you need to create a composite foreign key, for example if you had another table that wanted to reference perfil_plano_transacao, it would need to be a composite foreign key and you'd have to define it as you have done above.

I'll defer to @Halvor's answer for the rest of your question.

Answer 2

How to declare the name of the foreign key constraint

Adding multiple foreign key constraints can be done by having multiple ForeignKeyConstraints in your __table_args__. For example:

__table_args__ = (
    ForeignKeyConstraint(['pptr_perf_id'], ['perfil.perf_id'], name='fk_Perfil_Plano_Transacao_Perfil1'),
    ForeignKeyConstraint(['pptr_plan_id'], ['plano.plan_id'], name='fk_Perfil_Plano_Transacao_Plano1'),
    ForeignKeyConstraint(['pptr_tran_id'], ['transacao.tran_id'], name='fk_Perfil_Plano_Transacao_Transacao1'),
)

Here you see that your define your local column, then the column in the original table and give it a name. The reason the first two parameters are arrays are to allow for composite foreign keys.

Making this change to your code should evaluate to the following query:

CREATE TABLE perfil_plano_transacao (
        pptr_perf_id INTEGER NOT NULL,
        pptr_plan_id INTEGER NOT NULL,
        pptr_tran_id INTEGER NOT NULL,
        pptr_dt_inclusao DATETIME NOT NULL,
        pptr_dt_atualizacao DATETIME,
        PRIMARY KEY (pptr_perf_id, pptr_plan_id, pptr_tran_id),
        CONSTRAINT "fk_Perfil_Plano_Transacao_Perfil1" FOREIGN KEY(pptr_perf_id) REFERENCES perfil (perf_id),
        CONSTRAINT "fk_Perfil_Plano_Transacao_Plano1" FOREIGN KEY(pptr_plan_id) REFERENCES plano (plan_id),
        CONSTRAINT "fk_Perfil_Plano_Transacao_Transacao1" FOREIGN KEY(pptr_tran_id) REFERENCES transacao (tran_id)
)

How to set the INDEX

The simple way of adding an index is setting it on the column declaration:

pptr_perf_id = Column(Integer, primary_key=True, autoincrement=False)
pptr_plan_id = Column(Integer, primary_key=True, autoincrement=False, index=True)
pptr_tran_id = Column(Integer, primary_key=True, autoincrement=False, index=True)

Which would lead to the following two queries:

CREATE INDEX ix_perfil_plano_transacao_pptr_plan_id ON perfil_plano_transacao (pptr_plan_id)
CREATE INDEX ix_perfil_plano_transacao_pptr_tran_id ON perfil_plano_transacao (pptr_tran_id)

Or you can add it separately after the table declaration:

from sqlalchemy import Index
Index('fk_Perfil_Plano_Transacao_Transacao1_idx', PerfilPlanoTransacaoModel.pptr_tran_id.asc())
Index('fk_Perfil_Plano_Transacao_Plano1_idx', PerfilPlanoTransacaoModel.pptr_plan_id.asc())

Which would lead to the following two queries:

CREATE INDEX "fk_Perfil_Plano_Transacao_Transacao1_idx" ON perfil_plano_transacao (pptr_tran_id ASC)
CREATE INDEX "fk_Perfil_Plano_Transacao_Plano1_idx" ON perfil_plano_transacao (pptr_plan_id ASC)