Constraints are used to ensure that data in the database reflects the assumptions of the data model.
- Do foreign keys match up to corresponding keys? (
- Are mandatory columns filled in? (
- Are unique values columns in fact unique? (
- Do other data quality rules pass? (
Why enforce data quality rules in the database, and not at the application tier? Because if you qualify all your data at the application tier, you have to duplicate all that logic whenever you add another application that writes to the database.
If you are lucky enough to write a system successful enough to be in production for more than a couple years, the odds of multiple write applications proliferating rapidly converge to 100%. Enforcing core data quality rules in the database is a nice way to ground your system with solid assumptions about data integrity.
Our interactive learning portal has a whole section on the use of non-spatial constraints, if you're interested. There's also a video.
What about spatial, though? Thanks to the magic of PostgreSQL run-time
geometry type is just another type and all the machinery of
constraint checking works just as well for
geometry as any other type.
Here are a few examples of simple and complex constraints that spatial database users like to enforce on the geometry data.
PostgreSQL column level constraints allow us to apply tests to data, but only at a granular level: the check can only operate on the incoming value, so we are restricted to relatively simple tests that check the properties of a single geometry.
CREATE TABLE parcels ( pk bigint PRIMARY KEY, geom geometry(Polygon, 3005) NOT NULL CHECK (ST_IsValid(geom)), pid text NOT NULL UNIQUE )
An "invalid" polygon is one that has:
- A self-intersecting ring; or
- An interior ring that shares a boundary with an exterior ring; or
- An interior ring that is outside the exterior ring.
Here are some invalid polygons.
With our check constraint in place, inserts of valid polygons succeed.
INSERT INTO parcels (pk, pid, geom) VALUES (1, '12345', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))' );
And inserts of invalid polygons fail.
INSERT INTO parcels (pk, pid, geom) VALUES (2, '23456', 'POLYGON((160 170, 160 280, 213 315, 310 280, 300 140, 210 220, 280 190, 160 170))' );
NOTICE: Self-intersection at or near point 249.47368421052633 184.91228070175438 ERROR: new row for relation "parcels" violates check constraint "parcels_geom_check" DETAIL: Failing row contains (2, 0103000020BD0B00000100000008000000000000000000644000000000004065..., 23456).
Spatial constraints can also be used to enforce common-sense business rules, just as with non-spatial constraints. For example:
CHECK ST_Area(geom) > 10to enforce a minimum polygon area. Surveyed parcels might have a minimum allowed size.
CHECK ST_Length(geom) > 1to enforce a minimum line length. A road network with a 1 metre accuracy should not have any segments shorter than 1 metre.
CHECK ST_IsSimple(geom)to ensure lines do not self-intersect. A noded network should not have self-intersecting elements.
While check constraints are simple and easy to understand, spatial data calls out for more powerful tests of quality, that involve looking at structural relationships between geometries in a table, and between tables.
For that, we will need table-level constraints and triggers, which we will discuss in the next installment of this series.
April 20, 2020 •More by this author