title: sqlalchemy many to many relationships (the value of a duplicate key breaks the unique constraint) flask_user role
body: <p>can you help me, </p>
  
  <p>when i run this script it works well</p>
  
  <pre><code>class User(db.Model, UserMixin):
      __tablename__ = 'users'
      id = db.Column(db.Integer, primary_key=True)
      active = db.Column('is_active', db.Boolean(), nullable=False, server_default='1')
  
      # User authentication information. The collation='NOCASE' is required
      # to search case insensitively when USER_IFIND_MODE is 'nocase_collation'.
      email = db.Column(db.String(255, collation='NOCASE'), nullable=False, unique=True)
      email_confirmed_at = db.Column(db.DateTime())
      password = db.Column(db.String(255), nullable=False, server_default='')
  
      # User information
      first_name = db.Column(db.String(100, collation='NOCASE'), nullable=False, server_default='')
      last_name = db.Column(db.String(100, collation='NOCASE'), nullable=False, server_default='')
  
      # Define the relationship to Role via UserRoles
      roles = db.relationship('Role', secondary='user_roles')
  
  # Define the Role data-model
  class Role(db.Model):
      __tablename__ = 'roles'
      id = db.Column(db.Integer(), primary_key=True)
      name = db.Column(db.String(50), unique=True)
  
  # Define the UserRoles association table
  class UserRoles(db.Model):
      __tablename__ = 'user_roles'
      id = db.Column(db.Integer(), primary_key=True)
      user_id = db.Column(db.Integer(), db.ForeignKey('users.id', ondelete='CASCADE'))
      role_id = db.Column(db.Integer(), db.ForeignKey('roles.id', ondelete='CASCADE'))
  
  # Setup Flask-User and specify the User data-model
  user_manager = UserManager(app, db, User)
  
  # Create all database tables
  db.create_all()
  
  # Create 'member@example.com' user with no roles
  if not User.query.filter(User.email == 'member@example.com').first():
      user = User(
          email='member@example.com',
          email_confirmed_at=datetime.datetime.utcnow(),
          password=user_manager.hash_password('Password1'),
      )
      db.session.add(user)
      db.session.commit()
  
  # Create 'admin@example.com' user with 'Admin' and 'Agent' roles
  if not User.query.filter(User.email == 'admin@example.com').first():
      user = User(
          email='admin@example.com',
          email_confirmed_at=datetime.datetime.utcnow(),
          password=user_manager.hash_password('Password1'),
      )
      user.roles.append(Role(name='Admin'))
      user.roles.append(Role(name='Agent'))
      db.session.add(user)
      db.session.commit()
  </code></pre>
  
  <p>at this stage the code is working without problems but the problem is just after when I add another user with a role that already exists
  in this case the Admin role or the Agent role
  for example if I add the code below an error message displays</p>
  
  <pre><code>    if not User.query.filter(User.email == 'admin2@example.com').first():
      user = User(
          email='admin2@example.com',
          email_confirmed_at=datetime.datetime.utcnow(),
          password=user_manager.hash_password('Password1'),
      )
      user.roles.append(Role(name='Admin'))
      user.roles.append(Role(name='Agent'))
      db.session.add(user)
      db.session.commit()
  </code></pre>
  
  <blockquote>
    <p>sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) ERREUR:  la valeur d'une clé dupliquée rompt la contrainte unique « roles_name_key »
    DETAIL:  La clé « (name)=(Admin) » existe déjà.
     [SQL: 'INSERT INTO roles (name) VALUES (%(name)s) RETURNING roles.id'] [parameters: {'name': 'Admin'}] (Background on this error at: <a href="http://sqlalche" rel="nofollow noreferrer">http://sqlalche</a>.
    me/e/gkpj)</p>
  </blockquote>
  
  <p>I tried db driver different (pg8000, pygresql, py-postgresql) and still the same problem,</p>
  
  <p>as the error message shows the problem because of the name value which is unique</p>
  
  <p>name = db.Column (db.String (50), unique = True)</p>
  
  <p>can you explain to me what is the error that I made, and what is the role of this part of code</p>
  
  <pre><code># Define the relationship to Role via UserRoles
  
  roles = db.relationship ('Role', secondary = 'user_roles')
  </code></pre>
  
  <p>I use flask with flask_user</p>