Getting (sqlite3.IntegrityError) NOT NULL constraint failed when trying to POST using FastAPI/ SQLit-CodePudding

I am getting the following error

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: SignalJourneyAudienceConstraints.constraintId
[SQL: INSERT INTO "SignalJourneyAudienceConstraints" ("sourceId", "constraintTypeId", "constraintValue", targeting, frequency, period) VALUES (?, ?, ?, ?, ?, ?)]
[parameters: (None, 10, 'STRING', 1, None, None)]

When trying to use the below endpoint. I have made all fields except:

constraintTypeId: int
constraintValue: str
targeting: bool

As optional for the time being.

Endpoint

# mail.py

...

@app.post("/add-new-audience-constraint", status_code=status.HTTP_200_OK)
def add_new_audience_constraint(
    sjac: schema.SignalJourneyAudienceConstraints, db: Session = Depends(get_db)
):
    """Sets the main query data."""
    new_audience_constraint = models.SignalJourneyAudienceConstraints(
        constraintTypeId=sjac.constraintTypeId,
        constraintValue=sjac.constraintValue.upper(),
        targeting=True,
    )

    db.add(new_audience_constraint)
    db.commit()

    return {"message": "Data added successfully."}

Model

# models.py 

class SignalJourneyAudienceConstraints(Base):
    """Signal Journey Audience Constraints"""

    __tablename__ = "SignalJourneyAudienceConstraints"
    constraintId = Column(Integer, primary_key=True)
    audienceId = Column(
        Integer,
        ForeignKey("SignalJourneyAudiences.audienceId"),
        primary_key=True,
    )
    sourceId = Column(Integer, ForeignKey("SignalJourneySources.sourceId"))
    constraintTypeId = Column(
        Integer, ForeignKey("SignalJourneyConstraintType.constraintTypeId")
    )
    constraintValue = Column(String)
    targeting = Column(Boolean)
    frequency = Column(Integer)
    period = Column(Integer)

Schema

# schema.py
class SignalJourneyAudienceConstraints(BaseModel):
    """SignalJourneyAudienceConstraints BaseModel."""

    constraintId: Optional[int]  # PK
    audienceId: Optional[int]  # FK - SignalJourneyAudiences -> audienceId
    sourceId: Optional[int]  # FK - SignalJourneySources -> sourceId
    constraintTypeId: int  # FK - SignalJourneyConstraintType -> constraintTypeId
    constraintValue: str
    targeting: bool
    frequency: Optional[int]
    period: Optional[int]

    class Config:
        """config class"""

        orm_mode = True

I have looked into other SO questions, but none seem to fit what I am experiencing. Hopefully someone can explain what's going on and how to resolve it

ERD

CodePudding user response：

You have two fields listed as primary_key; are they both primary keys? Since you have two fields, the primary key field will not automagically defined as an auto increment field; for that you want to only have one primary key, integer field.

SQLAlchemy will only set the auto_increment property for SQLite automagically when there is a single primary key column and it's defined as an integer.

Since defining a composite key wasn't what you wanted, using constraintId as a single auto incrementing key (as shown in your ERD) is what you want - remove the primary_key entry for audienceId field.