Writing a composite Key in Flask SQL Alchemy

Question

I have my table like something below:

class Dummies(db.Model):

    __tablename__ = 'dummies'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(100), index=True, nullable=True)
    value = db.Column(db.String(100), index=True, nullable=True)
    sum_id = db.Column(db.Integer, db.ForeignKey('pattern.id',
                                                      onupdate='CASCADE',
                                                      ondelete='CASCADE'),
                                                      index=True, nullable=False)

I am quite new to flask and trying to get around the syntax for this relationship, I want to represent. I want to use the name, value and sum_id to uniquely represent a row. Could someone help me how to write the relationship for this ?

score 0 · Accepted Answer · answered Nov 25 '19 at 04:55

If you want enforce the unique constraint on multiple columns you can explicitly set it like this:


    __tablename__ = 'dummies'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=True)
    value = db.Column(db.String(100), nullable=True)
    sum_id = db.Column(db.Integer, db.ForeignKey('pattern.id'), nullable=False)                                                                                                      
    UniqueConstraint('name', 'value', 'sum_id'
                                 name='myMultipleColumnUniqueConstraint')

This way you can enforce multiple column unique constraint. Also just wanted point out that onupdate => refers to a default value for the column. There is no ondelete in sqlAlchemy as far as I know. but you can use:

  cascade = "all, delete-orphan" ``` 
on Parent Table relationship to achieve the same effect as ondelete="cascade" as in django.

class ParentDummies(db.Model): dummies= relationship("Dummy", cascade="all, delete-orphan")



Also, if you are trying make those columns as composite primary key, you can do so like this: 
```  class Dummies(db.Model):

    __tablename__ = 'dummies'

    id = db.Column(db.Integer)
    name = db.Column(db.String(100), nullable=True)
    value = db.Column(db.String(100), nullable=True)
    sum_id = db.Column(db.Integer, db.ForeignKey('pattern.id', nullable=False),
    PrimaryKeyConstrain('name', 'value', 'sum_id', name="myCompositePk")

OR

class Dummies(db.Model):

    __tablename__ = 'dummies'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(100), nullable=True, primary_key=True)
    value = db.Column(db.String(100), nullable=True, primary_key=True)
    sum_id = db.Column(db.Integer, db.ForeignKey('pattern.id'), nullable=False, primary_key=True)

Your models would fail to produce the constraints as explained here: https://stackoverflow.com/questions/26895207/how-is-a-unique-constraint-across-three-columns-defined, https://stackoverflow.com/questions/43975349/why-uniqueconstraint-doesnt-work-in-flask-sqlalchemy — Ilja Everilä, Nov 25 '19 at 05:54
In the last version `name, value, sum_id` would not be unique, because the autoincrementing column is included in the primary key. — Ilja Everilä, Nov 25 '19 at 07:17

Writing a composite Key in Flask SQL Alchemy

1 Answers1