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?

  • 6
    Use a trigger or something in the app code to do the verification against the all_users view. Never ever ever ever mess with anything that's deemed "internal". No extra indexes, no foreign keys etc. Erase the possibilities from your mind.
    – Philᵀᴹ
    Oct 29, 2012 at 15:08

1 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.

  • @Phil please submit your comment as an answer and I'll do so
    – kevinskio
    Nov 5, 2012 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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