On Database Constraints in Oracle

So there was something confusing about a check constraint in one of my tables.

I've proven that the check constraint works as it should, but I failed to understand why.

Actually there were two constraints that sort of combine.

Let's say I have a table called "customers". And that there are two constraints at play:

• constraint check01 check (bankaccountnr is not null or creditcardnr is not null or paypalemail is not null)
• constraint check02 check (bankaccountnr > 0 and creditcardnr > 0 and length(paypalemail) > 0)

The first constraint is obvious, at least one of the fields must be filled in, in order to process orders.

It's the second constraint that is confusing.

I thought that, because of the and, the check02 constraint should fire if I try to enter only a bankaccountnr (as the other two conditions evaluate to false).

And I thought I verified that by using the query:

select * from customers where bankaccountnr > 0 and creditcardnr > 0 and length(paypalemail) > 0;

As I suspected it provided me ONLY the records that had ALL fields entered properly.

But apparently, in (check) constraints, if the condition is "unknown", for example in the case of NULL-values, it is accepted.

According to the following quote, found in [1] which I found via [2]:

A check constraint on a column or set of columns requires that a specified condition be true or unknown for every row. If DML results in the condition of the constraint evaluating to false, then the SQL statement is rolled back.

References

[1] Oracle - Data Integrity

https://docs.oracle.com/database/121/CNCPT/datainte.htm#CNCPT1660

[2] StackOverflow - Why is this check constraint not working when it checks length?

https://stackoverflow.com/questions/66031098/why-is-this-check-constraint-not-working-when-it-checks-length

jakarta.json.bind.JsonbException: Cannot create instance of a record: class SomeClass, Multiple constructors found.

So I've been trying to use Java Records as data holders for JSONB serialisation and deserialisation. And it works well, until I encountered the following:

jakarta.json.bind.JsonbException: Cannot create instance of a record: class SomeClass, Multiple constructors found.

It turns out, I can put in an annotation @JsonbCreator on it, as explained in reference [1].

It just takes a bit of work, as I cannot put the annotation on top of a record class, it needs to be put on a constructor. The way this works, is to create a compact constructor in the record.

The compact constructor is usually used to put in some sort of validation in the record class upon instantiation, but here we need it for the annotation @JsonbCreate.

Brian Goetz in the comments of reference [2] indicates that this is the proper way to do it.

Unfortunately, it causes one of those "empty constructors" messages of SonarLint.

Warning:(31, 10) Remove this redundant constructor which is the same as a default one.

I assume SonarLint will fix this eventually.

For completeness, my code:

public record AdminItem(Integer id,
                        String belongsto,
                        Long room,
                        String shopkeeper,
                        String owner,
                        LocalDateTime creation)
{

  @JsonbCreator
  public AdminItem
  {
    // empty constructor, because I need to put the annotation @JsonbCreator somewhere.
  }

  public AdminItem(Item item)
  {
    this(item.getId(), 
        item.getBelongsTo() == null ? null : item.getBelongsTo().getName(),
        item.getRoom() == null ? null : item.getRoom().getId(),
        null,
        item.getOwner() == null ? null : item.getOwner().getName(),
        item.getCreation());
  }

}

References

[1] Carlos Chacin - 💾 Java 14 Records 🐞 with JakartaEE JSON-B

https://carloschac.in/2020/04/20/java-records-jsonb/

[2] StackOverflow - Constructor annotation on java records

https://stackoverflow.com/questions/67168624/constructor-annotation-on-java-records