How to have unique constraints on non-null (non-Nothing) rows?

[3noch]

I want to add a uniqueness constraint on a Maybe Text column but my intuition says this would limit me to only one Nothing row. I want to mimic the same behavior as many RDBMSs that consider NULL != NULL and make this trivial. Is this easy to accomplish? If not, would it be possible to provide a convenience API that sets this up since it's a common use case?

[agentm]

This would be certainly possible by modifying the generated inclusion dependency for the uniqueness constraint (see inclusionDependencyForKey). A new function for Maybe a types would likely be of general interest.

But, if you tell us more about your model and use case, I might be able to offer a better solution.

[agentm]

One alternative to Maybe-emulating-NULL is to create a relation-valued-attribute with a constraint that ensures that the nested relation has zero-or-one tuples and non-empty nested relations must be unique. While this may be purer in the relational sense, it would probably not be simpler to implement.

[agentm]

This is accomplished with just a small adjustment to the existing uniqueness (key) constraint. First, let us consider a contrived example:

person := relation{name Text, boss Maybe Text}

We wish to add a constraint so that each boss is referenced only once in this relation variable; that is, each boss may have only one subordinate. (I did say this is contrived- a better example would be welcomed.)

The following constraint states that the count for each of the unique boss names must be equal to the count of tuples which are non-Nothing.

constraint uniqueJust ((relation{tuple{}}:{a:=person where ^isJust(@boss)}):{b:=count(@a)}){b} equals ((relation{tuple{}}:{a:=person{boss} where ^isJust(@boss)}):{b:=count(@a)}){b}

For testing:

person := relation{name Text, boss Maybe Text}{tuple{name "Steve",boss Nothing}, tuple{name "Bob", boss Just "Steve"}, tuple{name "Jim", boss Nothing}} --OK
person := relation{name Text, boss Maybe Text}{tuple{name "Steve",boss Nothing}, tuple{name "Bob", boss Just "Steve"}, tuple{name "Jim", boss Just "Steve"}} --constraint violation- Steve has two underlings
ERR: InclusionDependencyCheckError "uniqueJust"

[3noch]

Perfect! This is truly a powerful constraint system.

[agentm]

Inclusion dependencies are indeed powerful! They have been shown to be able to represent any relational algebra (non-type-level) constraint that a relation algebra system could possibly have.