CODEWITHSUNDEEP
pythonflaskforeign-keysflask-sqlalchemy
WillB
WillB

Reputation: 43

Flask Foreign Key Constraint

I have an issue with foreign key in Flask. My model is the following :

Model.py

class User(db.Model):

    __tablename__ = "users"
    __table_args__ = {'extend_existing': True}
    user_id = db.Column(db.BigInteger, primary_key=True)
    # EDIT
    alerts = db.relationship('Alert', backref='user', lazy='dynamic')

    def __init__(self, user_id):
        self.user_id = user_id


class Alert(db.Model):

    __tablename__ = 'alert'
    __table_args__ = {'extend_existing': True}
    alert_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.BigInteger, db.ForeignKey('users.user_id'), nullable=False)
    name = db.Column(db.String(ALERT_NAME_MAX_SIZE), nullable=False)

    def __init__(self, user_id, name):
        self.user_id = user_id
        self.name = name

I am able to add some user, for example

a = User(16)
b = User(17)
db.session.add(a)
db.session.add(b)
db.session.commit()

and some alerts : 

c = Alert(16, 'test')
d = Alert(17, 'name_test')
db.session.add(c)
db.session.add(d)
db.session.commit()

I have two issues with the foreign key : First of all, when I try to modify the user_id alert, I am able to do it even if the user_id is not in the database 

 alert = Alert.query.get(1)
 alert.user_id = 1222 # not in the database
 db.session.commit()

and I am able to create a alert with an user_id not in the Database: 

r = Alert(16223, 'test')
db.session.add(r)

I don't understand why they is no relationship constraint. Thx,

Upvotes: 0

Views: 6663

Answers (3)

Rohanil
Rohanil

Reputation: 1887

There is mistake in your code for initialisation of Alert class. You should use backref variable (which is 'user') instead of user_id while initializing Alert. Following code should work. 

class User(db.Model):

    __tablename__ = "user"
    __table_args__ = {'extend_existing': True}
    user_id = db.Column(db.BigInteger, primary_key=True)
    alerts = db.relationship('Alert', backref='user', lazy='dynamic')

    def __init__(self, user_id):
        self.user_id = user_id


class Alert(db.Model):

    __tablename__ = 'alert'
    alert_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.BigInteger, db.ForeignKey('user.user_id'), nullable=False)
    name = db.Column(db.String(ALERT_NAME_MAX_SIZE), nullable=False)

    def __init__(self, user, name):
        self.user = user
        self.name = name

It works as below:

>>> a = User(7)
>>> db.session.add(a)
>>> db.session.commit()
>>> b = Alert(a, 'test')
>>> db.session.add(b)
>>> db.session.commit()
>>> alert = Alert.query.get(1)
>>> alert.user_id
7
>>> alert.user
<app.User object at 0x1045cb910>
>>> alert.user.user_id
7

It does not allow you to assign variable like d = Alert(88, 'trdft')

I think you should read Flask SqlAlchemy's One-to-Many Relationships for more details.

Upvotes: 1

WillB
WillB

Reputation: 43

So I find how to do it with this stackoverflow question , I find how to force foreign Key Constraint.

I juste add this in __init__.py and change nothing to models.py

@event.listens_for(Engine, "connect")
    def _set_sqlite_pragma(dbapi_connection, connection_record):
         if isinstance(dbapi_connection, SQLite3Connection):
              cursor = dbapi_connection.cursor()
              cursor.execute("PRAGMA foreign_keys=ON;")
              cursor.close()

Upvotes: 2

Karin
Karin

Reputation: 8610

If you are using SQLite, foreign key constraints are by default not enforced. See Enabling Foreign Key Support in the documentation for how to enable this.

Upvotes: 0

Related Questions