Flask Foreign Key Constraint

Question

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,

Yeah I can see that, but what's your database ? SQLite doesn't enforce foreign keys by default, you have to use specific config to get it working with SQLAlchemy. — polku, Aug 05 '16 at 15:54
Yes, but i don't find the good document ( or tuto ) about it — WillB, Aug 05 '16 at 15:58
I try with db.ForeignKeyConstraint ( edit in my code) but no change — WillB, Aug 05 '16 at 16:14

score 2 · Accepted Answer · edited May 23 '17 at 12:22

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()

Rohanil · Answer 2 · 2016-08-08T11:25:55.810

1

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.

edited Aug 08 '16 at 11:25

answered Aug 05 '16 at 15:44

Rohanil

1,717
5
22
47

Doesn't work, i can still create alert with no-existing user_id and modify an alerte with a no-existing user_id – WillB Aug 05 '16 at 15:49
Hello, Thx but it still don't work, i am able to create `d = Alert(88, 'trdft')`even if 88 is not in the user db – WillB Aug 08 '16 at 07:37
It should work. Alert(88, 'trdft') should throw an error because Alert is expecting ( – Rohanil Aug 08 '16 at 08:39
Sorry for my mistake, i didn't change `__init__` but now I have a different issue. I cannot add a alert with an existing User..... I can add a alert with an new User, and the User will be create. When I try to create a alert with an existing User I got : `IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: users.user_id ` – WillB Aug 08 '16 at 09:02
Did you start with fresh db? I have made small change in my answer. I had changed tablename to 'user' but forgot to change it in foreignkey declaration `db.ForeignKey('user.user_id')` before. Now it should work perfectly. If not, could you give your code and exact error by editing the question? And try with fresh db. Thanks. – Rohanil Aug 08 '16 at 11:29
Great explanation +1 – kepy97 Jul 29 '20 at 15:53

score 0 · Answer 3 · answered Aug 05 '16 at 21:22

0

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.

answered Aug 05 '16 at 21:22

Karin

8,404
25
34

Flask Foreign Key Constraint

3 Answers3