select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME from information_schema. KEY_COLUMN_USAGE where TABLE_NAME = 'yourTableName'; To display all constraints on a table, implement the above syntax.
Use the view table_constraints in the information_schema schema. The column table_name gives you the name of the table in which the constraint is defined, and the column constraint_name contains the name of the constraint.
To open designer select table, right click and choose Edit... from context menu. In the designer dialog select Constraints option. Again, list includes all table constraints and you will distinguish FKs by Type column.
Constraint names are stored in ALL_CONSTRAINTS table. The column names on which constraints are defined can be found in ALL_CONS_COLUMNS.
You need to query the data dictionary, specifically the USER_CONS_COLUMNS
view to see the table columns and corresponding constraints:
SELECT *
FROM user_cons_columns
WHERE table_name = '<your table name>';
FYI, unless you specifically created your table with a lower case name (using double quotes) then the table name will be defaulted to upper case so ensure it is so in your query.
If you then wish to see more information about the constraint itself query the USER_CONSTRAINTS
view:
SELECT *
FROM user_constraints
WHERE table_name = '<your table name>'
AND constraint_name = '<your constraint name>';
If the table is held in a schema that is not your default schema then you might need to replace the views with:
all_cons_columns
and
all_constraints
adding to the where clause:
AND owner = '<schema owner of the table>'
SELECT * FROM USER_CONSTRAINTS
maybe this can help:
SELECT constraint_name, constraint_type, column_name
from user_constraints natural join user_cons_columns
where table_name = "my_table_name";
cheers
select constraint_name,constraint_type
from user_constraints
where table_name = 'YOUR TABLE NAME';
note: table name should be in caps.
In case you don't know the name of the table then,
select constraint_name,constraint_type,table_name
from user_constraints;
Often enterprise databases have several users and I'm not aways on the right one :
SELECT * FROM ALL_CONSTRAINTS WHERE table_name = 'YOUR TABLE NAME' ;
Picked from Oracle documentation
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With