Home » SQL & PL/SQL » SQL & PL/SQL » referential integrity
referential integrity [message #306225] Thu, 13 March 2008 06:30 Go to next message
deepak samal
Messages: 29
Registered: February 2005
Junior Member
Hi,

While creating referential constraint can i refer a table of different schema. If , yes shall i need to give any permission.

I am connected as SYSTEM user and running below script.

ALTER TABLE SCHEMA1.dependent
ADD CONSTRAINT emp_num_fk
FOREIGN KEY ( emp_num )
REFERENCES SCHEMA2.employee ( emp_num );

Here i am getting error as "Table or view does not Exist".


regards
deepak

Re: referential integrity [message #306227 is a reply to message #306225] Thu, 13 March 2008 06:37 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Yes, you can refer to a table in different schema.A similar question was asked some days back in this forum.And why do you want to use the SYSTEM schema.Use it only for your day-to-day DBA tasks.


regards,
Re: referential integrity [message #306234 is a reply to message #306225] Thu, 13 March 2008 06:48 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
schema1 must have the "references" privilege on schema2 table.
See constraint, prerequisites section.

And this is NOT an expert question, just reading the SQL manual.

Regards
Michel

[Updated on: Thu, 13 March 2008 06:50]

Report message to a moderator

Previous Topic: select with as column header reanming not working
Next Topic: select between date
Goto Forum:
  


Current Time: Sun Dec 04 04:19:19 CST 2016

Total time taken to generate the page: 0.04953 seconds