下面以实际例子来说明:
例子表:
SQL> desc emp5
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(6)
LAST_NAME VARCHAR2(20)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
目前没有主健约束
SQL> select constraint_name,constraint_type,VALIDATED,status from user_constraints where table_name='EMP5';
CONSTRAINT_NAME C VALIDATED STATUS
-------------------- - ------------- --------
SYS_C007401 C VALIDATED ENABLED
SYS_C007402 C VALIDATED ENABLED
SYS_C007403 C VALIDATED ENABLED
检查约束情况;
SQL> select constraint_name,constraint_type,VALIDATED,status from user_constraints where table_name='EMP5';
CONSTRAINT_NAME C VALIDATED STATUS
-------------------- - ------------- --------
SYS_C007401 C VALIDATED ENABLED
SYS_C007402 C VALIDATED ENABLED
SYS_C007403 C VALIDATED ENABLED
SYS_C007405 P NOT VALIDATED ENABLED
索引,非唯一索引:
SQL> R
1* select index_name,index_type,uniqueness from user_indexes where table_name='EMP5'
INDEX_NAME INDEX_TYPE UNIQUENES
------------------------------ --------------------------- ---------
IDX_EMP5_EMPID NORMAL NONUNIQUE
再去新插入数据:
SQL> insert into emp5(employee_id,email,hire_date,job_id) values(101,'zq',sysdate,'zq');
insert into emp5(employee_id,email,hire_date,job_id) values(101,'zq',sysdate,'zq')
*
ERROR at line 1:
ORA-00001: unique constraint (HR1.SYS_C007405) violated