Find constraint in Oracle database

When you're developing web applications, you usually persist against a database. And sometimes, that database throws errors at you. One of them can be a foreign key or other constraint violation. If you manage the database yourself, the constraint names are of course very logical and tell you immediately where the problem is. But sometimes the DB management isn't in your hands, and then the constraint key can be some random combination of letters and numbers. Obviously, it's not easy to find the cause of the problem then... unless you know how to search!

You can query all constraints in an Oracle database. Usually, the error you get from the database will be somewhere in your logs, or even displayed on the screen, so you have the name of the constraint that is violated. Once you have that, you can just run this simple query:

select constraint_name, table_name
from all_constraints
WHERE constraint_name = 'Faulted_Constraint_Name';

Now you know which table causes the error, and you can start solving the problem.

Of course, the 'all_constraints' table has more fields than just constraint_name and table_name. To find out what more you can query, just use following command:

desc all_constraints

This will give you a list of all queryable fields.