When there are multiple PRIMARY KEY / UNIQUE constraints, they must have at least one column in common.

[liujichengs]

Self Checks

• This is only for bug report, if you would like to ask a question, please head to Discussions.
• I have searched for existing issues search for existing issues, including closed ones.
• I confirm that I am using English to submit this report (我已阅读并同意 Language Policy).
• Please do not modify this template :) and fill in all the required fields.

Dify version

0.6.8

Cloud or Self Hosted

Self Hosted (Source)

Steps to reproduce

flask db upgrade fail

INFO [alembic.runtime.migration] Running upgrade -> 64b051264f32, init
Traceback (most recent call last):
File "/home/admin/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
self.dialect.do_execute(
File "/home/admin/.local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.InvalidTableDefinition: UNIQUE or PRIMARY KEY definitions are incompatible with each other
HINT: When there are multiple PRIMARY KEY / UNIQUE constraints, they must have at least one column in common.

✔️ Expected Behavior

flask db upgrade fail

INFO [alembic.runtime.migration] Running upgrade -> 64b051264f32, init
Traceback (most recent call last):
File "/home/admin/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
self.dialect.do_execute(
File "/home/admin/.local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.InvalidTableDefinition: UNIQUE or PRIMARY KEY definitions are incompatible with each other
HINT: When there are multiple PRIMARY KEY / UNIQUE constraints, they must have at least one column in common.

❌ Actual Behavior

flask db upgrade fail

INFO [alembic.runtime.migration] Running upgrade -> 64b051264f32, init
Traceback (most recent call last):
File "/home/admin/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
self.dialect.do_execute(
File "/home/admin/.local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.InvalidTableDefinition: UNIQUE or PRIMARY KEY definitions are incompatible with each other
HINT: When there are multiple PRIMARY KEY / UNIQUE constraints, they must have at least one column in common.

[dosubot]

The error you're encountering suggests there's a conflict between UNIQUE and PRIMARY KEY constraints in your database schema. This typically happens when a migration tries to introduce constraints that are incompatible with the existing schema. Here's how to address the issue:

1. Review Previous Migrations and Model Definitions: Look through your previous Alembic migrations and SQLAlchemy model definitions to identify any columns that might be incorrectly set as both unique=True and part of a composite primary key. Ensure no column is marked with conflicting constraints.

2. Inspect Your Database Schema: Use database tools like pgAdmin or command-line utilities to examine the existing constraints in your database. Look for any that might conflict with your migration's changes.

3. Adjust Your Migration Script: If you find conflicting constraints, modify your migration script to remove them before applying new ones. Use Alembic's op.drop_constraint for removing existing constraints as needed.

4. Test in a Safe Environment: Always test your migration in a development or staging environment before applying it to your production database to ensure it runs smoothly.

5. Check for Known Issues: Investigate if there are any known issues with the versions of Flask, SQLAlchemy, or psycopg2 you're using that might be related to your problem. Updating these packages could resolve the issue.

6. Enable Detailed Logging: For more insight into the migration process, consider enabling DEBUG logging for Alembic and SQLAlchemy in the alembic.ini file. This can provide valuable information for troubleshooting.

This approach should help you identify and resolve the conflict causing the psycopg2.errors.InvalidTableDefinition error during your Flask database upgrade.

Details

Sources

• api/migrations/versions/e32f6ccb87c6_e08af0a69ccefbb59fa80c778efee300bb780980.py
• api/migrations/versions/c71211c8f604_add_tool_invoke_model_log.py
• api/models/model.py
• api/requirements.txt
• api/migrations/versions/64b051264f32_init.py
• api/migrations/versions/3ef9b2b6bee6_add_assistant_app.py
• api/migrations/versions/b289e2408ee2_add_workflow.py
• api/config.py
• zh_CN/learn-more/faq/install-faq.md
• api/migrations/alembic.ini

About Dosu
This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

To continue the conversation, mention @dosu.