many to many relation with foreign key to composite primary key

[patrick payet]

I had a SQLAlchemy model like -

<code>

class User(DeclarativeBase):

    __tablename__ = 'users'

    id = Column(BigInteger, primary_key=True)

    email = Column(String(100), unique=True, nullable=False)

    name = Column(String(100), nullable=False)

    hashed_password = Column(String(100), nullable=False)

    is_admin = Column(BOOLEAN, default=False)

    is_active = Column(BOOLEAN, default=True)

    created = Column(DateTime, default=datetime.now)

    modified = Column(DateTime, default=datetime.now, onpudate=datetime.datetime.now)

    roles = relationship('Role', secondary=users_roles, back_populates='users', cascade="all, delete-orphan")

    permissions = relationship('Permission', secondary=users_permissions, back_populates='users',

                               cascade="all, delete-orphan")

    def __repr__(self):

        return "<User(name='%s', email='%s', hashed_password='%s')>" % (self.name, self.email, self.hashed_password)

class Role(DeclarativeBase):

    __tablename__ = 'roles'

    id = Column(BigInteger, primary_key=True)

    name = Column(String(100), unique=True, nullable=False)

    users = relationship('User', secondary=users_roles, back_populates='roles',cascade="all, delete-orphan")

    permissions = relationship('Permission', secondary=roles_permissions, back_populates='roles',

                               cascade="all, delete-orphan")

    def __repr__(self):

        return "<Role(name='%s')>" % self.name

class Permission(DeclarativeBase):

    __tablename__ = 'permissions'

    id = Column(BigInteger, primary_key=True)

    name = Column(String(100), unique=True, nullable=False)

    description = Column(String(255))

    users = relationship('User', secondary=users_permissions, back_populates='permissions',

                         cascade="all, delete-orphan")

    roles = relationship('Role', secondary=roles_permissions, back_populates='permissions',

                         cascade="all, delete-orphan")

    def __repr__(self):

        return "<Permission(name='%s', description='%s')>" % (self.name, self.description)

</code>

However, Alembic is not generating the correct upgrade, i try to make it

<code>

def upgrade():

    # ### commands auto generated by Alembic - please adjust! ###

    # op.drop_table('users')

    # ### end Alembic commands ###

    op.create_table('roles',

                    sa.Column('id', sa.BIGINT(), autoincrement=True, nullable=False),

                    sa.Column('name', sa.String(100), autoincrement=False, nullable=False),

                    sa.Column('users', BigInteger),

                    sa.Column('permissions', BigInteger),

                    sa.PrimaryKeyConstraint('id'),

                    sa.UniqueConstraint('name', name='roles_name_key')

                    # sa.ForeignKeyConstraint(['users'], ['users_roles.users_id'], ondelete='CASCADE'),

                    # sa.ForeignKeyConstraint(['permissions'], ['roles_permissions.permissions_id'], ondelete='CASCADE')

                    )

    op.create_table('permissions',

                    sa.Column('id', sa.BIGINT(), autoincrement=True, nullable=False),

                    sa.Column('name', sa.String(100), autoincrement=False, nullable=False),

                    sa.Column('description', sa.String(255)),

                    sa.Column('users', BigInteger),

                    sa.Column('roles', BigInteger),

                    sa.PrimaryKeyConstraint('id')

                    # sa.ForeignKeyConstraint(['users'], ['users_permissions.users_id'], ondelete='CASCADE'),

                    # sa.ForeignKeyConstraint(['roles'], ['role_permissions.roles_id'], ondelete='CASCADE')

                    )

    op.create_table('users_roles',

                    sa.Column('users_id', BigInteger, sa.ForeignKey('users.id'), primary_key=True),

                    sa.Column('roles_id', BigInteger, sa.ForeignKey('roles.id'), primary_key=True)

                    )

    op.create_table('users_permissions',

                    sa.Column('users_id', BigInteger, sa.ForeignKey('users.id'), primary_key=True),

                    sa.Column('permissions_id', BigInteger, sa.ForeignKey('permissions.id'), primary_key=True)

                    )

    op.create_table('roles_permissions',

                    sa.Column('roles_id', BigInteger, sa.ForeignKey('roles.id'), primary_key=True),

                    sa.Column('permissions_id', BigInteger, sa.ForeignKey('permissions.id'), primary_key=True)

                    )

    op.drop_constraint('users_name_key', 'users')

    op.add_column('users', sa.Column('is_admin', sa.BOOLEAN, autoincrement=False, nullable=False, default=False))

    op.add_column('users', sa.Column('is_active', sa.BOOLEAN, autoincrement=False, nullable=False, default=True))

    op.add_column('users', sa.Column('created', sa.DateTime, autoincrement=False, nullable=False,

                                     default=datetime.datetime.now))

    op.add_column('users', sa.Column('modified', sa.DateTime, autoincrement=False, nullable=False,

                                     default=datetime.datetime.now))

    op.add_column('users', sa.Column('roles', BigInteger))

    op.add_column('users', sa.Column('permissions', BigInteger))

    op.create_foreign_key(constraint_name="users_roles_fk", source_table="users", referent_table="users_roles",

                          local_cols=["roles"], remote_cols=["roles_id"], ondelete='CASCADE')

    op.create_foreign_key("users_permissions_fk", "users", "users_permissions", ["permissions"], ["permissions_id"],

                          ondelete='CASCADE')

    op.create_foreign_key("permissions_users_fk", "permissions", "users_permissions", ["users"], ["users_id"],

                          ondelete='CASCADE')

    op.create_foreign_key("permissions_roles_fk", "permissions", "roles_permissions", ["roles"], ["roles_id"],

                          ondelete='CASCADE')

    op.create_foreign_key("roles_users_fk", "roles", "users_roles", ["users"], ["users_id"],

                          ondelete='CASCADE')

    op.create_foreign_key("roles_permissions_fk", "roles", "roles_permissions", ["permissions"], ["permissions_id"],

                          ondelete='CASCADE')

</code>

i have an error, primary key for users_roles is a composite keys and i dont know how declare it, how make that

<code>

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) there is no unique constraint matching given keys for referenced table "users_roles"

 [SQL: 'ALTER TABLE users ADD CONSTRAINT users_roles_fk FOREIGN KEY(roles) REFERENCES users_roles (roles_id) ON DELETE CASCADE'] (Background on this error at: http://sqlalche.me/e/f405)

</code>

[Mike Bayer]

it's not clear here if you're saying alembic is adding foreign keys
where they dont belong during autogenerate, or if you dont know why
you are getting the error you are getting. The error is because you
don't need FOREIGN KEY constraints on roles, permissions, or users -
the FOREIGN KEY is only on the many-to-many tables, in this case
users_roles, users_permissions, roles_permissions.

otherwise if you can illustrate the exact CREATE TABLE statements
you'd like to see, I can illustrate a correct model for that which
Alembic will generate.

> <code>
> sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) there is no unique constraint matching given keys for referenced table "users_roles"
> [SQL: 'ALTER TABLE users ADD CONSTRAINT users_roles_fk FOREIGN KEY(roles) REFERENCES users_roles (roles_id) ON DELETE CASCADE'] (Background on this error at: http://sqlalche.me/e/f405)
> </code>
>

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[patrick payet]

Hi Mike,

Thanks for your answer.

My problem it’s a classical problem for application development, I want to make authentication and authorisation for a web application.

I want to understand why Alembic can’t create the model in autogeneration mode and how modify this model to resolve this problem.

With this model I have a join table to provide a many-to-many relationship between users and roles (users group) for example.

This allows me to access all roles for one user. For this, I declare a variable roles in users class and I want to make a foreign key with this join table

to reach the roles.

This joins table has a composite primary key (users.id foreign key and roles.id foreign key)  and I don’t know how to make the link (foreign key) with alembic for the  users.roles and this composite primary key. When I declare just one element of this primary key, I have the error quoted previously ( there is no unique constraint matching given keys for referenced table "users_roles").

I will send you the CREATE TABLE statements by email in a moment.

Best regards,

Patrick

[Mike Bayer]

On Thu, Dec 13, 2018 at 7:11 AM patrick payet <pap...@gmail.com> wrote:
>
> Hi Mike,
> Thanks for your answer.
> My problem it’s a classical problem for application development, I want to make authentication and authorisation for a web application.
> I want to understand why Alembic can’t create the model in autogeneration mode and how modify this model to resolve this problem.
> With this model I have a join table to provide a many-to-many relationship between users and roles (users group) for example.
> This allows me to access all roles for one user. For this, I declare a variable roles in users class and I want to make a foreign key with this join table
> to reach the roles.
> This joins table has a composite primary key (users.id foreign key and roles.id foreign key) and I don’t know how to make the link (foreign key) with alembic for the users.roles and this composite primary key.

the link is the user_roles table and the FOREIGN KEY constraints it
has between users and roles tables. you can't locate a unique row
in user_roles given only the user table because it has no column that
refers to user_roles.roles_id. at least I'm trying to parse your
words as given. however CREATE TABLE statements will show exactly
what you mean in case it is something different that just happens to
sound the same when described.

When I declare just one element of this primary key, I have the error
quoted previously ( there is no unique constraint matching given keys
for referenced table "users_roles").
> I will send you the CREATE TABLE statements by email in a moment.

yup

[patrick payet]

        PRIMARY KEY (id), 

        UNIQUE (email)

)

CREATE TABLE permissions (

        id BIGSERIAL NOT NULL, 

        name VARCHAR(100) NOT NULL, 

        description VARCHAR(255), 

        PRIMARY KEY (id), 

        UNIQUE (name)

)

CREATE TABLE roles (

        id BIGSERIAL NOT NULL, 

        name VARCHAR(100) NOT NULL, 

        PRIMARY KEY (id), 

        UNIQUE (name)

)

CREATE TABLE users_roles (

        users_id BIGINT NOT NULL, 

        roles_id BIGINT NOT NULL, 

        PRIMARY KEY (users_id, roles_id), 

        FOREIGN KEY(users_id) REFERENCES users (id), 

        FOREIGN KEY(roles_id) REFERENCES roles (id)

)

CREATE TABLE roles_permissions (

        roles_id BIGINT NOT NULL, 

        permissions_id BIGINT NOT NULL, 

        PRIMARY KEY (roles_id, permissions_id), 

        FOREIGN KEY(roles_id) REFERENCES roles (id), 

        FOREIGN KEY(permissions_id) REFERENCES permissions (id)

)

CREATE TABLE users_permissions (

        users_id BIGINT NOT NULL, 

        permissions_id BIGINT NOT NULL, 

        PRIMARY KEY (users_id, permissions_id), 

        FOREIGN KEY(users_id) REFERENCES users (id), 

        FOREIGN KEY(permissions_id) REFERENCES permissions (id)

)

My mistake is to have mixed in my mind the information of the object mapping and the relational constraints of the database.

But why Alembic can’t create this model in auto-generation mode?

Best regards

[patrick payet]

Hi Mike, 

The message has been truncated. 

The CREATE TABLE statements is :

CREATE TABLE users (

        id BIGSERIAL NOT NULL, 

        email VARCHAR(100) NOT NULL, 

        name VARCHAR(100) NOT NULL, 

        hashed_password VARCHAR(100) NOT NULL, 

        is_admin BOOLEAN, 

        is_active BOOLEAN, 

        created TIMESTAMP WITHOUT TIME ZONE, 

        modified TIMESTAMP WITHOUT TIME ZONE, 

[Mike Bayer]

I can't reproduce any problem.

Filling out your mappings from your first email, these look like:

from sqlalchemy import *
from sqlalchemy.orm import relationship
from datetime import datetime

from sqlalchemy.ext.declarative import declarative_base as db
target_metadata = MetaData()

DeclarativeBase = db(metadata=target_metadata)

users_roles = Table(
'users_roles', target_metadata,
Column('users_id', ForeignKey('users.id'), primary_key=True),
Column('roles_id', ForeignKey('roles.id'), primary_key=True),
)

roles_permissions = Table(
'roles_permissions', target_metadata,
Column('permissions_id', ForeignKey('permissions.id'), primary_key=True),
Column('roles_id', ForeignKey('roles.id'), primary_key=True),
)
users_permissions = Table(
'users_permissions', target_metadata,
Column('users_id', ForeignKey('users.id'), primary_key=True),
Column('permissions_id', ForeignKey('permissions.id'), primary_key=True),
)

class User(DeclarativeBase):
__tablename__ = 'users'

id = Column(BigInteger, primary_key=True)
email = Column(String(100), unique=True, nullable=False)
name = Column(String(100), nullable=False)
hashed_password = Column(String(100), nullable=False)
is_admin = Column(BOOLEAN, default=False)
is_active = Column(BOOLEAN, default=True)
created = Column(DateTime, default=datetime.now)

modified = Column(DateTime, default=datetime.now, onupdate=datetime.now)

then i run autogenerate, migrations are generated as:

def upgrade():
# ### commands auto generated by Alembic - please adjust! ###

op.create_table('permissions',
sa.Column('id', sa.BigInteger(), nullable=False),
sa.Column('name', sa.String(length=100), nullable=False),
sa.Column('description', sa.String(length=255), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
op.create_table('roles',
sa.Column('id', sa.BigInteger(), nullable=False),
sa.Column('name', sa.String(length=100), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
op.create_table('users',
sa.Column('id', sa.BigInteger(), nullable=False),
sa.Column('email', sa.String(length=100), nullable=False),
sa.Column('name', sa.String(length=100), nullable=False),
sa.Column('hashed_password', sa.String(length=100), nullable=False),
sa.Column('is_admin', sa.BOOLEAN(), nullable=True),
sa.Column('is_active', sa.BOOLEAN(), nullable=True),
sa.Column('created', sa.DateTime(), nullable=True),
sa.Column('modified', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('email')
)
op.create_table('roles_permissions',
sa.Column('permissions_id', sa.BigInteger(), nullable=False),
sa.Column('roles_id', sa.BigInteger(), nullable=False),
sa.ForeignKeyConstraint(['permissions_id'], ['permissions.id'], ),
sa.ForeignKeyConstraint(['roles_id'], ['roles.id'], ),
sa.PrimaryKeyConstraint('permissions_id', 'roles_id')
)
op.create_table('users_permissions',
sa.Column('users_id', sa.BigInteger(), nullable=False),
sa.Column('permissions_id', sa.BigInteger(), nullable=False),
sa.ForeignKeyConstraint(['permissions_id'], ['permissions.id'], ),
sa.ForeignKeyConstraint(['users_id'], ['users.id'], ),
sa.PrimaryKeyConstraint('users_id', 'permissions_id')
)
op.create_table('users_roles',
sa.Column('users_id', sa.BigInteger(), nullable=False),
sa.Column('roles_id', sa.BigInteger(), nullable=False),
sa.ForeignKeyConstraint(['roles_id'], ['roles.id'], ),
sa.ForeignKeyConstraint(['users_id'], ['users.id'], ),
sa.PrimaryKeyConstraint('users_id', 'roles_id')
)


then I run upgrade, SQL script output matches what you specified:

INFO [alembic.runtime.migration] Running upgrade -> 678e61b9e311, rev1
INFO [sqlalchemy.engine.base.Engine]

CREATE TABLE permissions (
id BIGSERIAL NOT NULL,
name VARCHAR(100) NOT NULL,
description VARCHAR(255),
PRIMARY KEY (id),
UNIQUE (name)
)

INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]

CREATE TABLE roles (
id BIGSERIAL NOT NULL,
name VARCHAR(100) NOT NULL,
PRIMARY KEY (id),
UNIQUE (name)
)

INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]

CREATE TABLE users (
id BIGSERIAL NOT NULL,
email VARCHAR(100) NOT NULL,
name VARCHAR(100) NOT NULL,
hashed_password VARCHAR(100) NOT NULL,
is_admin BOOLEAN,
is_active BOOLEAN,
created TIMESTAMP WITHOUT TIME ZONE,
modified TIMESTAMP WITHOUT TIME ZONE,
PRIMARY KEY (id),
UNIQUE (email)
)

INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]
CREATE TABLE roles_permissions (
permissions_id BIGINT NOT NULL,
roles_id BIGINT NOT NULL,
PRIMARY KEY (permissions_id, roles_id),
FOREIGN KEY(permissions_id) REFERENCES permissions (id),

FOREIGN KEY(roles_id) REFERENCES roles (id)
)

INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]

CREATE TABLE users_permissions (
users_id BIGINT NOT NULL,
permissions_id BIGINT NOT NULL,
PRIMARY KEY (users_id, permissions_id),

FOREIGN KEY(permissions_id) REFERENCES permissions (id),
FOREIGN KEY(users_id) REFERENCES users (id)
)


INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine]

CREATE TABLE users_roles (
users_id BIGINT NOT NULL,
roles_id BIGINT NOT NULL,
PRIMARY KEY (users_id, roles_id),

FOREIGN KEY(roles_id) REFERENCES roles (id),

FOREIGN KEY(users_id) REFERENCES users (id)
)

[patrick payet]

Hi Mike,

Thank you for the time you gave me.

The other problem I had to do was that my models were distributed in different files. Alembic could not access their class and therefore could not auto-generate his file.

Best regards,