Django中avg_winning_trade字段违反非空约束问题求助

报错信息 #

django.db.utils.IntegrityError: null value in column "avg_winning_trade" of relation "trading_summary" violates not-null constraint
DETAIL:  Failing row contains (43897354-d89b-4014-a607-a0e6ee423b52, 1, 2023-02-05 11:09:56.727199+00, null, 1, 19, 1, 0.00, null, -1.01, -1.01, 0, 0, 0.00, 0%, 0.00).

模型代码 #

class Summary(
  ActivatorModel,
  Model):

  user = models.OneToOneField(User, on_delete=models.CASCADE, null=True, blank=True)
  starting_balance = models.DecimalField(default=0, max_digits=10, decimal_places=2)
  total_number_of_trades = models.PositiveIntegerField(default=0)
  total_number_of_winning_trades = models.PositiveIntegerField(default=0)
  total_number_of_losing_trades = models.PositiveIntegerField(default=0)
  total_number_of_be_trade = models.PositiveIntegerField(default=0)
  largest_winning_trade = models.DecimalField(default=0, max_digits=10, decimal_places=2)
  largest_losing_trade = models.DecimalField(default=0, max_digits=10, decimal_places=2)
  avg_winning_trade = models.DecimalField(default=0, max_digits=10, decimal_places=2)
  avg_losing_trade = models.DecimalField(default=0, max_digits=10, decimal_places=2)
  total_trade_costs = models.DecimalField(default=0, max_digits=10, decimal_places=2)
  trade_win_rate = models.CharField(max_length=10)

数据库表结构 #

Column             |           Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------------------------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id                             | uuid                     |           | not null |         | plain    |             |              | 
 status                         | integer                  |           | not null |         | plain    |             |              | 
 activate_date                  | timestamp with time zone |           |          |         | plain    |             |              | 
 deactivate_date                | timestamp with time zone |           |          |         | plain    |             |              | 
 total_number_of_trades         | integer                  |           | not null |         | plain    |             |              | 
 user_id                        | integer                  |           |          |         | plain    |             |              | 
 total_number_of_winning_trades | integer                  |           | not null |         | plain    |             |              | 
 avg_losing_trade               | numeric(10,2)            |           | not null |         | main     |             |              | 
 avg_winning_trade              | numeric(10,2)            |           | not null |         | main     |             |              | 
 largest_losing_trade           | numeric(10,2)            |           | not null |         | main     |             |              | 
 largest_winning_trade          | numeric(10,2)            |           | not null |         | main     |             |              | 
 total_number_of_be_trade       | integer                  |           | not null |         | plain    |             |              | 
 total_number_of_losing_trades  | integer                  |           | not null |         | plain    |             |              | 
 total_trade_costs              | numeric(10,2)            |           | not null |         | main     |             |              | 
 trade_win_rate                 | character varying(10)    |           | not null |         | extended |             |              | 
 starting_balance               | numeric(10,2)            |           | not null |         | main     |             |              | 

原因分析 #

• 数据库层面未同步默认值：模型中设置的default=0是Django ORM层面的默认值，但从数据库表结构可见，avg_winning_trade的Default列为空。如果通过原生SQL、第三方工具或未经过ORM的方式插入数据，不会触发ORM的默认值逻辑，导致字段为空。
• 代码中显式赋值为None：创建Summary实例时，可能存在手动给avg_winning_trade赋值为None的情况，覆盖了ORM的默认值。
• 迁移操作遗漏：添加default=0后未生成并执行正确的迁移，导致数据库表的字段默认值未同步更新。

解决方法 #

1. 同步数据库默认值

   • 生成迁移文件：python manage.py makemigrations
   • 执行迁移：python manage.py migrate
   • 若自动生成的迁移未包含默认值设置，可手动编辑迁移文件的AlterField部分，确保添加default=0和null=False参数。

2. 检查实例创建代码
   确保创建Summary对象时没有显式将avg_winning_trade设为None，示例：

   # 错误写法
   Summary.objects.create(avg_winning_trade=None, ...)
   # 正确写法，依赖ORM默认值
   Summary.objects.create(...)
   

3. 修复已有空值数据
   如果表中已存在avg_winning_trade为空的记录，先通过SQL更新：

   UPDATE trading_summary SET avg_winning_trade = 0 WHERE avg_winning_trade IS NULL;
   

内容的提问来源于stack exchange，提问作者tanjona Randrianasolo