Privileges Required to Create FOREIGN KEY Integrity Constraints

Got an ORA-01031: insufficient privileges when trying as system to create a foreign key constraint columns of tables in two different schemas. Turns out there's a REFERENCES privilege ... who knew!


From Oracle docs ...


Privileges Required to Create FOREIGN KEY Integrity Constraints

To create a FOREIGN KEY constraint, the creator of the constraint must have privileged access to both the parent and the child table.

The Parent Table The creator of the referential integrity constraint must own the parent table or have REFERENCES object privileges on the columns that constitute the parent key of the parent table.
The Child Table The creator of the referential integrity constraint must have the ability to create tables (that is, the CREATE TABLE or CREATE ANY TABLE system privilege) or the ability to alter the child table (that is, the ALTER object privilege for the child table or the ALTER ANY TABLE system privilege).
In both cases, necessary privileges cannot be obtained through a role; they must be explicitly granted to the creator of the constraint.

These restrictions allow:

The owner of the child table to explicitly decide what constraints are enforced on her or his tables and the other users that can create constraints on her or his tables
The owner of the parent table to explicitly decide if foreign keys can depend on the primary and unique keys in her tables