CODEWITHSUNDEEP
sqloracle-database
Philomath
Philomath

Reputation: 1163

Foreign key reference to a new schema

I wanted to move some tables to a new schema. When I moved it, I am getting errors at the foreign key referencing. I also tried giving permissions. Accounts is the old schema where address table lived and I moved the address to a new schema called address. I get the error table or view does not exists:

CREATE TABLE ACCOUNTS.PARTY_ADDRESS
(
 PARTY_ID NUMBER(18,0),
 ADDRESS_ID NUMBER(18,0),
 CONSTRAINT PARTY_ADDRESS_PK PRIMARY KEY (PARTY_ID, ADDRESS_ID),
 constraint PARTY_ADDRESS_party_fk foreign key (PARTY_ID)
 references PARTY(id),
 constraint PARTY_ADDRESS_ADDRESS_UNQ unique (ADDRESS_ID)
);

ALTER TABLE ACCOUNTS.PARTY_ADDRESS ADD CONSTRAINT PARTY_ADDRESS_address_fk FOREIGN KEY (ADDRESS_ID) REFERENCES ADDRESS.ADDRESS(id);

I have also given grant in address: 

GRANT ALL ON ADDRESS.ADDRESS TO ACCOUNTS;

Upvotes: 0

Views: 1332

Answers (1)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

The table privilege REFERENCES is required for a schema to be able to create referential constraints to a table in another schema. GRANT ALL should have worked. In my test case it did work, e.g.:

Schema 1:

create table schema1.t1 (id number primary key);

Table T1 created.

Schema 2:

create table schema2.t2 (id number primary key, fk number);

Table T2 created.

alter table schema2.t2 add constraint fktest
  foreign key (fk) references schema1.t1 (id);

ORA-00942: table or view does not exist

Schema 1:

grant references on schema1.t1 to schema2;

Grant succeeded.

Schema 2:

alter table schema2.t2 add constraint fktest
  foreign key (fk) references schema1.t1 (id);

Table T2 altered.

Schema 1:

revoke references on schema1.t1 from schema2;

ORA-01981: CASCADE CONSTRAINTS must be specified to perform this revoke

revoke references on schema1.t1 from schema2 cascade constraints;

Revoke succeeded.

It also works if you GRANT ALL:

Schema 1:

grant all on schema1.t1 to schema2;

Grant succeeded.

Schema 2:

alter table schema2.t2 add constraint fktest
  foreign key (fk) references schema1.t1 (id);

Table T2 altered.

Upvotes: 1

Related Questions