Question 89037 in PostgreSQL Question posted in
The official documentation can be found here.

How to fix wrong number of constraints in Django with psql prompt? – Postgresql

I’m unable to migrate a model due to the following exception and I’m trying to fix the issue with psql.

ValueError: Found wrong number (2) of constraints for accounting_inventory(trade_id)

models.py

class Inventory(TimestampedModel):

    class Type(models.TextChoices):
        SNAPSHOT = 0, "Snapshot"
        ASSET = 1, "Asset"
        CONTRACT = 2, "Contract"

    id = models.UUIDField(default=uuid.uuid4, primary_key=True, editable=False)
    account = models.ForeignKey(Account, on_delete=models.CASCADE, null=True)
    trade = models.OneToOneField(Trade, on_delete=models.CASCADE, null=True)
    type = models.CharField(max_length=64, choices=Type.choices)
    datetime = models.DateTimeField()
    assets = models.JSONField(default=dict, null=True)
    contracts = models.JSONField(default=dict, null=True)

    class Meta:
        verbose_name_plural = "Inventory"
        unique_together = [('trade',), ]

    def __str__(self):
        return str(self.id)[-4:]

Now, Django says there is no migration:

root@1f3de954c6e0:/app# python manage.py makemigrations
No changes detected

Problem is when I remove the unique_together it generates this migration file but can’t migrate because of the exception above.

migration file

root@1f3de954c6e0:/app# more accounting/migrations/0022_alter_inventory_unique_together.py 
# Generated by Django 4.0.6 on 2023-04-10 06:40

from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [
        ('accounting', '0021_alter_inventory_unique_together_inventory_account_and_more'),
    ]

    operations = [
        migrations.AlterUniqueTogether(
            name='inventory',
            unique_together=set(),
        ),
    ]

I connected with psql and found this when listing constraints:

defaultdb=> SELECT con.*
defaultdb-> FROM pg_catalog.pg_constraint con
defaultdb-> INNER JOIN pg_catalog.pg_class rel
defaultdb-> ON rel.oid = con.conrelid
defaultdb-> INNER JOIN pg_catalog.pg_namespace nsp
defaultdb-> ON nsp.oid = connamespace
defaultdb-> WHERE nsp.nspname = 'public'
defaultdb-> AND rel.relname = 'accounting_inventory';
  oid  |                            conname                             | connamespace | contype | condeferrable | condeferred | convalidated | conrelid | contypid | conindid | conparentid | confrelid | confupdtype | confdeltype | confmatchtype | conislocal | coninhcount | connoinherit | conkey | confkey | conpfeqop | conppeqop | conffeqop | confdelsetcols | conexclop | conbin 
-------+----------------------------------------------------------------+--------------+---------+---------------+-------------+--------------+----------+----------+----------+-------------+-----------+-------------+-------------+---------------+------------+-------------+--------------+--------+---------+-----------+-----------+-----------+----------------+-----------+--------
 45847 | accounting_inventory_account_id_d03d3e05_fk_trading_account_id |         2200 | f       | t             | t           | t            |    20082 |        0 |    17118 |           0 |     17112 | a           | a           | s             | t          |           0 | t            | {24}   | {1}     | {410}     | {410}     | {410}     |                |           | 
 20288 | accounting_inventory_trade_id_5cf7c1af_fk_accounting_trade_id  |         2200 | f       | t             | t           | t            |    20082 |        0 |    20272 |           0 |     20267 | a           | a           | s             | t          |           0 | t            | {8}    | {3}     | {2972}    | {2972}    | {2972}    |                |           | 
 20106 | accounting_inventory_trade_id_5cf7c1af_uniq                    |         2200 | u       | f             | f           | t            |    20082 |        0 |    20105 |           0 |         0 |             |             |               | t          |           0 | t            | {8}    |         |           |           |           |                |           | 
 20089 | accounting_inventory_trade_id_key                              |         2200 | u       | f             | f           | t            |    20082 |        0 |    20088 |           0 |         0 |             |             |               | t          |           0 | t            | {8}    |         |           |           |           |                |           | 
 43164 | accounting_inventory_uuid_110c3781_pk                          |         2200 | p       | f             | f           | t            |    20082 |        0 |    43163 |           0 |         0 |             |             |               | t          |           0 | t            | {23}   |         |           |           |           |                |           | 
(5 rows)

And this:

defaultdb=> d+ accounting_inventory
                                              Table "public.accounting_inventory"
   Column    |           Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
-------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 dt_created  | timestamp with time zone |           |          |         | plain    |             |              | 
 dt_modified | timestamp with time zone |           |          |         | plain    |             |              | 
 trade_id    | uuid                     |           |          |         | plain    |             |              | 
 type        | character varying(64)    |           | not null |         | extended |             |              | 
 datetime    | timestamp with time zone |           | not null |         | plain    |             |              | 
 assets      | jsonb                    |           |          |         | extended |             |              | 
 contracts   | jsonb                    |           |          |         | extended |             |              | 
 id          | uuid                     |           | not null |         | plain    |             |              | 
 account_id  | bigint                   |           |          |         | plain    |             |              | 
Indexes:
    "accounting_inventory_uuid_110c3781_pk" PRIMARY KEY, btree (id)
    "accounting_inventory_account_id_d03d3e05" btree (account_id)
    "accounting_inventory_trade_id_5cf7c1af_uniq" UNIQUE CONSTRAINT, btree (trade_id)
    "accounting_inventory_trade_id_key" UNIQUE CONSTRAINT, btree (trade_id)
Foreign-key constraints:
    "accounting_inventory_account_id_d03d3e05_fk_trading_account_id" FOREIGN KEY (account_id) REFERENCES trading_account(id) DEFERRABLE INITIALLY DEFERRED
    "accounting_inventory_trade_id_5cf7c1af_fk_accounting_trade_id" FOREIGN KEY (trade_id) REFERENCES accounting_trade(id) DEFERRABLE INITIALLY DEFERRED
Access method: heap

What should I do to fix the problem ? I notice there are two unique constraints in the db for trade:

accounting_inventory_trade_id_5cf7c1af_uniq
accounting_inventory_trade_id_key
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I fixed the issue by doing the following:

    Delete inappropriate CONSTRAINTS :

    ALTER TABLE public.accounting_inventory DROP CONSTRAINT accounting_inventory_trade_id_5cf7c1af_uniq;
ALTER TABLE public.accounting_inventory DROP CONSTRAINT accounting_inventory_trade_id_key;

    Recreate CONSTRAINT with 2 columns:

    defaultdb=> ALTER TABLE accounting_inventory ADD UNIQUE (trade_id, account_id);

    Set unique_together in my models.py:

    unique_together = [('trade', 'account',), ]

    Then I generated the migration file migrated with the --fake option and it was fine.


  2. 0

    unique_together expects at least 2 fields. You’ve only given it one.

    unique_together = [('trade',), ]

    If you want just trade to be unique, use unique=True when you define the field.

    Note also that unique_together is on its way out, and you should use the constraints option instead if you need this: https://docs.djangoproject.com/en/4.1/ref/models/options/#unique-together

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search