Constraint names are stored in ALL_CONSTRAINTS table. The column names on which constraints are defined can be found in ALL_CONS_COLUMNS.
Discussion: 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.
select * from all_constraints
where owner = '<NAME>'
and constraint_name = 'SYS_C00381400'
/
Like all data dictionary views, this a USER_CONSTRAINTS view if you just want to check your current schema and a DBA_CONSTRAINTS view for administration users.
The construction of the constraint name indicates a system generated constraint name. For instance, if we specify NOT NULL in a table declaration. Or indeed a primary or unique key. For example:
SQL> create table t23 (id number not null primary key)
2 /
Table created.
SQL> select constraint_name, constraint_type
2 from user_constraints
3 where table_name = 'T23'
4 /
CONSTRAINT_NAME C
------------------------------ -
SYS_C00935190 C
SYS_C00935191 P
SQL>
'C'
for check, 'P'
for primary.
Generally it's a good idea to give relational constraints an explicit name. For instance, if the database creates an index for the primary key (which it will do if that column is not already indexed) it will use the constraint name oo name the index. You don't want a database full of indexes named like SYS_C00935191
.
To be honest most people don't bother naming NOT NULL constraints.
To get a more detailed description (which table/column references which table/column) you can run the following query:
SELECT uc.constraint_name||CHR(10)
|| '('||ucc1.TABLE_NAME||'.'||ucc1.column_name||')' constraint_source
, 'REFERENCES'||CHR(10)
|| '('||ucc2.TABLE_NAME||'.'||ucc2.column_name||')' references_column
FROM user_constraints uc ,
user_cons_columns ucc1 ,
user_cons_columns ucc2
WHERE uc.constraint_name = ucc1.constraint_name
AND uc.r_constraint_name = ucc2.constraint_name
AND ucc1.POSITION = ucc2.POSITION -- Correction for multiple column primary keys.
AND uc.constraint_type = 'R'
AND uc.constraint_name = 'SYS_C00381400'
ORDER BY ucc1.TABLE_NAME ,
uc.constraint_name;
From here.
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";
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