Improvement of the error message provided when assert_integrity fails with Database constraints

[gregorw]

Hello CAP Team,

with the Release March 2022 the feature of Database Integrity Constraints was introduced. It is also mentioned that it will become the default in the next major release in 2022. But it's also mentioned that:

"Database constraints are not intended for checking user input. Instead, they protect the integrity of your data in the database layer against programming errors."

When I activate the Database constraints by setting my package.json to:

  "cds": {
    "features": {
      "assert_integrity": "db"
    },

I see this result:

{
  "error": {
    "code": "400",
    "message": "Foreign key constraint violation"
  }
}

returned from the OData V4 API. When I run the following HTTP request to my demo project bookshop-demo:

### Create Order as john - using deep insert
POST http://localhost:4004/catalog/Orders
Authorization: Basic john:
Content-Type: application/json

{
    "OrderNo": "10",
    "CustomerOrderNo": "Order from john",
    "currency_code": "EUR",
    "Items": [
        {
            "book_ID": 3201
        }
    ]
}

In the srv console logs I can see:

[SqlError: transaction rollback, deferred fk constraint violation: deferred foreign key violation on table 982A782C110E4DD99FA07A3141898F90:MY_BOOKSHOP_ORDERITEMS] {
  code: 155,
  sqlState: 'HY000',
  level: 1,
  position: 0,
  id: '1123653',
  timestamp: 1653137976527
}

with the configuration set to

  "cds": {
    "features": {
      "assert_integrity": "app"
    },

the result is:

{
  "error": {
    "code": "400",
    "message": "Reference integrity is violated for association \"book\"",
    "target": "CatalogService.Books",
    "@Common.numericSeverity": 4
  }
}

and in the console I see:

{
  code: 'ASSERT_REFERENCE_INTEGRITY',
  target: 'CatalogService.Books',
  args: [ 'book' ],
  entity: 'CatalogService.OrderItems',
  element: 'book',
  type: 'cds.Association',
  value: { ID: 3201 },
  numericSeverity: 4,
  id: '1335269',
  level: 'ERROR',
  timestamp: 1653137397582
}

which helps to track down the problem quickly.

I hope there are plans to improve the error message for assert_integrity = db. Wouldn't it be an option to have the database integrity as the default and then when an error occurs use the already existing functionality to detect where the inconsistency comes from? That would improve performance in the standard cases but keep the error message on the same level as before.

Best Regards
Gregor

[OlenaT]

Hi gregorwolf,

We will discuss this internally and get back to you as soon as possible.

Best regards,

Olena

[OlenaT]

Hi gregorwolf,

The option you are proposing will not work for us, because the existing functionality is to send additional select requests to find inconsistencies. This is exactly what we are trying to avoid by delegating constrains checks to db. But we are working on other ways how error messages can be improved. This new functionality will be available with the next release (cds 6), after that I will give you an update with more detailed information.

Best regards,

Olena

[gregorw]

Hi Olena,

thank you for the update.

CU
Gregor

[OlenaT · Accepted solution]

Hi Gregor,

For a better error message you can annotate your association with @assert.target:

https://cap.cloud.sap/docs/releases/jun22#db-constraints

https://cap.cloud.sap/docs/guides/providing-services#assert-target

Best regards,

Olena