Constraint in a Many-to-Many Relation

A many-to-many relationship between two tables can be challenging to enforce constraints on, especially when those constraints span multiple records. In this article, we’ll explore how to enforce the business rule “A Polygon Must Have At Least Three Sides” using a combination of triggers and materialized views.

Understanding Many-to-Many Relationships

Before we dive into the solution, let’s quickly review what a many-to-many relationship is. It occurs when one table has a foreign key referencing another table, and vice versa. This creates a junction table (also known as an intersection table) that allows for multiple relationships between the two tables.

In our example, we have three tables: POLYGON, VERTEX, and POLYGON_VERTICES. The POLYGON_VERTICES table serves as the junction table, allowing a many-to-many relationship between POLYGON and VERTEX.

Creating the Tables

Let’s create the tables with the necessary constraints:

create table polygon
    (polygon_id number generated always as identity
     , type varchar2(30) not null unique
     , constraint polygon_pk  primary key (polygon_id) )
/
create table vertex
    (vertex_id number generated always as identity
     , side varchar2(10) not null unique
     , constraint vertex_pk  primary key (vertex_id) )
/
create table polygon_vertices
    (polygon_id number not null
     , vertex_id number not null
     , constraint polygon_vertices_uk unique (polygon_id, vertex_id)
     , constraint polygon_vertices_polygon_fk foreign key (polygon_id) references polygon (polygon_id)
     , constraint polygon_vertices_vertex_fk foreign key (vertex_id) references vertex(vertex_id)
     )
/

The Problem: Enforcing the Business Rule

We need to enforce the business rule “A Polygon Must Have At Least Three Sides”. However, this rule spans multiple records in the POLYGON_VERTICES table. We can’t put an aggregation function (like COUNT(*)) directly in a CHECK constraint on POLYGON_VERTICES.

Workarounds: Triggers and Stored Procedures

There are two common workarounds for enforcing constraints on many-to-many relationships:

1. FOR EACH ROW Triggers: We could build a trigger that maintains a list of new POLYGON_ID values in session memory and then uses an AFTER STATEMENT trigger to count the number of VERTEX_ID values associated with each POLYGON_ID. This approach is clunky.
2. Stored Procedures: Another approach would be to use a stored procedure to manage inserts, but this doesn’t help if users can find a way to insert directly into POLYGON_VERTICES.

A Better Approach: Materialized Views

However, there’s an even better approach that’s both safe and transparent: we build an aggregating materialized view over POLYGON_VERTICES and put the check constraint on that.

Creating the Materialized View

First, let’s create a materialized view that counts the number of VERTEX_ID values associated with each POLYGON_ID:

create materialized view pv_sides
refresh on commit 
as 
select polygon_id 
        , count(*) as no_of_sides
from polygon_vertices
group by polygon_id
/

This materialized view, pv_sides, will be updated every time a new row is inserted into POLYGON_VERTICES.

Putting the Check Constraint

Next, let’s add a check constraint to the materialized view:

alter table pv_sides
   add constraint valid_polygon_ck check 
       ( no_of_sides >= 3 )
/

This constraint ensures that the number of VERTEX_ID values associated with each POLYGON_ID is at least 3.

Maintaining the Materialized View

Finally, let’s create a log materialized view to track any changes made to the original tables:

create materialized view log on polygon_vertices
/

This will help us monitor any inserts or updates to the POLYGON_VERTICES table and ensure that our constraint remains enforced.

Conclusion

Enforcing business rules on many-to-many relationships can be challenging, but there are creative solutions available. By building an aggregating materialized view and putting a check constraint on it, we’ve ensured that our polygon relationship has at least three sides. This approach is both safe and transparent, making it the recommended solution for enforcing similar constraints in your own applications.

Additional Considerations

If you’re working with Oracle databases, you may need to use triggers or stored procedures to enforce the business rule. However, using materialized views provides a more elegant and efficient solution.

In addition to this example, consider the following best practices when working with many-to-many relationships:

• Use junction tables (intersection tables) to establish relationships between multiple tables.
• Create materialized views to track aggregated data or perform complex calculations.
• Use check constraints to enforce business rules on your data.
• Monitor and maintain log materialized views to track changes made to the original tables.

Last modified on 2023-12-18