Oracle--约束

**约束条件：**

 

FOREIGN KEY：在子表中定义一个级表约束

REFERENCES：指定表和父表中的列

ON DELETE CASCADE：当删除父表时，级联删除子表纪录

ON DELETE SET NULL：将子表的相关依赖纪录外键置为 null

create table teacher
(
    sid number constraint teacher_sid_pk primary key,
    sname varchar2(20) constraint teacher_sname_notnull not null,
    email varchar2(64) constraint teacher_email_notnull not null 
                    constraint teacher_email_unique unique,

    
    sex number constraint teacher_sex_min_check check(sex in(1,2)),
    age number constraint teacher_age check(age>20)
);

 

**插入:**

 

    insert into teacher(sid, sname, email, sex, age) values (1, 'tom11', '[e-mail]', 1, 21);

 

 

       

# 主外键约束

 

create table teacher2
(
    sid number constraint teacher2_sid_pk primary key,
    sname varchar2(20) constraint teacher2_sname_notnull not null,
    email varchar2(64) constraint teache2r_email_notnull not null 
                    constraint teache2r_email_unique unique,


    
    sex number constraint teacher2_sex_min_check check(sex in(1,2)),
    age number constraint teacher2_age check(age>20),
    deptno number constraint teacher2_deptno_pk references dept(deptno)
);