Modeling One-to-Constant Relationship

Question

Can a One-To-Constant Relationship be completely modeled in Oracle with constraints? In other words, the PARENT entity ALWAYS has EXACTLY n-CHILDREN of the child entity, and each child only has one parent.

Consider n to be a database constant.

Answer 1

I don't see how. It is the old question "which came first, the chicken or the egg?". How can you constrain the parent when no children have been added yet, and how can you add children without a parent?

you could create a new table, called something like "ValidParents" that only has the parent IDs that have N children, and keep it in sync with triggers.

Answer 2

This may not be what you want, but I do have one method that does something similar.

The usual arrangement for one-to-many is something like this:

Primary Table:
primary_id (PK)
primary_stuff

Secondary Table:
secondary_id (PK)
primary_id (FK)
secondary_stuff

The alternative, to model a strict one-to-one would be:

Primary Table:
primary_id (PK)
secondary_id (FK, non-null)
primary_stuff

Secondary Table:
secondary_id (PK)
secondary_stuff

It might be a bit strange, but it works. A variation of this may be useful where there's a one-to-many with a one-to-one in it, such as having multiple addresses for a customer, but exactly one billing address.

Answer 3

Building upon the earler "chicken + egg" points, you can create deferrable constraints which aren't validated until commit time... these might help?

e.g.

ALTER TABLE AGREEMENTS ADD CONSTRAINT name FOREIGN KEY (column) REFERENCES table (column) DEFERRABLE INITIALLY DEFERRED;

Answer 4

Doing this so that it is sound and correct even when multiple sessions are doing updates is not easy. You will get yourself in a mess if you try this with triggers, and Oracle's declarative constraints are not powerful enough to express this.

It can be done as follows:-

1. Create a materialized view log on both the parent and the child tables
2. Create a materialized join view that joins them together and counts the number of children grouped by the parent. This must be REFRESH FAST ON COMMIT
3. Put a constraint on the materialized join view that the count of child records must equal "n" (your database constant)

You can then do a series of insert/update/delete statements. When you commit, the materialized view will refresh and if the condition is not met you will get a constraint violation error at that point.

A bonus bit of trickery is to only include rows that fail the constraint into the materialized view (HAVING count(ChildId) <> 5) so you do not waste any storage space.

Answer 5

An alternative solutionb to the chicken and egg problem is to use INSERT ALL. Because it is a single statement it obviates the need for deferrable foreign key constraints. It also provides a mechanism for inserting an exact number of dependent rows. Additional constraints prevent the insertion of additional rows. But we need a subsidiary table with foreign keys to prevent the accidental deletion of the rows of interest.

In this example, n = 3.

SQL> create table parent
  2   ( pk_col number not null
  3     , col1 varchar2(20)
  4     , constraint par_pk primary key (pk_col)
  5    )
  6  /

Table created.

SQL> 
SQL> create table child
  2   ( pk_col number not null
  3     , seqno number(1,0) not null
  4     , col2 date
  5     , constraint ch_pk primary key
  6          (pk_col, seqno)
  7     , constraint ch_par_fk foreign key
  8          (pk_col) references parent (pk_col)
  9     , constraint ch_ck check (seqno between 1 and 3)
 10    )
 11  /

Table created.

SQL> 
SQL> create table child_lock
  2   ( pk_col_1 number not null
  3     , seqno_1 number(1,0) not null
  4     , pk_col_2 number not null
  5     , seqno_2 number(1,0) not null
  6     , pk_col_3 number not null
  7     , seqno_3 number(1,0) not null
  8     , constraint chlk_pk primary key
  9          (pk_col_1, seqno_1, pk_col_2, seqno_2, pk_col_3, seqno_3)
 10     , constraint chlk_par_1_fk foreign key
 11          (pk_col_1, seqno_1) references child (pk_col, seqno)
 12     , constraint chlk_par_2_fk foreign key
 13          (pk_col_2, seqno_2) references child (pk_col, seqno)
 14     , constraint chlk_par_3_fk foreign key
 15          (pk_col_3, seqno_3) references child (pk_col, seqno)
 16    )
 17  /

Table created.

SQL> 
SQL> insert all
  2      into parent values (pk_val, val_1)
  3      into child values (pk_val, 1, sysdate)
  4      into child values (pk_val, 2, sysdate+1)
  5      into child values (pk_val, 3, sysdate+2)
  6      into child_lock values (pk_val, 1, pk_val, 2, pk_val, 3)
  7  select 999 as pk_val
  8         , 'APPLE PIE' as val_1
  9  from dual
 10  /

5 rows created.

SQL> 
SQL> insert into child values (999, 4, sysdate+4)
  2  /
insert into child values (999, 4, sysdate+4)
*
ERROR at line 1:
ORA-02290: check constraint (APC.CH_CK) violated 


SQL> insert into child values (999, 3, sysdate+4)
  2  /
insert into child values (999, 3, sysdate+4)
*
ERROR at line 1:
ORA-00001: unique constraint (APC.CH_PK) violated 


SQL> insert into child values (999, 2.5, sysdate+4)
  2  /
insert into child values (999, 2.5, sysdate+4)
*
ERROR at line 1:
ORA-00001: unique constraint (APC.CH_PK) violated 


SQL> delete from child
  2  /
delete from child
*
ERROR at line 1:
ORA-02292: integrity constraint (APC.CHLK_PAR_1_FK) violated - child record found 


SQL>

I accept the solution is a trifle contrived and also inflexible, but then so is the original requirement. It is also far from bulletproof - delete the row from CHILD_LOCK and you can delete one or more CHILD records.

Answer 6

You can create your tables as normal with a 1:M relationship, then on the child table have a count column with a check constraint that determines how many children can exist for a parent, as well as a unique constraint over the Parent ID + count column. e.g.:

CREATE TABLE Parent (PID NUMBER PRIMARY KEY);

CREATE TABLE Child (
    PID NUMBER NOT NULL,
    Count NUMBER(1) NOT NULL,
    CONSTRAINT count_check CHECK (Count BETWEEN 1 AND 5),
    CONSTRAINT parent_child_fk FOREIGN KEY (PID) REFERENCES Parent (PID),
    CONSTRAINT count_unique UNIQUE (PID, Count));

The only thing this doesn't guarantee is that for each parent there are AT LEAST five children; to get around this you might create a materialized view with a constraint as WW suggests, or build something extra in the application (e.g. an "error" report).

Answer 7

There is an alternative solution to force each parent to have exactly either 0 or n children without materialized views using just check, foreign key and uniqueness constraints. For this, one has to number the children and add a field containing the number of the next sibling. Here an example for n=5 that works in PostgreSQL, for other DBS one has to adapt probably the type serial:

create table Tree(
  id serial,
  parent_id integer not null references Tree(id),
  child_nr integer check(child_nr between 1 and 5),
  next_sibling_nr integer,
  unique (parent_id, child_nr),
  check(next_sibling_nr in (child_nr+1, child_nr-4)),
  check(((parent_id is null) and (child_nr is null) and
    (next_sibling_nr is null)) or ((parent_id is not null)
    and (child_nr is not null) and (next_sibling_nr is not null))),
  foreign key (parent_id, next_sibling_nr) references Tree(parent_id, child_nr),
  primary key (id)
);

The last (long) check constraint ensures that the fields parent_id, child_nr and next_sibling_nr are all null or all not null. The uniqueness constraint and the check for the child_nr field take care that a parent has at most 5 children. The other check constraint and the foreign key constraint on the pair (parent_id, next_sibling_nr) ensure that there are not less than 5 children.

After inserting a root with automatically generated id 1 with the command

insert into Tree (parent_id)
  values (null);

one can add children always in packs of 5:

insert into Tree (parent_id, child_nr, next_sibling_nr)
  values (1, 1, 2),
         (1, 2, 3),
         (1, 3, 4),
         (1, 4, 5),
         (1, 5, 1);

This solution is derived from the answers to a similar question I asked some weeks ago.