Mandatory and Optional Participation in ER Diagrams

Reading the diagrams

Start with one instance of an entity and read the symbols at the opposite end to count its related instances. The markers next to PRODUCT count products for one supplier; the markers next to SUPPLIER count suppliers for one product.

• || next to an entity means “exactly one” (mandatory one).
• O| means “zero or one”.
• O< means “zero or many”.
• |< means “one or many”.

Examples with participation

Implementation patterns

The following examples use Oracle SQL syntax and are illustrative; they have not been executed against a live Oracle database in this revision.

1) One-to-one

Shared primary key (tight coupling):

CREATE TABLE employee (
  emp_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  last_name  VARCHAR2(60) NOT NULL,
  first_name VARCHAR2(60) NOT NULL
);

CREATE TABLE office (
  emp_id   NUMBER PRIMARY KEY,             -- shared PK enforces 1:1
  room_no  VARCHAR2(20) NOT NULL,
  CONSTRAINT fk_office_emp
    FOREIGN KEY (emp_id) REFERENCES employee(emp_id)
);

Unique foreign key (looser coupling):

CREATE TABLE office_uq (
  office_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  emp_id    NUMBER UNIQUE NOT NULL,        -- one office per employee
  room_no   VARCHAR2(20) NOT NULL,
  CONSTRAINT fk_office_uq_emp
    FOREIGN KEY (emp_id) REFERENCES employee(emp_id)
);

Both one-to-one patterns allow an employee to have no office while requiring every office row to reference exactly one employee. They implement the optional-office rule shown in the first diagram. A different rule requiring every employee to have an office would need additional enforcement.

2) One-to-many

CREATE TABLE supplier (
  supplier_id   NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  supplier_code VARCHAR2(20) UNIQUE NOT NULL,
  name          VARCHAR2(120) NOT NULL
);

CREATE TABLE product (
  product_id  NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  supplier_id NUMBER NOT NULL,             -- mandatory one
  sku         VARCHAR2(40) UNIQUE NOT NULL,
  title       VARCHAR2(120) NOT NULL,
  CONSTRAINT fk_product_supplier
    FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id)
);

3) Many-to-many (resolved)

CREATE TABLE student (
  stud_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  student_no  VARCHAR2(20) UNIQUE NOT NULL,
  last_name   VARCHAR2(60) NOT NULL
);

CREATE TABLE class (
  class_id    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  class_code  VARCHAR2(20) UNIQUE NOT NULL,
  title       VARCHAR2(120) NOT NULL
);

CREATE TABLE enrollment (
  stud_id     NUMBER NOT NULL,
  class_id    NUMBER NOT NULL,
  enrolled_on DATE DEFAULT SYSDATE,
  grade       VARCHAR2(2),
  CONSTRAINT pk_enrollment PRIMARY KEY (stud_id, class_id),
  CONSTRAINT fk_enr_stud  FOREIGN KEY (stud_id)  REFERENCES student(stud_id),
  CONSTRAINT fk_enr_class FOREIGN KEY (class_id) REFERENCES class(class_id)
);

Participation vs. identifying relationships

Don’t conflate mandatory with identifying. Mandatory means the minimum is at least one; identifying means the parent’s PK participates in the child’s PK. A child with its own primary key can have a mandatory, non-identifying foreign key. If the parent key is part of the child primary key, that child cannot omit the parent because primary-key columns cannot be NULL. The parent may still have zero children; always state which direction is optional.

Referential actions on delete

Referential actions differ by database. Oracle supports ON DELETE CASCADE and ON DELETE SET NULL; omitting ON DELETE prevents deletion of a referenced parent. The list below describes common SQL choices across products:

• NO ACTION / RESTRICT: prevent parent delete if children exist.
• CASCADE: delete children automatically.
• SET NULL: set the FK to NULL (FK must allow NULLs).
• SET DEFAULT: set the FK to its DEFAULT value.

-- Alternative business rule: a product may temporarily have no supplier.
ALTER TABLE product DROP CONSTRAINT fk_product_supplier;
ALTER TABLE product MODIFY (supplier_id NULL);

ALTER TABLE product
  ADD CONSTRAINT fk_product_supplier
  FOREIGN KEY (supplier_id)
  REFERENCES supplier(supplier_id)
  ON DELETE SET NULL;  -- example: product may temporarily have no supplier

This alternative deliberately changes product participation from mandatory to optional. Dropping the original constraint and allowing NULL are necessary before replacing the foreign key with ON DELETE SET NULL.

Quick self-check

Given SUPPLIER ||-----O< PRODUCT, answer:

1. Is a product required to have a supplier? (Yes: the two bars near SUPPLIER specify exactly one supplier per product.)
2. Can a supplier have zero products? (Yes: the circle and crow's foot near PRODUCT specify zero or many products per supplier.)

ER Diagram Participation - Exercise