What's the best practice to enforce a foreign key constraint through a REST API?

Question

I am making a to-do list webapp as a hobby project using flask for the backend and a PostgreSQL database to store the data. The database model is a as follows:

models.py

class Group(db.Model):
    __tablename__ = "groups"
    group_id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name = db.Column(db.String(20), unique=True)


class Collection(db.Model):
    __tablename__ = "collections"
    collection_id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name = db.Column(db.String(20), unique=True)
    group_name = db.Column(db.String(20), db.ForeignKey("groups.name"), nullable=True)

    def to_dict(self):
        return {
            "collection_id": self.collection_id,
            "name": self.name,
            "group_name": self.group_name,
        }


class Task(db.Model):
    __tablename__ = "tasks"
    task_id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    text = db.Column(db.String(200))
    completed = db.Column(db.Boolean, default=False)
    collection_name = db.Column(
        db.String(20), db.ForeignKey("collections.name"), nullable=False
    )

    def to_dict(self):
        return {
            "task_id": self.task_id,
            "text": self.text,
            "completed": self.completed,
            "collection_name": self.collection_name,
        }

While making the REST api logic for adding tasks to the database, I was unsure if:

• I should check if the collection_name column already exists in the collections table before trying to insert the data in the database.
• I should try inserting the row anyway and catch the sqlalchemy.exc.IntegrityError exception if it happens.

The problem I see with the first solution, is I need to query the collections tables for the list of valid collection_name each time I want to add a task, which I am not sure if it's a good practice performance wise.

While the problem I see with the second solution is that the sqlalchemy.exc.IntegrityError exception is pretty vague and in a more sophisticated table with several foreign keys, I would need to parse the exception's message to know which foreign key was violated.

For now, I implemented the second solution because I have a very simple table with only one foreign key constraint.

In the following you can see the code for the controller.py that handles the API call and service.py that talks with the database.

controller.py

@tasks_api.route(COMMON_API_ENDPOINT + "/tasks", methods=["POST"])
def add_task():
    request_body = request.get_json()

    # Check for missing fields in the call
    mandatory_fields = set(["text", "collection_name"])
    try:
        missing_fields = mandatory_fields - set(request_body.keys())
        assert len(missing_fields) == 0
    except AssertionError:
        return (
            jsonify(
                {
                    "error": "The following mandatory fields are missing: "
                    + str(missing_fields)
                }
            ),
            400,
        )

    # Try to call the add task service function
    try:
        task = TaskService.add_task(
            text=request_body["text"], collection_name=request_body["collection_name"]
        )
    except CollectionNotFoundError as e:
        return jsonify({"error_message": str(e)}), 400
    else:
        return (
            jsonify(
                {
                    "result": "A new task was created successfully.",
                    "description": task.to_dict(),
                }
            ),
            201,
        )

service.py

def add_task(text: str, collection_name: str) -> Task:
    try:
        with get_session() as session:
            task = Task(text=text, collection_name=collection_name)
            session.add(task)
            return task
    except sqlalchemy.exc.IntegrityError:
        raise CollectionNotFoundError(
            "Foreign key violation: There is no collection with the name "
            + collection_name
        )

While writing this post, I wondered if this is an XY problem where both solutions are not the best. I am open to other suggestions too.

Thanks !