Before changing a table, you often need to know its constraints: which columns form the key, which tables point to it, what a check enforces. USER_CONSTRAINTS and USER_CONS_COLUMNS hold all of that, and a few joins answer questions such as "what does this foreign key reference" and "who references this table".
Code for This Guide
The main examples are in the examples/dictionary folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
The Views
| View | Holds |
|---|---|
| USER_CONSTRAINTS | One row per constraint: name, table, type, status, and for foreign keys R_CONSTRAINT_NAME, the key it references |
| USER_CONS_COLUMNS | The columns of each constraint, with their position |
CONSTRAINT_TYPE is P for primary key, U for unique, R for foreign key (referential), and C for check, which includes NOT NULL.
All Constraints of a Table
Example:
-- every constraint of TICKETS, with its type and, for checks, the condition
select constraint_name,
decode(constraint_type, 'P', 'primary key', 'U', 'unique', 'R', 'foreign key',
'C', 'check', constraint_type) as kind,
status, search_condition_vc as condition
from user_constraints
where table_name = 'TICKETS'
order by constraint_type, constraint_name;Output:
CONSTRAINT_NAME KIND STATUS CONDITION
_____________________ ______________ __________ ___________________________________
SYS_C0016151 check ENABLED "TICKET_ID" IS NOT NULL
SYS_C0016152 check ENABLED "BOOKING_ID" IS NOT NULL
SYS_C0016153 check ENABLED "FLIGHT_ID" IS NOT NULL
SYS_C0016154 check ENABLED "CABIN" IS NOT NULL
SYS_C0016155 check ENABLED "SEAT_NO" IS NOT NULL
SYS_C0016156 check ENABLED "FARE" IS NOT NULL
SYS_C0016157 check ENABLED "CHECKED_IN" IS NOT NULL
TICKETS_CABIN_CK check ENABLED cabin in ('BUSINESS', 'ECONOMY')
TICKETS_PK primary key ENABLED
TICKETS_BOOKING_FK foreign key ENABLED
TICKETS_FLIGHT_FK foreign key ENABLED
11 rows selected.The NOT NULL constraints have system names such as SYS_C0016151, while the others were named when the table was created, which makes their error messages much easier to read. SEARCH_CONDITION_VC shows each check's condition as text.
What a Foreign Key References
Joining a foreign key through R_CONSTRAINT_NAME to the key it references, and both to their columns, shows the full relationship.
Example:
-- the foreign keys of FLIGHTS, and the tables they point to
select c.constraint_name, cc.column_name, p.table_name as references_table,
pc.column_name as references_column
from user_constraints c
join user_cons_columns cc on cc.constraint_name = c.constraint_name
join user_constraints p on p.constraint_name = c.r_constraint_name
join user_cons_columns pc on pc.constraint_name = p.constraint_name
where c.table_name = 'FLIGHTS' and c.constraint_type = 'R'
order by c.constraint_name;Output:
CONSTRAINT_NAME COLUMN_NAME REFERENCES_TABLE REFERENCES_COLUMN ______________________ ______________ ___________________ ____________________ FLIGHTS_AIRCRAFT_FK TAIL_NUMBER AIRCRAFT TAIL_NUMBER FLIGHTS_ROUTE_FK ROUTE_ID ROUTES ROUTE_ID
Which Tables Reference a Table
The reverse question, needed before deleting rows or dropping a table, finds the foreign keys whose R_CONSTRAINT_NAME is the table's primary key.
Example:
-- which tables reference FLIGHTS?
select c.table_name, c.constraint_name, c.delete_rule
from user_constraints c
where c.r_constraint_name in (select constraint_name from user_constraints
where table_name = 'FLIGHTS' and constraint_type = 'P')
order by c.table_name;Output:
TABLE_NAME CONSTRAINT_NAME DELETE_RULE ___________________ ____________________ ______________ CREW_ASSIGNMENTS CREW_FLIGHT_FK NO ACTION REVIEWS REVIEWS_FLIGHT_FK NO ACTION TICKETS TICKETS_FLIGHT_FK NO ACTION
Three tables reference FLIGHTS, all with DELETE_RULE NO ACTION, so a flight with tickets, crew, or reviews cannot be deleted.
Things to Know
- ALL_CONSTRAINTS and ALL_CONS_COLUMNS do the same across schemas, with OWNER and R_OWNER columns; join on both name and owner there.
- STATUS and VALIDATED show whether a constraint is enabled and whether existing rows were checked.
- Name your constraints when creating tables; system names tell nothing in an error message.
Related Guides
Conclusion
USER_CONSTRAINTS and USER_CONS_COLUMNS describe every constraint of your tables. Join a foreign key to the key it references through R_CONSTRAINT_NAME, find the tables that reference a table the same way in reverse, and read check conditions from SEARCH_CONDITION_VC.
See my articles more often when you search Google.
