1

I'm trying to solve a problem. I have a table POLYGON and a table VERTEX; every polygon can be associated to a minimum of 3 or more vertices, every vertices can be associated to 1 or more polygons. Since it is a many to many relation I created another table that contain polygon's primary key and vertex primary key. Now how can I add the constraint that the polygon must have 3 or more vertices?

ATTENTION: It is not 0..3 multiplicity but 3..*. I didn't find any question with the same problem

Diablo3000
  • 67
  • 2
  • 8
  • 1
    Unfortunately, I think you need to use a trigger for this. – Gordon Linoff Feb 02 '18 at 18:58
  • Yes, but i don't know how to set it because if i check before inserting if there are 3 or less vertices then i can't make the insertion possible. – Diablo3000 Feb 02 '18 at 19:01
  • @apc This question is re a minimum of 3, requiring a different answer than for a maximum of 3. The alleged duplicate isn't clear so should not be used as a duplicate. Moreover it is re either exactly or maximum 3 and only if it is ever clarified to exactly 3 could it be a duplicate. Moreover the the asker says "restrict to 0,3" in the comments, so if it's ever edited to be clear it will not be a duplicate. – philipxy Feb 02 '18 at 20:35
  • I still believe this question is a duplicate of [this other StackOverflow thread](https://stackoverflow.com/a/47280427/146325). However, some people including the OP have objected that is is not an exact duplicate so I have reopened it, and also answered it. – APC Feb 03 '18 at 09:24

1 Answers1

2

There are three tables, POLYGON, VERTEX and POLYGON_VERTICES. POLYGON_VERTICES is an intersection table allowing a many-to-many relationship between POLYGON and VERTEX.

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)
     )
/

We need to enforce a business rule, A Polygon Must Have At Least Three Sides. To do this we must count the number of VERTEX_ID for a given POLYGON_ID, which means the rule spans multiple records. The term for this is Assertion, which is part of the SQL standard but not (yet) implemented in Oracle. So there is no easy way of enforcing the role declaratively; we can't put an aggregation in a CHECK constraint on POLYGON_VERTICES.

There are various workarounds. We could build a FOR EACH ROW trigger to maintain a list of the new POLYGON_IDs in session memory and an AFTER STATEMENT trigger to do the count and enforce the rule. That is clunky. We could use a stored procedure to manage the inserts. That doesn't help if users can find a way to insert directly into POLYGON_VERTICES.

But there is an approach which is both safe and transparent: we build an aggregating materialized view over POLYGON_VERTICES and put the check constraint on that:

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

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

create materialized view log on polygon_vertices
/

Here is a LiveSQL Demo of the full script (free OTN login required).

APC
  • 144,005
  • 19
  • 170
  • 281