使用遞歸表強制數據完整性

我有一個現有的 oracle 11g 數據庫模式，可與 Web 應用程序一起使用。我正在計劃擴展應用程序，以便 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

不知道這對您是否有用，因為它需要進行很多更改，但是這個問題很有趣，所以我會嘗試。

這些將是主要的變化

• 使用樹閉包而不是引用層次結構的鄰接列表。閉包表包含從每個父節點到所有子節點的路徑，因此所有可能的父子組合都被公開。

請注意，使用樹閉包，每個祖先節點都將其自身指向為後代，這意味著在CaseProperty遞歸中停止ID = ParentID而不是在ParentID is NULL

我不清楚父母是否允許成為任何祖先或只是第一步。閉包表暴露了祖先和所有後代，因此Level Difference被添加到TreeClosure作為子類型的AllowedCombosfor 中LevelDifference in (0,1)。

• 傳播 AK{PropertyID, PropertyTypeID} 而不是僅僅傳播PropertyID
• 使用複合鍵CaseProperty

---

---

以下是模型中用於闡明關係的主要約束（您可能需要修改語法）

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)

;

引用自：https://dba.stackexchange.com/questions/36698