Adding a foreign key constraint to USER_TABLES

[RichardF]

Hi,

ALTER TABLE TABLE1 ADD (
CONSTRAINT FK_TABLENAME FOREIGN KEY (TABLENAME)
REFERENCES USER_TABLES(TABLE_NAME) );

This gives me insufficient privileges - but I thought User_tables was a user accessible table.

Is it possible to do this ?


Rich.

Programmers are tools for converting caffeine into code

 

[SantaMufasa]

Richard,

Under typical circumstances, you would be correct. But in the case of The Oracle Data Dictionary, all of its user-visible objects are, in fact, VIEWS (which are not referenceable with foreign keys)...they are not TABLES.

The other problem is that if you had a working foreign key that pointed to a primary key in the data dictionary, you would, in effect, prevent Oracle from executing "DROP TABLE..." commands against tables to which your foreign key references. Therefore, Oracle will not allow you to create foreign keys again Data Dictionary objects.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”