title: postgres/sqlalchemy - how to add unique constraint to existing column of table
body: <p>I've looked at several other answers but have yet to find one that shows the create statement &amp; the model class together, most just show how to define a column as unique when the table is created.</p>
  <p>I have a working line of code that writes rows to postgres &amp; the associated model class below</p>
  <pre><code>self.cur.execute(&quot;INSERT INTO autos(make,year,model,license) VALUES (%s,%s,%s,%s)&quot;,(item['make'],item['year'],item['model'],item['license']))
  
  class AutoItem(db.Model):
      __tablename__ = 'autos'
      id = db.Column(db.Integer, primary_key=True)
      make = db.Column(db.String(255))
      year = db.Column(db.String(255))
      model = db.Column(db.String(255))
      license = db.Column(db.String(255))
  </code></pre>
  <p>in order to prevent duplicate rows I added an <code>ON CONSTRAINT</code> statement to the line of code like so</p>
  <pre><code>self.cur.execute(&quot;INSERT INTO autos(make,year,model,license) VALUES (%s,%s,%s,%s) ON CONFLICT (license) DO NOTHING;&quot;,(item['make'],item['year'],item['model'],item['license']))
  </code></pre>
  <p>adding the <code>ON CONSTRAINT</code> statement generates the following error</p>
  <pre><code>psycopg2.errors.InvalidColumnReference: there is no unique or exclusion constraint matching the ON CONFLICT specification
  </code></pre>
  <p>so to update the model class such that the license column is unique what I have now is</p>
  <pre><code>class AutoItem(db.Model):
      __tablename__ = 'autos'
      id = db.Column(db.Integer, primary_key=True)
      make = db.Column(db.String(255))
      year = db.Column(db.String(255))
      model = db.Column(db.String(255))
      license = db.Column(db.String(255), unique=True) #THIS WORKS
  </code></pre>
  <p>and I added an import to the top of the model class (file name is models.py)</p>
  <pre><code>from sqlalchemy import UniqueConstraint #THIS WORKS
  </code></pre>
  <p>but the error <code>psycopg2.errors.InvalidColumnReference: there is no unique or exclusion constraint matching the ON CONFLICT specification</code> still remains? Previously I dropped the table &amp; created a new one but that generates the same error too. I don't know if its psycopg2, my model class and/or the <code>ON CONSTRAINT</code> statement that's the problem?</p>
  <p>EDIT adding screenshot below</p>
  <p><a href="https://i.stack.imgur.com/25THT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/25THT.png" alt="enter image description here" /></a></p>
  <p>EDIT 2 - ISSUE IS RESOLVED</p>
  <p>the working solution line of code and model class are above, see the update to the model class as well as the import at the top. I guess I made some mistake/s when creating the table. What worked for me was to completely drop the <code>autos</code> table &amp; create it again...</p>