SQL> ed
已写入 file afiedt.buf
1 create table test7
2 (tid number,
3 tname varchar2(20),
4 gender varchar(6) check (gender in('男','女')),
5 sal number check(sal>0)
6* )
SQL> /
表已创建。
SQL> desc test7;
名称 是否为空? 类型
----------------------------------------------------------------------------- -------- ----------------------------------------------------
TID NUMBER
TNAME VARCHAR2(20)
GENDER VARCHAR2(6)
SAL NUMBER
SQL> insert into test7 values(1,'TOM','男',1000);
已创建 1 行。
SQL> insert into test7 values(2,'TOM','hh',1000);
insert into test7 values(2,'TOM','hh',1000)
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.SYS_C0011055)
SQL>
SQL> ed
已写入 file afiedt.buf
1 create table student
2 (sid number constraint student_PK primary key,
3 sname varchar2(20) constraint student_name_notnull not null,
4 email varchar2(20) constraint student_email_unique unique
5 constraint student_email_notnull not null,
6 age number constraint student_age_min check(age>10),
7 gender varchar2(6) constraint gender_female_or_male check(gender in('男','女')),
8 deptno number constraint student_FK references dept(deptno) ON DELETE SET NULL
9* )
SQL> /
表已创建。
SQL> desc student;
名称 是否为空? 类型
----------------------------------------------------------------------------- -------- ----------------------------------------------------
SID NOT NULL NUMBER
SNAME NOT NULL VARCHAR2(20)
EMAIL NOT NULL VARCHAR2(20)
AGE NUMBER
GENDER VARCHAR2(6)
DEPTNO NUMBER
SQL> insert into student values(1,'TOM','tom@126.com',20,'男',10);
已创建 1 行。
SQL> insert into student values(2,'TOM','tom@126.com',20,'男',10);
insert into student values(2,'TOM','tom@126.com',20,'男',10)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.STUDENT_EMAIL_UNIQUE)
SQL> insert into student values(3,'TOM3','tom3@126.com',30,'男',100);
insert into student values(3,'TOM3','tom3@126.com',30,'男',100)
*
第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SCOTT.STUDENT_FK) - 未找到父项关键字
SQL> ed
已写入 file afiedt.buf
1 select constraint_name,constraint_Type,search_condition
2* from user_constraints where table_name='STUDENT'
SQL> /
CONSTRAINT_NAME C SEARCH_CONDITION
------------------------------ - --------------------------------------------------------------------------------
STUDENT_NAME_NOTNULL C "SNAME" IS NOT NULL
STUDENT_EMAIL_NOTNULL C "EMAIL" IS NOT NULL
STUDENT_AGE_MIN C age>10
GENDER_FEMALE_OR_MALE C gender in('男','女')
STUDENT_PK P
STUDENT_EMAIL_UNIQUE U
STUDENT_FK R
已选择7行。
SQL>
