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
具体待补充
