使用递归表强制数据完整性

我有一个适用于 Web 应用程序的现有 oracle 11g 数据库模式。我正计划对应用程序进行扩展，以便 Web 服务可以对数据库进行数据操作。作为计划的一部分，我已经意识到没有对父/子关系的数据完整性检查，这会使其他应用程序使用该表成为问题。我计划在 Web 服务中进行验证，但最佳做法是在数据库和 Web 服务中进行验证。

--the base lookup table has a table with text values that is not shown.
--Example Red, Green, 
CREATE TABLE PROPERTY
(
  ID                        NUMBER(9)           NOT NULL, --PRIMARY KEY
  TENANT_ID                 NUMBER(9)           NOT NULL
)
-- a property may or may not have a parent property. 
--Example "Weight" of an item is a child of the "Shipping Weight"
CREATE TABLE PROPERTY_DEPENDENCY  --PRIMARY KEY PROPERTY_ID,PROPERTY_TYPE_ID
(
  PROPERTY_ID               NUMBER(9)           NOT NULL,
  PARENT_PROPERTY_ID        NUMBER(9),
  PROPERTY_TYPE_ID          NUMBER(9)           NOT NULL,
  ACTIVE                    NUMBER(1)           NOT NULL
)
--examples "Item Colour", "Item Trim Colour","Shipping Weight", "Weight"
CREATE TABLE PROPERTY_TYPE
(
  ID                        NUMBER(9)           NOT NULL,  --PRIMARY KEY
  VALUE                     VARCHAR2(200 BYTE)  NOT NULL,
  PROPERTY_TYPE             NUMBER(10)          DEFAULT 1   NOT NULL
)

--and the table that you insert and update into
CREATE TABLE CASE_PROPERTY
(
  ID                        NUMBER(9)           NOT NULL, --PRIMARY KEY
  PARENT_ID                 NUMBER(9),          --constraint on PROPERTY
  CASE_ID                   NUMBER(9)           NOT NULL,--foreign key
  PROPERTY_ID               NUMBER(9),          --constraint on PROPERTY
  PROPERTY_TYPE_ID          NUMBER(9)           NOT NULL --constraint on PROPERTY_TYPE
)

这些是我发现的问题：

• 您可以插入到 CASE_PROPERTY 中并使属性成为自己的父母或祖父母
• 您可以将 PROPERTY_ID 的错误 PROPERTY_TYPE_ID 插入 CASE_PROPERTY
• 您可以在 CASE_PROPERTY 中插入一个 PARENT_ID，这对 PROPERTY_TYPE_ID 没有意义

我可以添加一个检查约束，PARENT_ID <> PROPERTY_ID这样你就不能成为自己的父母。

编辑 3： 真正的问题是表格没有正确规范化，这对报告非常有用，但很难进行数据验证。CASE_PROPERTY.PROPERTY_TYPE_ID应该始终与中的值相同，PROPERTY_DEPENDENCY.PROPERTY_TYPE_ID但我不知道如何验证这一点。

除了触发器之外，还有其他方法可以强制执行数据完整性CASE_PROPERTY吗？

编辑：我会整理一个完整的例子。如果我在上添加外键约束，PROPERTY_DEPENDENCY我将验证是否只插入了具有父项的属性，但它们是正确的父项吗？

编辑 2：这是允许插入的完整示例。最后两个插入是允许但不应插入的数据示例。

ALTER TABLE CASE_PROPERTY ADD  CONSTRAINT CASE_PROPERTY_R01  FOREIGN
KEY (PARENT_ID)  REFERENCES CASE_PROPERTY (ID)  ENABLE  VALIDATE

Insert into PROPERTY    (ID, TENANT_ID)  Values    (2, 1); 
Insert into PROPERTY    (ID, TENANT_ID)  Values    (3, 1); 
Insert into PROPERTY    (ID, TENANT_ID)  Values    (4, 1); 

Insert into PROPERTY_TYPE    (ID, 
    VALUE, PROPERTY_TYPE)  Values    (10, 'Colour', 2);    
Insert into PROPERTY_TYPE    (ID, 
    VALUE, PROPERTY_TYPE)  Values    (11, 'Trim Colour', 1);    
Insert into PROPERTY_TYPE    (ID, 
    VALUE, PROPERTY_TYPE)  Values    (12, 'Shipping Weight', 1); 
Insert into PROPERTY_TYPE    (ID, 
    VALUE, PROPERTY_TYPE)  Values    (13, 'Weight', 3); 

Insert into PROPERTY_DEPENDENCY    (PROPERTY_ID, 
    PARENT_PROPERTY_ID, PROPERTY_TYPE_ID)  Values    (4, 3, 11); 
Insert into PROPERTY_DEPENDENCY    (PROPERTY_ID, 
    PARENT_PROPERTY_ID, PROPERTY_TYPE_ID)  Values    (3, NULL, 10);
Insert into PROPERTY_DEPENDENCY    (PROPERTY_ID, 
    PARENT_PROPERTY_ID, PROPERTY_TYPE_ID)  Values    (1, NULL, 12);    
Insert into PROPERTY_DEPENDENCY    (PROPERTY_ID, 
    PARENT_PROPERTY_ID, PROPERTY_TYPE_ID)  Values    (2, 1, 13); 

--example of a property validated data insert

--item 201 with type 13 is the child of item 200 of type 12
Insert into CASE_PROPERTY    (ID, 
    PARENT_ID, CASE_ID, PROPERTY_ID, PROPERTY_TYPE_ID)  Values    (200, NULL, 3000, 1, 12); 
Insert into CASE_PROPERTY    (ID, 
    PARENT_ID, CASE_ID, PROPERTY_ID, PROPERTY_TYPE_ID)  Values    (201, 200, 3000, 2, 13); 

--bad data inserts

-- a property is parent to itself with an incorrect property_type_id 

Insert into CASE_PROPERTY    (ID, 
    PARENT_ID, CASE_ID, PROPERTY_ID, PROPERTY_TYPE_ID)  Values    (202, 202, 4000, 3, 10);  
--should be 202, null,4000,3,10 

--a property is inserted with a parent that is not allowed 

Insert into CASE_PROPERTY    (ID, 
    PARENT_ID, CASE_ID, PROPERTY_ID, PROPERTY_TYPE_ID)  Values    (203, 200, 4000, 2, 13);  
--parent property should be 1 not 2