Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a way to write a check constraint such that values in a column are limited to table names currently in the schema? It appears subqueries are not allowed (ORA-02251), but logically I am looking to do:

CONSTRAINT ck_table_name
     CHECK (src_table IN (select table_name from user_tables))
share|improve this question
1  
Logically you don't want a check constraint but a foreign key. –  Colin 't Hart Jul 4 at 7:05

2 Answers 2

What you want is not a check constraint but an ordinary foreign key.

Normally you would do:

alter table table_name add foreign key (src_table) references user_tables(table_name);

however Oracle won't allow this as user_tables is a view, not a table.

You could try to reference the underlying Oracle data-dictionary table, but that probably won't work either -- not least because you'd need two columns at your end -- table_name and schema -- but also you'd need to be granted the privilege to reference that table.

An approach that I would consider -- as recommended above -- is to create your own table listing which tables can be referred to. You could populate this table yourself, or you could create a DDL trigger to maintain it for you!

The other thing to consider is what to do when the table is dropped: will you on delete cascade delete the record in your table, or set its contents to null using on delete set null?

share|improve this answer

You should avoid creating check constraints based on data dictionary tables. You can create your own table that has application tables. You can also include other columns that might be meaningful to your application, but not already included in user_tables. If you create your own table, then you should create a foreign key between you table that you are working on and you own version of user_tables. An example of a column that you might want in your custom user tables would be parent table, which implies a self join, hence it needs to allow nulls since not every table will have a parent table.

share|improve this answer
    
Why should I avoid that? The table that I am creating will be used in a PL/SQL block to generate dynamic SQL using the name given in that column. That seems like an ideal use case - am I missing something? –  JHFB Jun 2 at 13:15
    
It's a terrible idea. Generate it procedurally using a script if you have to. Then store it in some kind of source repository to version control it –  Phil Jun 2 at 13:25
    
What is your use case for dynamically creating tables? –  Phil Jun 2 at 13:26
    
I worked on a tool a long time ago that allowed users of a data warehouse to create custom tables, we created our own tables to manage the metadata, even though we could have used the data dictionary tables. One advantage is that if a table is in our customer table, then we know that we generated it. –  Gandolf989 Jun 2 at 13:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.