Reputation: 8589
I am building the following DB scheme in Flask-SQLAlchemy:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
app = Flask(__name__)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db'
db = SQLAlchemy(app)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64), index=True, unique=True)
email = db.Column(db.String(120), index=True, unique=True)
password_hash = db.Column(db.String(128))
posts = db.relationship('Post', backref='author', lazy='dynamic')
def __repr__(self):
return '<User {}>'.format(self.username)
class Post(db.Model):
id = db.Column(db.Integer, primary_key=True)
body = db.Column(db.String(140))
timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
def __repr__(self):
return '<Post {}>'.format(self.body)
db.drop_all()
db.create_all()
u = User(username='susan', email='[email protected]')
db.session.add(u)
p = Post(body='this is my first post!', user_id=1)
db.session.add(p)
# Now I create a new post for a non-existing user and I'd expect an error...
p = Post(body='this is my second post!', user_id=2)
db.session.add(p)
db.session.commit()
As you can see I manually enter a new user and then enter two posts.
I would like that on the second post insert an error is thrown because user_id=2 is not existing in the database.
But instead the insert is successful and I also see the data into the DB:
sqlite> select * from post;
1|this is my post!|2018-07-09 16:13:16.947856|1
2|this is my post!|2018-07-09 16:13:16.948996|2
sqlite>
How can I enforce the constraint in order to make it throw an error?
Thanks for your help.
Upvotes: 4
Views: 2528
Reputation: 326
To resolve this situation, instead of assigning an id to a post object, it would be better and more ORM-like to associate a User with a Post. Also, by enforcing the NOT NULL
ability of the foreign key column, invalid numbers cannot be added.
For example:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
app = Flask(__name__)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db'
db = SQLAlchemy(app)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64), index=True, unique=True)
email = db.Column(db.String(120), index=True, unique=True)
password_hash = db.Column(db.String(128))
posts = db.relationship('Post', lazy='dynamic', back_populates='author')
def __repr__(self):
return '<User {}>'.format(self.username)
class Post(db.Model):
id = db.Column(db.Integer, primary_key=True)
body = db.Column(db.String(140))
timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
author_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
author = db.relationship('User')
def __repr__(self):
return '<Post {}>'.format(self.body)
db.drop_all()
db.create_all()
# Susan will be both created and added to the session
u1 = User(username='susan', email='[email protected]')
db.session.add(u1)
# John will be created, but not added
u2 = User(username='john', email='[email protected]')
# Create a post by Susan
p1 = Post(body='this is my post!', author=u1)
# Add susan's post to the session
db.session.add(p1)
# Create a post by john, since john does not yet exist as a user, he is created automatically
p2 = Post(body='this is my post!', author=u2)
# Add john's post to the session
db.session.add(p2)
# After the session has everything defined, commit it to the database
db.session.commit()
Upvotes: 2