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.

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?

share|improve this question
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. –  FreshPhilOfSO Oct 29 '12 at 15:08
add comment

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
    
If I add an answer, you can change the accepted one :) –  FreshPhilOfSO Nov 5 '12 at 17:02
    
@Phil please submit your comment as an answer and I'll do so –  kevinsky Nov 5 '12 at 17:05
add comment

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.