ORACLE数据库约束-非空约束查询

oracle非空约束查询 IS NOT NULL

1、oracle查询表的非空约束

SET SERVEROUT ON
 BEGIN
 FOR I IN (select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,SEARCH_CONDITION from dba_constraints where TABLE_NAME='&table_name' and owner='&username' and CONSTRAINT_TYPE='C') LOOP
 IF INSTR(I.SEARCH_CONDITION , 'IS NOT NULL')>0 THEN
 DBMS_OUTPUT.PUT_LINE(I.OWNER||'  '||I.CONSTRAINT_NAME||'  '||I.CONSTRAINT_TYPE||'  '||I.TABLE_NAME||'  '||I.SEARCH_CONDITION );
 END IF;
 END LOOP;
 END;
 /

2、oracle查询当前用户表的非空约束

SET SERVEROUT ON
 BEGIN
 FOR I IN (select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,SEARCH_CONDITION from USER_constraints where CONSTRAINT_TYPE='C') LOOP
 IF INSTR(I.SEARCH_CONDITION , 'IS NOT NULL')>0 THEN
 DBMS_OUTPUT.PUT_LINE(I.OWNER||'  '||I.CONSTRAINT_NAME||'  '||I.CONSTRAINT_TYPE||'  '||I.TABLE_NAME||'  '||I.SEARCH_CONDITION );
 END IF;
 END LOOP;
 END;
 /

3、oracle查询表相关的约束

select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,SEARCH_CONDITION from dba_constraints where TABLE_NAME='&table_name' and owner='&username' ;

select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_OWNER,R_CONSTRAINT_NAME,STATUS,INDEX_OWNER,INDEX_NAME
 from dba_constraints where TABLE_NAME='&table_name' and owner='&username';