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
上面兩種如果出現(xiàn)違反情況代态，數(shù)據(jù)庫往往直接拋出錯誤 - 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
具體待補充
