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?