check constraint where values must be a table name 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))
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?