Constraint and Trigger

Constraint

Impose restrictions on allowable data, beyond those imposed by structure and types

• Non-null Constraint
• Key constraint
• Attribute-based and tuple-based constraint

create table Student(sID int primary key, 
                     sName text unique, 
                     GPA real NOT NULL, 
                     sizeHS int check(sizeHS < 2000 AND sizeHS > 1000));
create table Apply(sID int, cName text, major text,
                    check(cName <> 'Stanford' or major <> 'CS'));

Referential Integrity(foreign key) constraint

外键的指向必须是存在的(以指针作比喻的话就是不存在野指针)

比如 R.A 指向 S.B, A 称作外键，B 往往要求是 primary key 或至少是 unique 的，
如下操作可能会违反 Referential Integrity

• Insert into R
• Update R.A
  上面两种如果出现违反情况，数据库往往直接抛出错误
• Delte from S
  • Restrict(default) 抛出错误
  • Set Null
  • Cascade 不断递归删除
• Update S.B
  • Restrict(default)
  • Set Null
  • Cascade 不断递归更改

create table Apply(sID int references Student(sID) on delete set null,
                   cName text references College(cName) on update cascade,
                   major text);

Trigger

"Event-Condition-Action Rule"
when event occurs, check conditon, if true, do action

Move logic from app to db
Enfore constraint

具体待补充