CODEWITHSUNDEEP
oracle-databaseforeign-keysschema
Adder
Adder

Reputation: 5868

Creating foreign keys on a table fails in Oracle and 2 schemas

I have two schemas, and I am trying to create a table with two foreign key constraint. Creating the foreign key constraint does not work regardless of whether I add the constraint separately or in the table creation DDL. Also regardless of which of the two users I try to run it. The oracle error is ORA-01031: insufficient privileges. The table is created when I omit the foreign key constraints.

Intended result: create a table with two constraints. 

CREATE TABLE "XXX_MONITORING"."COMPOSITE_STATUS"
   (    "COMPOSITE_STATUS_ID" NUMBER,
    "COMPOSITE_ID" NUMBER,
    "STATUS" CHAR(1),
    CONSTRAINT "COMPOSITE_FK" FOREIGN KEY ("COMPOSITE_ID")
      REFERENCES "XXX_MONITORING_CONFIGURATION"."COMPOSITE_KPI_COMPONENTS" ("COMPONENT_ID") ON DELETE CASCADE ENABLE,
    CONSTRAINT "COMPOSITE_STATUS_FK" FOREIGN KEY ("STATUS")
      REFERENCES "XXX_MONITORING_CONFIGURATION"."INDICATION_COLOR" ("INDICATION_COLOR_ID") ON DELETE CASCADE ENABLE  
   );

Upvotes: 0

Views: 47

Answers (1)

Popeye
Popeye

Reputation: 35900

The table COMPOSITE_KPI_COMPONENTS and INDICATION_COLOR are in different schema i.e. XXX_MONITORING_CONFIGURATION.

That user must grant REFERENCES on COMPOSITE_KPI_COMPONENTS and INDICATION_COLOR to XXX_MONITORING.

-- Grant statement

grant REFERENCES on COMPOSITE_KPI_COMPONENTS to XXX_MONITORING;
grant REFERENCES on INDICATION_COLOR to XXX_MONITORING;

Upvotes: 2

Related Questions