How to Use Foreign Key in Flask-SQLAlchemy: Syntax and Example

In Flask-SQLAlchemy, use db.ForeignKey inside a model's column to link it to another table's primary key. Define the foreign key column with db.Column and reference the target table and column as a string like 'target_table.id'. This creates a relationship between tables for data integrity.

Syntax

To use a foreign key in Flask-SQLAlchemy, define a column with db.Column and set its type (usually db.Integer). Then add db.ForeignKey('target_table.target_column') to link it to the referenced table's column.

• db.Column: Defines a column in the model.
• db.Integer: Data type for the column.
• db.ForeignKey('table.column'): Specifies the foreign key relationship.

python

class Child(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('parent.id'))

Example

This example shows two models, Parent and Child. The Child model has a foreign key parent_id that links to Parent's id. It demonstrates how to create and query related records.

python

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

class Parent(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)
    children = db.relationship('Child', backref='parent', lazy=True)

class Child(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)
    parent_id = db.Column(db.Integer, db.ForeignKey('parent.id'), nullable=False)

with app.app_context():
    db.create_all()
    p = Parent(name='Alice')
    db.session.add(p)
    db.session.commit()
    c = Child(name='Bob', parent_id=p.id)
    db.session.add(c)
    db.session.commit()
    # Query child and access parent
    child = Child.query.first()
    print(f"Child: {child.name}, Parent: {child.parent.name}")

Output

Child: Bob, Parent: Alice

Common Pitfalls

Common mistakes when using foreign keys in Flask-SQLAlchemy include:

• Not matching the foreign key string to the exact table and column name (case sensitive).
• Forgetting to add nullable=False if the foreign key must always have a value.
• Not creating the referenced table before the foreign key table, causing errors on db.create_all().
• Missing the db.relationship on the parent model, which helps with easy access to related objects.

python

class Child(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    # Wrong foreign key string (missing 'parent' table)
    parent_id = db.Column(db.Integer, db.ForeignKey('parents.id'))  # Incorrect

# Correct usage:
class Child(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('parent.id'))  # Correct

Quick Reference

Tips for using foreign keys in Flask-SQLAlchemy:

• Use db.ForeignKey('table.column') with the exact table and column name.
• Add db.relationship on the parent model for easy access to children.
• Set nullable=False on foreign key columns if the relationship is required.
• Create tables in order: referenced tables first, then referencing tables.

Key Takeaways

Use db.ForeignKey('table.column') inside db.Column to define foreign keys in Flask-SQLAlchemy.

Always match the foreign key string to the exact referenced table and column name.

Add db.relationship on the parent model to access related child records easily.

Set nullable=False on foreign key columns if the relationship must always exist.

Create referenced tables before tables that use foreign keys to avoid errors.