SQL> select owner, constraint_name, constraint_type from dba_constraints
where owner='MAXIMSG' and table_name='SCM_INV_OPERATIONM';
OWNER CONSTRAINT_NAME C
------------------------------ ------------------------------ -
MAXIMSG SYS_C005768 C
MAXIMSG SYS_C005769 C
MAXIMSG SCM_INV_OPERATIONM_PK P
MAXIMSG OPM__OPERATION_BY_FK R
MAXIMSG OPM__STATUS_FK R
MAXIMSG INV_OPM__OP_ID_FK R
MAXIMSG INV_OPM__COMPANY_ENTITY_FK R
MAXIMSG INV_OPM__SCM_OPM_PERFORMER R
MAXIMSG OPM__ACCOUNT_FK R
9 rows selected.
In the column constraint_type there may have values C,P,U,R,V and O which means,
1)C :check constraint on a table
2)P :primary key
3)U :unique key
4)R :referential integrity
5)V :with check option, on a view
6)O :with read only, on a view
You can get constraints along with their columns and position by querying from DBA_CONS_COLUMNS/ USER_CONS_COLUMNS/ ALL_CONS_COLUMNS.
SQL> col owner for a10
col constraint_name for a27
col table_name for a25
col column_name for a23
set pages 100
SQL> select * from dba_cons_columns where table_name='SCM_INV_OPERATIONM'
and owner='MAXIMSG' order by constraint_name, position;
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
---------- --------------------------- ------------------------- ----------------------- ----------
MAXIMSG INV_OPM__COMPANY_ENTITY_FK SCM_INV_OPERATIONM COMPANY_ID 1
MAXIMSG INV_OPM__OP_ID_FK SCM_INV_OPERATIONM OPERATION_ID 1
MAXIMSG INV_OPM__SCM_OPM_PERFORMER SCM_INV_OPERATIONM OPERATION_PERFORMER_ID 1
MAXIMSG INV_OPM__SCM_OPM_PERFORMER SCM_INV_OPERATIONM COMPANY_ID 2
MAXIMSG OPM__ACCOUNT_FK SCM_INV_OPERATIONM CASH_ACC_ID 1
MAXIMSG OPM__ACCOUNT_FK SCM_INV_OPERATIONM COMPANY_ID 2
MAXIMSG OPM__OPERATION_BY_FK SCM_INV_OPERATIONM OPERATION_BY 1
MAXIMSG OPM__STATUS_FK SCM_INV_OPERATIONM OPERATION_STATUS 1
MAXIMSG SCM_INV_OPERATIONM_PK SCM_INV_OPERATIONM ID 1
MAXIMSG SYS_C005768 SCM_INV_OPERATIONM ID
MAXIMSG SYS_C005769 SCM_INV_OPERATIONM OPERATION_ID
11 rows selected.
By joining both two views you can get a list of constraints , their type, column_name, column position and their reference constraint name, reference table name in the constraint by,
SQL> col r_owner for a10
SQL> Select c.constraint_name, cc.column_name, c.r_owner,
c.r_constraint_name,c.constraint_type,cc.position, r.table_name
from dba_constraints c
JOIN dba_cons_columns cc
ON(c.table_name=cc.table_name AND c.owner=cc.owner
AND c.constraint_name=cc.constraint_name)
LEFT JOIN dba_constraints r
ON(c.r_constraint_name=r.constraint_name AND r.constraint_type in ('P','U') )
where c.table_name='SCM_INV_OPERATIONM' and c.owner='MAXIMSG' order by
constraint_name, position;
CONSTRAINT_NAME COLUMN_NAME R_OWNER R_CONSTRAINT_NAME C POS TABLE_NAME
--------------------------- ----------------------- ------- ------------------------ - --- ------------------------
INV_OPM__COMPANY_ENTITY_FK COMPANY_ID MAXIMSG PK_COMPANY R 1 HW_COMPANY_ENTITY
INV_OPM__OP_ID_FK OPERATION_ID MAXIMSG OPERATION__ID_PK R 1 SCM_OPERATION
INV_OPM__SCM_OPM_PERFORMER OPERATION_PERFORMER_ID MAXIMSG PK_SCM_OPERATION_PERFORM R 1 SCM_OPERATION_PERFORMER
INV_OPM__SCM_OPM_PERFORMER COMPANY_ID MAXIMSG PK_SCM_OPERATION_PERFORM R 2 SCM_OPERATION_PERFORMER
OPM__ACCOUNT_FK CASH_ACC_ID MAXIMSG PK_ACCOUNT R 1 HW_ACCOUNT
OPM__ACCOUNT_FK COMPANY_ID MAXIMSG PK_ACCOUNT R 2 HW_ACCOUNT
OPM__OPERATION_BY_FK OPERATION_BY MAXIMSG PK_SCM_ME R 1 SCM_ME
OPM__STATUS_FK OPERATION_STATUS MAXIMSG PK_HW_STATUS R 1 HW_STATUS
SCM_INV_OPERATIONM_PK ID P 1
SYS_C005768 ID C
SYS_C005769 OPERATION_ID C
11 rows selected.
Related Documents
How to Disable and Enable all constraints using SQL
ORA-02297: cannot disable constraint -dependencies exist
Type of constraint in oracle
No comments:
Post a Comment