This Python3 answer using Flask and sqlalchemy is completely derivative, it just puts everything from above into a small self-contained working example for MySQL. I needed a uniqueness constraint on the table that implements a many-to-many relationship. Maybe you can run this to debug local environment problems, in my case they were purely between the keyboard and the chair :)
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import Column, ForeignKey, Integer, String, UniqueConstraint
from sqlalchemy.orm import relationship
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+mysqlconnector://user:pass@localhost/mydb'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
user_role = db.Table(
'user_role',
Column('uid', String(6), ForeignKey('user.uid')),
Column('role', String(32), ForeignKey('role.name')),
UniqueConstraint('uid', 'role', name='idx_uid_role'))
class UserModel(db.Model):
__tablename__ = 'user'
uid = Column(String(6), primary_key=True)
create_time = Column(Integer, nullable=False)
login_time = Column(Integer, nullable=True)
roles = relationship('RoleModel', secondary='user_role',
backref='user', lazy='joined')
class RoleModel(db.Model):
__tablename__ = 'role'
name = Column(String(32), primary_key=True)
description = Column(String(256), nullable=False)
db.create_all()
After you run this, check the indexes defined for the table like this:
mysql> show index from user_role;
And you should see:
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| user_role | 0 | idx_uid_role | 1 | uid | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| user_role | 0 | idx_uid_role | 2 | role | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
| user_role | 1 | role | 1 | role | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)
Create test data:
mysql> insert into user values ('abc123', 1, 2);
mysql> insert into role values ('role1', 'Description');
mysql> insert into user_role (uid, role) values ('abc123', 'role1');
Finally, test the uniqueness constraint by running that last insert a second time, you should see:
mysql> insert into user_role (uid, role) values ('abc123', 'role1');
ERROR 1062 (23000): Duplicate entry 'abc123-role1' for key 'user_role.idx_uid_role'
