As my French colleague Dimitri Fontaine was pointing out a few days ago, PostgreSQL 9.2 is out. This is another great release for PostgreSQL, but we are already ahead in the development of the next release: PostgreSQL 9.3.
The Italian team of 2ndQuadrant has been working since last year on adding a new feature to PostgreSQL: support of referential integrity between the elements of an array in a table (referencing) and the records of another table (referenced).
We renamed it “Array ELEMENT foreign keys” – thanks to the feedback received from the hackers list. As you may have guessed, it is not part of the SQL standard. We have submitted a patch for 9.3, but currently it is still missing a reviewer.
This patch is heavily based on the experience we did with the one we submitted for PostgreSQL 9.2 and did not make it. Here we are again, this time with a smaller patch – hoping to improve this feature when (and if) it is accepted in Postgres’ core. Array remove and replace functions, for instance, were part of the original patch and have already been included in 9.3.
This feature adds the ELEMENT REFERENCES column constraint, as well as the array ELEMENT table constraint in foreign keys. Current implementation allows only for NO ACTION and RESTRICT actions, even though specific actions have already been considered and will eventually be included in future releases.
A simple example is probably the easiest way to explain the feature:
[sql]
CREATE TABLE drivers (
driver_id integer PRIMARY KEY,
first_name text,
last_name text,
…
);
CREATE TABLE races (
race_id integer PRIMARY KEY,
title text,
race_day DATE,
…
practice1_positions integer[] ELEMENT REFERENCES drivers,
practice2_positions integer[] ELEMENT REFERENCES drivers,
practice3_positions integer[] ELEMENT REFERENCES drivers,
qualifying_positions integer[] ELEMENT REFERENCES drivers,
final_positions integer[] ELEMENT REFERENCES drivers
);
[/sql]
We have 5 arrays of integers where each element points to a record in the drivers table, and referential integrity is enforced (e.g., we cannot insert in any of those arrays a driver that does not exist). For example, the first element in the final_positions array identifies the driver that won that particular race.
Before this patch, we would have had a junction (linking) table to enforce referential integrity. For example, a table called practice1_positions with:
- race_id (FK)
- position
- driver_id (FK)
Right, we could have kept the model simpler with just one single table, called race_position and another identifying column for the type of position (practice1, practice2, etc.). In any case, the model is still more complex due to the presence of a junction table between races and drivers.
Another important aspect of the array ELEMENT foreign key patch is that the position of the item in the array comes along with implicit – albeit optional – information on ordinality (useful for many use cases). Constraints on the cardinality of the array can be applied too, forcing for example the number of elements (consider the starting lineup of a football/soccer team, which has 11 available slots).
However, what I like the most of this model though is that it allows developers to think in terms of object aggregation even at database level, implementing many-to-many relationships without the need of linking tables – while keeping the same requirements of logical data integrity and consistency.
Apart from the tough challenge of being accepted in the core of PostgreSQL, it would be interesting to see how this feature is taken into consideration by ORMs – but I guess this is just speculation as things stand now.
In any case, I take the chance to invite everyone that wants to contribute to PostgreSQL to join the current commit fest and become a reviewer for this patch.
A very useful resource for new (not only) reviewers is the Wiki of PostgreSQL.