数据库关系设计实战：MySQL外键约束与Django外键应用详解

MySQL 外键详解

1. 外键概念

外键（Foreign Key）是数据库中用于建立表与表之间关系的约束机制，它指向另一个表的主键或唯一键。

2. 外键的作用

数据完整性保证

• 参照完整性：确保外键值在参照表中存在
• 级联操作：自动维护相关数据的一致性
• 约束检查：防止插入无效的关联数据

关系维护

• 一对多关系：一个父记录对应多个子记录
• 多对多关系：通过中间表实现
• 一对一关系：特殊的一对多关系

3. MySQL 外键语法

创建表时定义外键

-- 父表
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 子表（带外键）
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 定义外键约束
    CONSTRAINT fk_employee_department 
    FOREIGN KEY (department_id) 
    REFERENCES departments(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

添加外键约束

-- 为已存在的表添加外键
ALTER TABLE employees 
ADD CONSTRAINT fk_employee_department 
FOREIGN KEY (department_id) 
REFERENCES departments(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

删除外键约束

-- 删除外键约束
ALTER TABLE employees 
DROP FOREIGN KEY fk_employee_department;

4. 外键约束选项

ON DELETE 选项

-- CASCADE: 删除父记录时自动删除相关子记录
ON DELETE CASCADE

-- SET NULL: 删除父记录时将外键字段设为NULL
ON DELETE SET NULL

-- RESTRICT: 有子记录时禁止删除父记录（默认）
ON DELETE RESTRICT

-- NO ACTION: 同RESTRICT
ON DELETE NO ACTION

-- SET DEFAULT: 设置为默认值（MySQL不支持）

ON UPDATE 选项

-- CASCADE: 更新父键时自动更新外键
ON UPDATE CASCADE

-- SET NULL: 更新父键时将外键设为NULL
ON UPDATE SET NULL

-- RESTRICT: 有子记录时禁止更新父键
ON UPDATE RESTRICT

-- NO ACTION: 同RESTRICT
ON UPDATE NO ACTION

5. 外键使用示例

完整的多表设计

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 分类表
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 文章表
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author_id INT NOT NULL,
    category_id INT,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- 外键约束
    CONSTRAINT fk_article_author 
    FOREIGN KEY (author_id) 
    REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    
    CONSTRAINT fk_article_category 
    FOREIGN KEY (category_id) 
    REFERENCES categories(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

-- 标签表
CREATE TABLE tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 文章标签关联表（多对多）
CREATE TABLE article_tags (
    article_id INT,
    tag_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (article_id, tag_id),
    
    CONSTRAINT fk_article_tag_article 
    FOREIGN KEY (article_id) 
    REFERENCES articles(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    
    CONSTRAINT fk_article_tag_tag 
    FOREIGN KEY (tag_id) 
    REFERENCES tags(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

-- 评论表
CREATE TABLE comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    content TEXT NOT NULL,
    article_id INT NOT NULL,
    author_id INT NOT NULL,
    parent_id INT DEFAULT NULL,  -- 自引用外键，用于回复评论
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_comment_article 
    FOREIGN KEY (article_id) 
    REFERENCES articles(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    
    CONSTRAINT fk_comment_author 
    FOREIGN KEY (author_id) 
    REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    
    CONSTRAINT fk_comment_parent 
    FOREIGN KEY (parent_id) 
    REFERENCES comments(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

6. 外键查询操作

基本联接查询

-- 内连接：获取有分类的文章
SELECT a.title, a.content, c.name as category_name, u.username as author
FROM articles a
INNER JOIN categories c ON a.category_id = c.id
INNER JOIN users u ON a.author_id = u.id;

-- 左连接：获取所有文章（包括无分类的）
SELECT a.title, c.name as category_name
FROM articles a
LEFT JOIN categories c ON a.category_id = c.id;

-- 多表连接
SELECT 
    a.title,
    u.username as author,
    c.name as category,
    GROUP_CONCAT(t.name) as tags
FROM articles a
LEFT JOIN users u ON a.author_id = u.id
LEFT JOIN categories c ON a.category_id = c.id
LEFT JOIN article_tags at ON a.id = at.article_id
LEFT JOIN tags t ON at.tag_id = t.id
GROUP BY a.id;

子查询示例

-- 查找没有文章的分类
SELECT * FROM categories 
WHERE id NOT IN (
    SELECT DISTINCT category_id 
    FROM articles 
    WHERE category_id IS NOT NULL
);

-- 查找文章数量最多的用户
SELECT u.username, COUNT(a.id) as article_count
FROM users u
LEFT JOIN articles a ON u.id = a.author_id
GROUP BY u.id
ORDER BY article_count DESC
LIMIT 1;

Django 外键详解

1. Django 外键基础

Django 使用 ForeignKey 字段来定义外键关系，它会自动在数据库中创建外键约束。

2. 基本外键定义

简单外键关系

# models.py
from django.db import models
from django.contrib.auth.models import User

class Category(models.Model):
    name = models.CharField(max_length=100)
    description = models.TextField(blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    
    class Meta:
        verbose_name_plural = "Categories"
    
    def __str__(self):
        return self.name

class Article(models.Model):
    title = models.CharField(max_length=200)
    content = models.TextField()
    author = models.ForeignKey(
        User, 
        on_delete=models.CASCADE,
        related_name='articles'
    )
    category = models.ForeignKey(
        Category,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name='articles'
    )
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    
    def __str__(self):
        return self.title

3. on_delete 参数详解

Django 的 on_delete 参数对应 MySQL 的 ON DELETE 选项：

from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=100)

class Book(models.Model):
    title = models.CharField(max_length=200)
    
    # CASCADE: 删除作者时删除所有相关书籍
    author_cascade = models.ForeignKey(
        Author,
        on_delete=models.CASCADE
    )
    
    # SET_NULL: 删除作者时将外键设为 NULL
    author_set_null = models.ForeignKey(
        Author,
        on_delete=models.SET_NULL,
        null=True,
        blank=True
    )
    
    # PROTECT: 有相关书籍时禁止删除作者
    author_protect = models.ForeignKey(
        Author,
        on_delete=models.PROTECT
    )
    
    # SET_DEFAULT: 删除作者时设为默认值
    author_set_default = models.ForeignKey(
        Author,
        on_delete=models.SET_DEFAULT,
        default=1
    )
    
    # SET(): 删除时调用函数设置值
    def get_default_author():
        return Author.objects.get_or_create(name="Unknown")[0]
    
    author_set_function = models.ForeignKey(
        Author,
        on_delete=models.SET(get_default_author)
    )
    
    # DO_NOTHING: 不做任何操作（可能导致数据库约束错误）
    author_do_nothing = models.ForeignKey(
        Author,
        on_delete=models.DO_NOTHING
    )

4. 外键字段选项

class Article(models.Model):
    # 基本外键
    category = models.ForeignKey(
        'Category',  # 可以使用字符串引用
        on_delete=models.CASCADE
    )
    
    # 带完整选项的外键
    author = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name='authored_articles',  # 反向关系名称
        related_query_name='authored_article',  # 反向查询名称
        to_field='username',  # 指定引用字段（默认是主键）
        db_column='author_username',  # 数据库列名
        db_index=True,  # 创建数据库索引
        limit_choices_to={'is_active': True},  # 限制选择范围
        verbose_name='文章作者',  # 字段显示名称
        help_text='选择文章的作者'  # 帮助文本
    )

5. 关系查询

正向查询（从子对象到父对象）

# 获取文章的作者
article = Article.objects.get(id=1)
author = article.author
print(author.username)

# 获取文章的分类
category = article.category
if category:
    print(category.name)

反向查询（从父对象到子对象）

# 获取用户的所有文章
user = User.objects.get(username='john')
articles = user.articles.all()  # 使用 related_name

# 或者使用默认的反向关系
articles = user.article_set.all()

# 获取分类下的所有文章
category = Category.objects.get(name='Technology')
articles = category.articles.all()

跨关系查询

# 使用双下划线进行跨关系查询
# 查找特定作者的文章
articles = Article.objects.filter(author__username='john')

# 查找特定分类下的文章
articles = Article.objects.filter(category__name='Technology')

# 复杂的跨关系查询
articles = Article.objects.filter(
    author__is_active=True,
    category__name__icontains='tech',
    created_at__year=2023
)

# 反向跨关系查询
users = User.objects.filter(articles__category__name='Technology')

# 统计查询
from django.db.models import Count
users_with_article_count = User.objects.annotate(
    article_count=Count('articles')
).filter(article_count__gt=5)

6. 多对多关系

class Tag(models.Model):
    name = models.CharField(max_length=50, unique=True)
    
    def __str__(self):
        return self.name

class Article(models.Model):
    title = models.CharField(max_length=200)
    content = models.TextField()
    author = models.ForeignKey(User, on_delete=models.CASCADE)
    
    # 多对多关系
    tags = models.ManyToManyField(
        Tag,
        blank=True,
        related_name='articles',
        through='ArticleTag'  # 可选：指定中间表
    )

# 自定义中间表
class ArticleTag(models.Model):
    article = models.ForeignKey(Article, on_delete=models.CASCADE)
    tag = models.ForeignKey(Tag, on_delete=models.CASCADE)
    added_by = models.ForeignKey(User, on_delete=models.CASCADE)
    added_at = models.DateTimeField(auto_now_add=True)
    
    class Meta:
        unique_together = ('article', 'tag')

# 多对多关系操作
article = Article.objects.get(id=1)

# 添加标签
tag = Tag.objects.get(name='Python')
article.tags.add(tag)

# 批量添加
article.tags.add(tag1, tag2, tag3)

# 删除标签
article.tags.remove(tag)

# 清空所有标签
article.tags.clear()

# 设置标签（替换所有现有标签）
article.tags.set([tag1, tag2])

# 查询
articles_with_python_tag = Article.objects.filter(tags__name='Python')

7. 自引用外键

class Comment(models.Model):
    content = models.TextField()
    article = models.ForeignKey(Article, on_delete=models.CASCADE)
    author = models.ForeignKey(User, on_delete=models.CASCADE)
    
    # 自引用外键，用于回复评论
    parent = models.ForeignKey(
        'self',
        on_delete=models.CASCADE,
        null=True,
        blank=True,
        related_name='replies'
    )
    created_at = models.DateTimeField(auto_now_add=True)
    
    def __str__(self):
        return f"Comment by {self.author.username}"

# 使用自引用外键
# 创建主评论
main_comment = Comment.objects.create(
    content="Great article!",
    article=article,
    author=user
)

# 创建回复
reply = Comment.objects.create(
    content="I agree!",
    article=article,
    author=another_user,
    parent=main_comment
)

# 查询评论的回复
replies = main_comment.replies.all()

# 查询顶级评论（没有父评论的评论）
top_level_comments = Comment.objects.filter(parent=None)

8. 外键性能优化

使用 select_related

# 避免 N+1 查询问题
# 不好的做法
articles = Article.objects.all()
for article in articles:
    print(article.author.username)  # 每次都会查询数据库

# 好的做法
articles = Article.objects.select_related('author', 'category').all()
for article in articles:
    print(article.author.username)  # 不会额外查询数据库

使用 prefetch_related

# 对于反向关系和多对多关系
authors = User.objects.prefetch_related('articles').all()
for author in authors:
    for article in author.articles.all():  # 不会额外查询
        print(article.title)

# 对于多对多关系
articles = Article.objects.prefetch_related('tags').all()
for article in articles:
    for tag in article.tags.all():
        print(tag.name)

使用 Prefetch 对象进行高级预取

from django.db.models import Prefetch

# 只预取已发布的文章
authors = User.objects.prefetch_related(
    Prefetch(
        'articles',
        queryset=Article.objects.filter(status='published'),
        to_attr='published_articles'
    )
).all()

for author in authors:
    for article in author.published_articles:
        print(article.title)

9. 外键字段的表单处理

在 Django Forms 中使用外键

from django import forms
from .models import Article, Category

class ArticleForm(forms.ModelForm):
    class Meta:
        model = Article
        fields = ['title', 'content', 'category', 'tags']
        widgets = {
            'category': forms.Select(attrs={'class': 'form-control'}),
            'tags': forms.CheckboxSelectMultiple(),
        }
    
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        # 限制分类选择
        self.fields['category'].queryset = Category.objects.filter(
            is_active=True
        )

# 在视图中使用
from django.shortcuts import render, redirect

def create_article(request):
    if request.method == 'POST':
        form = ArticleForm(request.POST)
        if form.is_valid():
            article = form.save(commit=False)
            article.author = request.user
            article.save()
            form.save_m2m()  # 保存多对多关系
            return redirect('article_detail', pk=article.pk)
    else:
        form = ArticleForm()
    
    return render(request, 'create_article.html', {'form': form})

10. 外键的 Django Admin 配置

from django.contrib import admin
from .models import Article, Category, Tag

@admin.register(Category)
class CategoryAdmin(admin.ModelAdmin):
    list_display = ['name', 'created_at']
    search_fields = ['name']

class ArticleTagInline(admin.TabularInline):
    model = Article.tags.through
    extra = 1

@admin.register(Article)
class ArticleAdmin(admin.ModelAdmin):
    list_display = ['title', 'author', 'category', 'created_at']
    list_filter = ['category', 'created_at', 'author']
    search_fields = ['title', 'content']
    raw_id_fields = ['author']  # 对于大量数据使用原始ID字段
    filter_horizontal = ['tags']  # 多对多字段的水平过滤器
    inlines = [ArticleTagInline]
    
    def get_queryset(self, request):
        # 优化查询
        return super().get_queryset(request).select_related(
            'author', 'category'
        ).prefetch_related('tags')

11. 数据库迁移

# 创建迁移文件
# python manage.py makemigrations

# 应用迁移
# python manage.py migrate

# 如果需要修改外键约束
from django.db import migrations, models

class Migration(migrations.Migration):
    dependencies = [
        ('myapp', '0001_initial'),
    ]

    operations = [
        migrations.AlterField(
            model_name='article',
            name='category',
            field=models.ForeignKey(
                blank=True,
                null=True,
                on_delete=models.SET_NULL,
                related_name='articles',
                to='myapp.category'
            ),
        ),
    ]

12. 最佳实践

命名规范

class Article(models.Model):
    # 使用有意义的 related_name
    author = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name='authored_articles'  # 而不是默认的 'article_set'
    )
    
    # 对于可能为空的外键，总是设置 null=True, blank=True
    category = models.ForeignKey(
        Category,
        on_delete=models.SET_NULL,
        null=True,
        blank=True
    )

性能考虑

# 使用数据库级别的约束
class Article(models.Model):
    author = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        db_index=True  # 创建数据库索引
    )
    
    class Meta:
        indexes = [
            models.Index(fields=['author', 'created_at']),  # 复合索引
        ]

数据完整性

from django.core.exceptions import ValidationError

class Article(models.Model):
    author = models.ForeignKey(User, on_delete=models.CASCADE)
    
    def clean(self):
        # 自定义验证
        if self.author and not self.author.is_active:
            raise ValidationError('Cannot assign inactive author')
    
    def save(self, *args, **kwargs):
        self.full_clean()  # 调用验证方法
        super().save(*args, **kwargs)