Using postgresql Constraints in Relations

usr338

Hello, I think it is necessary to have a way to apply check and unique and even exclude constraints to relations.

Martin

Unique you can set if you create an unique index on the field.

For any other checks, I believe the way to do that is have a backend trigger function before insert/update to perform business logic checks and call instance.abort_show_message, if your check fails.

gabriel

Martin Unique you can set if you create an unique index on the field.

For any other checks, I believe the way to do that is have a backend trigger function before insert/update to perform business logic checks and call instance.abort_show_message, if your check fails.

I was just about to reply as well - this is the way :)
Unique indexes can span multiple attributes as well - so you can have singular or composite keys.

usr338

gabriel
Thank you, Martin and Gabriel, for response but there are some slight difference.
a unique constraint is a high-level concept that enforces the uniqueness of values in a column or group of columns. A unique index, on the other hand, is a lower-level concept that also enforces uniqueness but is primarily used to speed up search queries.
Unique indexes on nullable columns in PostgreSQL allow multiple null values, as nulls are considered distinct from each other. This means you can have several rows with null values in the indexed column without violating the uniqueness constraint.(i believe).
And for other constraints , i think it is slower than database native implementation of them,but for now there is no other option.

gabriel

What we discussed it what you can do right now. You can enforce different types of uniqueness with triggers including how to deal with NULLs - but while they are also executed by the RDBMS, I would assume that they are less-performant than constraints.

It´s not planned currently to add more low-level DB features, like constraints at the moment. We have many more lower hanging fruits to pick at the moment.

Martin

My understanding:

Unique Index: NULLs are considered distinct. (Unless you provide the NULLS NOT DISTINCT option to the index not possible in REI3)

Unique Constraint: NULLs are considered distinct.

So both options give the same result, no?

From the Postgresql doc:

In general, a unique constraint is violated if there is more than one row in the table where the values of all of the columns included in the constraint are equal. However, two null values are never considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases might not follow this rule. So be careful when developing applications that are intended to be portable.

Martin

Also interesting:

PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.

So even under the hood there is no difference, it seems.

usr338

Thank you both for answer, but i think constraints option in Rei3 is essential still, for example what if i want to have check constraints on 2 field to not be the null same time.(one of them should always have value). that is needed in polymorphic associations.and many case like this. however it is possible using triggers but may be(I don't know) it be a bottleneck in tables with a lot of records.

gabriel

I did not say that we won´t do it - just that it´s not a priority at the moment.

As far as I know, it has not been essential so far. I have yet to see an app that had issues because of this kind of performance bottle neck. We have other, more pressing issues to deal with currently.

If you or someone else runs into this issues, we will take it seriously and re-prioritize accordingly.

rfernandez

Hi,

Sorry to break into this party 🙂
I have this situation in which I have 3 tables (A B C) related to the same table D, but by definition only one of them could be related to the same D record at a time. So I need to implement a constraint for managing this restriction.
And this is more or less how I would do it in a standard situation.

Table D:

id_D (PK)
D_type (CHECK: 'A', 'B', 'C')
id_A (FK, nullable)
id_B (FK, nullable)
id_C(FK, nullable)

-- Constraint: exactly one FK must have a value
CHECK ((id_A IS NOT NULL)::int +
(id_B IS NOT NULL)::int +
(id_C IS NOT NULL)::int = 1)

Has there been any improvements on this direction, that allow to define such thing inside r3 at the relation level?
Otherwise how would you suggest I proceed to fulfill this requirement? Is the backend trigger function still the suggested way to go?

Thank you!!

gabriel

rfernandez Is the backend trigger function still the suggested way to go?

Yes, this is currently the suggested way to go. Via trigger you can check local (ie. relation-specific constraints) as well as non-local constraints, based on conditions from data outside the current relation.

Implementing simple check constraints is on our wishlist but not prioritized atm, as it would be less powerfull than what you can do right now. In addition, authors would need to check 2 places for potential conditions (local check constraints and triggers) when they need to trouble shoot, making this less straight forward.