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 want to create a table where I can have a column username which will be the foreign key and it will only contain usernames of existing users on that database. For example, if I want to insert 'John' in that field, there have to be a database user 'John' otherwise database should throw error. Any help?

share|improve this question
add comment

1 Answer

You cannot create a foreign key that references an Oracle data dictionary table. So assuming the "users on that database" are Oracle database users that exist in dba_users, you can't reference dba_users in your constraint.

You can, however, create your own copy of the dba_users table and create a job (using dbms_scheduler or dbms_job or some external scheduler) that will periodically copy the data from dba_users to your copy of the table. You can then create a primary key constraint on the username column in your copy and create a foreign key constraint that references your copy of the table. You may need to work with the DBA team to ensure that your copy of the table gets refreshed as part of any process that creates new database users depending on how soon after a user is created you would expect rows to be created in the child table.

share|improve this answer
    
Actually,I'm working as a junior dba since last 4 months. :) –  Rajat Saxena May 14 '13 at 7:22
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.