Unique Constraint in a Table over columns with multiple many to many relationships fails. How to fix ?

[Padam Sethia]

Hey Guys !

So as the title says it all , I'm try to do the above , but I'm getting SQL Syntax error when I'm using db.Index to create a unique index . I've tried all the solutions but nothing seems to work.

P.S : I'm using Flask-SQLalchemy with MySQL. 

Here's my table

class FinGoods(db.Model):
    id = db.Column(db.Integer , primary_key = True)
    product_category = db.relationship('FinCat' ,cascade="all,delete", secondary='cat_goods' ,         
                   backref='cat_goods' , lazy = 'joined')
    fabric_combo = db.relationship('FabComb' ,cascade="all,delete", secondary='comb_goods' , 
               backref='comb_goods' , lazy = 'joined')
    print_tech = db.relationship('PrintTech' ,cascade="all,delete", secondary='tech_goods' , 
             backref='print_goods' , lazy = 'joined')
    design = db.relationship('FinDes' ,cascade="all,delete", secondary='des_goods' , 
         backref='des_goods' , lazy = 'joined')
    uom = db.relationship('Uom' ,cascade="all,delete", secondary='uom_goods' , backref='uom_goods' , 
      lazy = 'joined')
    alt_name = db.Column(db.String(200))
    gen_name = db.Column(db.String(100))

    def get_gen_name(self):
        product_category = self.product_category
        fabric_combo = self.fabric_combo
        print_tech = self.print_tech
        design = self.design
        uom = self.uom
        display_name = "{} / {} / {} / {}".format(product_category[0].cat, fabric_combo[0].comb,         
    print_tech[0].tech, design[0].des)
        return display_name


db.Table('cat_goods',
    db.Column('cat_id' , db.Integer , db.ForeignKey('fin_cat.id' , ondelete='SET NULL' )),
    db.Column('goods_id' , db.Integer , db.ForeignKey('fin_goods.id' , ondelete='SET NULL'))
)

db.Table('comb_goods',
    db.Column('comb_id' , db.Integer , db.ForeignKey('fab_comb.id' , ondelete='SET NULL' )),
    db.Column('goods_id' , db.Integer , db.ForeignKey('fin_goods.id' , ondelete='SET NULL'))
)
db.Table('tech_goods',
    db.Column('tech_id' , db.Integer , db.ForeignKey('print_tech.id' , ondelete='SET NULL' )),
    db.Column('goods_id' , db.Integer , db.ForeignKey('fin_goods.id' , ondelete='SET NULL'))
)
db.Table('des_goods',
    db.Column('des_id' , db.Integer , db.ForeignKey('fin_des.id' , ondelete='SET NULL' )),
    db.Column('goods_id' , db.Integer , db.ForeignKey('fin_goods.id' , ondelete='SET NULL'))
)
db.Table('uom_goods',
    db.Column('uom_id' , db.Integer , db.ForeignKey('uom.id' , ondelete='SET NULL' )),
    db.Column('goods_id' , db.Integer , db.ForeignKey('fin_goods.id' , ondelete='SET NULL'))
)

Here's the code for the unique Index :

db.Index('fin_goods_unq', cat_goods.cat_id, fin_goods.fabric_combo,fin_goods.print_tech , fin_goods.design, fin_goods.uom, unique=True)

But it's throwing a SQL Syntax error , which I'm unable to wrap my head around.

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= raw_fab_id AND id = cat_id, id = raw_fab_id AND id = comb_id, id = raw_fab_id ' at line 1")
[SQL: CREATE UNIQUE INDEX raw_goods_unq ON raw_fab_main (id = raw_fab_id AND id = cat_id, id = raw_fab_id AND id = comb_id, id = raw_fab_id AND id = const_id, id = raw_fab_id AND id = proc_id, id = raw_fab_id AND id = width_id, id = raw_fab_id AND id = dye_id)]
(Background on this error at: http://sqlalche.me/e/f405)

Thanks!

[Mike Bayer]

you're using relationship attributes in your Index, which is not allowed.   a SQL INDEX is against database columns, so you'd need to create an Index that has only column attrfibutes inside of it.  But also, an INDEX is only against one table at a time, so it's not valid to have columns from "cat_goods" and "fin_goods" at the same time.

What you want to do is design your database schema from SQL, e.g. as a series of approximate CREATE TABLE and CREATE INDEX statements first.   Then model your ORM models around that. 

But it's throwing a SQL Syntax error , which I'm unable to wrap my head around.

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= raw_fab_id AND id = cat_id, id = raw_fab_id AND id = comb_id, id = raw_fab_id ' at line 1")
[SQL: CREATE UNIQUE INDEX raw_goods_unq ON raw_fab_main (id = raw_fab_id AND id = cat_id, id = raw_fab_id AND id = comb_id, id = raw_fab_id AND id = const_id, id = raw_fab_id AND id = proc_id, id = raw_fab_id AND id = width_id, id = raw_fab_id AND id = dye_id)]
(Background on this error at: http://sqlalche.me/e/f405)

Thanks!

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/28cf95ee-8cf8-48b5-a120-0ffcad64368d%40googlegroups.com.