Oracle-DB: Requirements for a multi-column index for protecting foreign key constraints
If you want avoid blocking locks while using referential integrity constraints in an Oracle-DB, it is suggested to protect a foreign key constraint by an index. Not always it is useful and necessary to protect a foreign key constraint with an index. But this question was considered separately in this blog post. The question today is: What column structure must an index have to be accepted by the Oracle DB as protection for a multi-column foreign key constraint? To answer this question, several scenarios are tested. Test setup: CREATE TABLE Dim(ID1 NUMBER, ID2 NUMBER, ID3 NUMBER, Name VARCHAR2(200)); ALTER TABLE Dim ADD CONSTRAINT Dim_PK PRIMARY KEY (ID1, ID2, ID3); INSERT INTO Dim VALUES(1,1,1, 'First'); INSERT INTO Dim VALUES(2,2,2, 'Second'); CREATE TABLE Fact(ID1 NUMBER, ID2 NUMBER, ID3 NUMBER, Name VARCHAR2(200)); ALTER TABLE Fact ADD CONSTRAINT Fact_Dim_FK FOREIGN KEY (ID1, ID2, ID3) REFERENCES Dim(ID1, ID2, ID3); Scenario 1: No index second session is blo...