Oracle: How to add a foreign key on USER_TABLES.TABLE_NAME in a table

user_10167746 0
Viewed 205

I am creating a table and I need to add a foreign key constraint on USER_TABLES.TABLE_NAME.

I am not quite sure how to do that?

I tried the following line of code: 

CONSTRAINT FK_USR_TBLS FOREIGN KEY (TABLE_NAME) REFERENCES USER_TABLES(TABLE_NAME)

But got the following error when I tried to create the table: 

ORA-00904: "TABLE_NAME": invalid identifier
constraints oracle sql
2 Answers

user_tables is a view in the Oracle data dictionary. There are two complexities here.

We cannot build foreign keys on objects which don't have a primary key. Because user_tables is part of the data dictionary and managed by Oracle we cannot change it in any way, shape or form without invalidating the Support.

But that doesn't matter because we cannot enforce constraints on views. Sure, we can build primary keys but they must be declared as DISABLE NOVALIDATE. This means we cannot reference the constraint in a foreign key on a table.

You have a slightly odd requirement, but if you want to build a table which enforces a relationship against the data dictionary you can do it with a materialized view. Something like this:

create materialized view my_tables
as
select table_name from user_tables
/

alter materialized view my_tables 
    add constraint my_tables_uk unique (table_name)
/

create table t42 (id number, table_name varchar2(30))
/

alter table t42 
    add constraint t42_tname_fk foreign key (table_name) 
    references my_tables(table_name)
/
user_1463250

FOREIGN KEY can be done only on column(s):

[CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name, ...) REFERENCES tbl_name (col_name,...)

You must state the column(s) name inside the round brackets, you can't use table name

Also notice corresponding columns must have same data types:

Corresponding columns in the foreign key and the referenced key must have similar data types.

Also USER_TABLES is a view part of sys scheme

user_72949000
Related