10.3 Oracle的完整性

• 实体完整性
  CREATE TABLE Student
  (
  Sno NUMBER(8),
  Sname VARCHAR(20),
  Sage NUMBER(20),
  CONSTRAINT pk_sno PRIMARY KEY(Sno)
  );
• 参照完整性
  CREATE TABLE Student
  (
  Sno NUMBER(8),
  Sname VARCHAR(20),
  Sage NUMBER(20),
  Dno NUMBER(8),
  CONSTRAINT fk_dno FOREIGN KEY(Dno)
  　　REFRENCES Dept(Dno),
  CONSTRAINT pk_sno PRIMARY KEY(Sno)
  );
• 用户定义的完整性
  • NOT NULL、UNIQUE和CHECK
    CREATE TABLE Student
    (
    Sno NUMBER(8),
    Sname VARCHAR(20) CONSTRAINT u1 UNIQUE,
    Sage NUMBER(20) NOT NULL,
    Ssex VARCHAR(2) CONSTRAINT c1 CHECK(Ssex IN( '男','女' )),
    CONSTRAINT pk_sno PRIMARY KEY(Sno)
    );
  • CREATE TRIGGER
    CREATE TRIGGER Update_sal
    
    BEFORE INSERT OR UPDATE OF Sal,Pos ON Teacher
    FOR EACH ROW
    WHEN (:new.Pos='教授')
    BEGIN
    IF :new.Sal<1000 THEN
    　　:new.Sal:=1000;
    END IF;
    END;

---