Database integrity: prevent non semantic and incorrect data from entering the database;
Database security: prevent illegal users from intruding into the database and illegally accessing the database data.
5.1 entity integrity PRIMARY KEY
Entity integrity definition: single attribute - column level, table level constraint, multi attribute - only table level constraint
Column level definition main code

CREATE TABLE STUDENT
(SNO CHAR(9)PRIMARY KEY,/*Column level definition main code*/
SNAME CHAR(20) NOT NULL,
SSEX CHAR(2),
SAGE SMALLINT,
SDEPT CHAR(20)
);

Table level definition main code

CREATE TABLE SC
(SNO CHAR(9) NOT NULL,
CNO CHAR(4) NOT NULL,
GRADE SMALLINT,
PRIMARY KEY(SNO,CNO)/*Table level definition main code*/
);

5.2 reference integrity REFERENCES

CREATE TABLE SC
(SNO CHAR(9) NOT NULL,
CNO CHAR(4) NOT NULL,
GRADE SMALLINT,
PRIMARY KEY(SNO,CNO),/*Table level definition main code*/
FOREIGN KEY (SNO)REFERENCES STUDENT(SNO),
FOREIGN KEY(CNO)REFERENCES COURSE(CNO)/*Table level definition referential integrity*/
)

Default treatment

CREATE TABLE SC
(SNO CHAR(9) NOT NULL,
CNO CHAR(4) NOT NULL,
GRADE SMALLINT,
PRIMARY KEY(SNO,CNO),/*Table level definition main code*/
FOREIGN KEY (SNO)REFERENCES STUDENT(SNO)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY(CNO)REFERENCES COURSE(CNO)
ON DELETE NO ACTION/*Refuse to delete the course table tuple when it is inconsistent with the SC table*/
ON UPDATE CASCADE/*When the course table cno is updated, the corresponding tuple of SC table is updated in cascade*/
);

5.3 user defined integrity NOT NULL, UNIQUE, CHECK
Column value is NOT NULL
UNIQUE column value
Whether the column value satisfies the Boolean expression CHECK

Column value is not empty

CREATE TABLE SC
(SNO CHAR(9) NOT NULL,
CNO CHAR(4) NOT NULL,
GRADE SMALLINT,
PRIMARY KEY(SNO,CNO)/*Table level entity integrity has been implied SNO,CNO Null value not allowed
)

Unique column value
slightly

constraint
Constraints on attributes

CREATE TABLE STUDENT
(SNO CHAR(9) PRIMARY KEY,
SNAME CHAR(8) NOT NULL,
SSEX CHAR(2) CHECK(SSEX IN ('male','female')),
SAGE SMALLINT CHECK(SAGE>=0 AND SAGE<=150),
SDEPT CHAR(20)
);

Constraints on tuples
Tuple level restriction can set mutual restriction of values between different attributes

CREATE TABLE STUDENT
(SNO CHAR(9),
SNAME CHAR(8) NOT NULL,
SSEX CHAR(2),
SAGE SMALLINT,
SDEPT CHAR(20),
PRIMARY KEY(SNO),
CHECK(SSEX='female' OR SNAME LIEK 'Ms.%')
)；

5.4 naming clauses for integrity constraints
CONSTRAINT, naming integrity constraints
Syntax: constraint < integrity constraint name > [primary key phrase | FOREIGN KEY phrase | CHECK phrase]

CREATE TABLE TEACHER
(ENO NUMERIC(4) PRIMARY KEY,
ENAME CHAR(10),
JOB CHAR(8),
SAL NUMERIC(7,2),
DEDUCT NUMERIC(7,2),
DEPTNO NUMERIC(2),
CONSTRAINT EMPFKEY FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
CONSTRAINT C1 CHECK (SAL+DEDUCT>=3000)
);

Modify the integrity limit in the table

ALTER TABLE TEACHER
DROP CONSTRAINT C1;
ALTER TABLE STUDENT
ADD CONSTRAINT C1 CHECK(SAL+DEDUCT>=4000);

*5.5 integrity constraints in domains
SQL support domain concept
Establish gender field and declare the value range of gender field

CREATE DOMAIN GENDERDOMAIN CHAR(2)
CHECK (VALUE IN ('male','female'));

5.6 trigger
A special event driven process defined by a user on a relational table. After it is defined, the server automatically activates the corresponding trigger for the user action
grammar
Create trigger < trigger name >
{before|after} < trigger event > on < table name >
FOR EACH{ROW|STATEMENT}
[when < trigger condition >]
< trigger action body >

1. Only the owner of the table can create a trigger on the table
The first mock exam is the first mock exam. 2. The trigger name is unique in the same mode. The trigger name and the < table name > must be in the same mode.
3. Trigger condition: WHEN the trigger is activated, if the trigger condition is true, the trigger action body will be executed; if the WHEN trigger condition is omitted, the trigger will be executed immediately after activation
4. Trigger action body: anonymous PL/SQL procedure block calls to the created stored procedure

NEW/OLD(old!!) reference	Anonymous PL/SQL procedure block	Call to created stored procedure
row-level trigger	can	can
Statement Level Trigger	must not	must not

BEFORE row level trigger

CREATE TRIGGER Insert_Or_Update_Sal
  BEFORE ISNERT OR UPDATE ON Teacehr
  FOR EACH ROW
  AS BEGIN
     IF (new.Job='professor') AND (new.sal<4000) THEN
            new.sal=4000;
      END IF;
  END;

AFTER row level trigger
Add records to the salary change table after the change of teachers' salary

CREATE TABLE Sal_log
  (Eno NUMERIC(4) REFERENCES teacher(Eno),
  Sal NUMERIC(7,2),
  Username char(10),
  Date TIMESTAMP
  );
CREATE TRIGGER Insert_Sal
  AFTER INSERT ON Teacher
  FOR EACH ROW
  AS BEGIN
    INSERT INTO Sal_log VALUES(
            new.Eno,new.Sal,CURRENT_USER,CURRENT_TIMESTAMP);/*Why not new.username*/
    END;
CREATE TRIGGER Update_Sal
  AFTER UPDATE ON Teacher
  FOR EACH ROW
  AS BEGIN
    IF (new.Sal<>old) THEN INSERT INTO Sal_log VALUES(
          new.Eno,new.Sal,CURRENT_USER,CURRENT_TIMESTAMP);
    END IF;
  END;

Execution order when trigger is activated

1. BEFORE trigger
2. sql statement to activate trigger
3. after trigger
4. Create sibling first execute first

Trigger delete

DROP TRIGGER Insert_Sal ON Teacher