So, we can easily find all the constraints on the table in oracle using data dictionary views.
Where (r_constraint_name) in ( select constraint_name SQL> select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME='DEPT_MASTER' and CONSTRAINT_TYPE='U' Other Queries for Constraints select table_name PK_EMP PK_EMP P query to find unique constraints on a table in oracle SQL> CREATE TABLE DEPT_MASTER (
Find all my tables sql tabs how to#
The parent table being referenced can be found used the R_CONSTRAINT_NAME SQL> select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE,table_name from user_constraints where CONSTRAINT_NAME='PK_DEPT' How to check the primary key in the table SQL> select CONSTRAINT_NAME C_NAME,INDEX_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME='EMP' and CONSTRAINT_TYPE='P' Select owner, CONSTRAINT_NAME ,INDEX_NAME,CONSTRAINT_TYPE,Search_condition,R_CONSTRAINT_NAME R_NAME from all_constraints where CONSTRAINT_NAME='&1' How to check the Referential integrity constraints in the table SQL> select CONSTRAINT_NAME C_NAME,INDEX_NAME,CONSTRAINT_TYPE,Search_condition,R_CONSTRAINT_NAME R_NAME from user_constraints where TABLE_NAME='EMP' and CONSTRAINT_TYPE='R' SQL> select OWNER,CONSTRAINT_NAME ,COLUMN_NAME,POSITION from User_cons_columns where TABLE_NAME='DEPT' How to find constraint by name in Oracle select CONSTRAINT_NAME ,INDEX_NAME,CONSTRAINT_TYPE,Search_condition,R_CONSTRAINT_NAME R_NAME from user_constraints where CONSTRAINT_NAME='&1' Similar Queries can be used for selecting constraints on DEPT Table SQL> select CONSTRAINT_NAME C_NAME,INDEX_NAME,CONSTRAINT_TYPE,Search_condition,R_CONSTRAINT_NAME R_NAME from user_constraints where TABLE_NAME='DEPT' If there are multiple columns present in the constraints, Position will specify the order of the column in the constraint We will get null values for the Position column if we have a single column in the constraint. SQL> select OWNER,CONSTRAINT_NAME ,COLUMN_NAME,POSITION from User_cons_columns where TABLE_NAME='EMP' Now Primary key, Foreign Key, and Unique key constraints columns can be found using the below query SQL> column OWNER format a10 Here CONSTRAINT_TYPE define the type of constraintsĬ stands for check constraints or not null constraints Select CONSTRAINT_NAME C_NAME,INDEX_NAME,CONSTRAINT_TYPE,Search_condition,R_CONSTRAINT_NAME R_NAME from user_constraints where TABLE_NAME='EMP'
0 kommentar(er)
