How to enforce full integer (no floating point)

Do not know if this will be useful to you because it requires quite a few changes, but the problem is interesting, so I'll try.

These would be the major changes

• Using the tree closure instead of the adjacency list for the reference hierarchy. The closure table contains paths form each parent to all of it descendants, so all possible parent-child combinations are exposed.

Note that with the tree closure, each ancestor node points to itself as a descendant, meaning that in CaseProperty recursion stops on ID = ParentID instead on ParentID is NULL

It is not clear to me is a parent allowed to be any ancestor or just the one first step up. The closure table exposes ancestor and all descendants, so Level Difference is added to the TreeClosure, which is sub-typed as AllowedCombos for LevelDifference in (0,1).

• Propagating AK {PropertyID, PropertyTypeID} instead of just PropertyID

• Using composite key in CaseProperty

Here are main constraints from the model to clarify relationships (you may need to modify syntax)

ALTER TABLE Property ADD
  CONSTRAINT PK_PR  PRIMARY KEY (PropertyID)

, CONSTRAINT AK1_PR UNIQUE (PropertyID ,PropertyTypeID)

, CONSTRAINT FK1_PR FOREIGN KEY (PropertyTypeID)
         REFERENCES PropertyType(PropertyTypeID)
;



ALTER TABLE TreeClosure ADD
  CONSTRAINT PK_TC PRIMARY KEY (AncestorID ,DescendantID ,AncestorTypeID ,DescendantTypeID)

, CONSTRAINT FK1_TC FOREIGN KEY (AncestorID ,AncestorTypeID) 
             REFERENCES Property(PropertyID ,PropertyTypeID)

, CONSTRAINT FK2_TC FOREIGN KEY (DescendantID ,DescendantTypeID)
             REFERENCES Property(PropertyID   ,PropertyTypeID)
;



ALTER TABLE CaseProperty ADD
  CONSTRAINT PK_CP PRIMARY KEY (CaseID, PropertyID, PropertyTypeID)

, CONSTRAINT FK1_CP FOREIGN KEY (CaseID)
                 REFERENCES Case(CaseID)

, CONSTRAINT FK2_CP FOREIGN KEY (PropertyID ,PropertyTypeID)
             REFERENCES Property(PropertyID ,PropertyTypeID)

, CONSTRAINT FK4_CP FOREIGN KEY (ParentCaseID ,ParentPropertyID ,ParentPropertyTypeID)
         REFERENCES CaseProperty(CaseID       ,PropertyID       ,PropertyTypeID)

, CONSTRAINT FK5_CP FOREIGN KEY (ParentPropertyID ,PropertyID   , ParentPropertyTypeID ,PropertyTypeID) 
        REFERENCES AllowedCombos(AncestorID       ,DescendantID , AncestorTypeID       ,DescendantTypeID)

;

I'd think it's probably not a best practice to use EXCEPTION to do logic/handling, such as duplicate keys. Here's an idea: write a stored proc. Also create a sequence that has your alternate values that will be used in cases of duplicate keys. You might want to start this sequence with a fairly high value like 1,000,000 (or much higher, just depends on your actual data). Then do your inserts via this stored proc whenever you want the dupe-sub functionality.

CREATE TABLE components (
    id_component INTEGER PRIMARY KEY
  , code         VARCHAR2(255) 
  , description  VARCHAR2(255)
  , model        VARCHAR2(255)
  , resp         VARCHAR2(255)
);

CREATE SEQUENCE components_alt_id_seq
  START WITH 1000000;

CREATE OR REPLACE PROCEDURE component_ins
  (  p_id_component IN components.id_component%TYPE
   , p_code         IN components.code%TYPE 
   , p_description  IN components.description%TYPE 
   , p_model        IN components.model%TYPE 
   , p_resp         IN components.resp%TYPE )
AS
  v_is_duplicate INTEGER;
BEGIN
  SELECT count(*) INTO v_is_duplicate FROM components WHERE id_component = p_id_component;
  IF v_duplicate = 0 THEN
    INSERT INTO components (id_component, code, description, model, resp)
      VALUES (p_id_component, p_code, p_description, p_model, p_resp);
  ELSE
    INSERT INTO components (id_component, code, description, model, resp)
      VALUES (components_alt_id_seq.NEXTVAL, p_code, p_description, p_model, p_resp);
  END IF;
END;
/

EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39823, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39824, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (40015, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (40016, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')

And then checked the result. Notice that the duplicate input values were automatically subbed with a value taken from the sequence.

SELECT id_component FROM components;

ID_COMPONENT
------------
       39822
       39823
       39824
       40015
       40016
     1000005
     1000006

7 rows selected.