（十七）约束

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>