Pitfalls of creating a foreign key constraint on a system table?

Question

I have a number of tables which hold metadata about the tables and packages in the system. This is intended to be a resource for analysts and documentation so it is not used to create data.

One of the columns that appears in a few of these tables shows the user or "Owner" of the object. The data should only contain user accounts in the database and it seems like a good practice to have a foreign key constraint from the metadata tables to sys.user$.

However I have always approached SYS tables with caution and I was surprised to see that there is no primary key for SYS.USER$ only a unique index on the NAME column.

As far as I know I cannot create a foreign key on sys.all_users. Or I could create a table as Select username, user_id from all_users so the data entry is checked on insert but it all seems very clumsy.

Is there a better way to access a table that has a primary key and the names of all schemas?

I ended up adding a check constraint as a hard coded solution that won't mess with system files. I still think there should be way to get a list of the schemas or "owners" that can be used as a foreign key constraint.

@Phil, why don't you post your comment as an answer?

Answer 1

I did not find an elegant way to solve this question but adding a check constraint on the "Owner" column of the metadata tables accomplished the objective of ensuring that only schema owners would be listed. If a new schema owner is added then all the check constraints would have to be changed but that is not too onerous.

I tip my hat to Phil for confirming that backing away slowly from system tables is the best approach and wish he had posted this as an answer.