Constraint

• 用于限定某一列或某几列的规则
• CONSTRAINT的种类
  • PRIMARY KEY CONSTRAINT
    • 识别一列或多列，保证在表中该列中每一行的独特性
  • FOREIGN KEY CONSTRAINT
    • 限制一列或多列，仅包含外界参考的PRIMARY KEY中包含的数值，
  • UNIQUE CONSTRAINT
    • 限制该列中每一行的独特性，PRIMARY KEY是一种特殊的UNIQUE KEY
  • CHECK CONSTRAINT
    • 限制每列中可接受的数据类型（大小，类型）

Constraint Creation

• 在创建Table的过程中

  CREATE TABLE $tbl_name 
  (...,
  CONSTRAINT $cons_name FOREIGN KEY ($fld_name) REFERENCES $parent_tbl_name ($primary_fld_of_parent_tbl) ON DELETE RESTRICT ON UPDATE CASCADE)
  

• ALERT

  ALERT TABLE $tbl_name ADD CONSTRAINT $cons_name FOREIGN KEY ($fld_name) 
  REFERENCES $parent_tbl_name ($primary_fld_of_parent_tbl) ON DELETE RESTRICT ON UPDATE CASCADE;
  

  • ON DELETE RESTRICT: 当删除parent table 中的PRIMARY KEY时会报错
  • ON UPDATE CASCADE: 当parent table改变时，会广播(propagate)到其他child table内。如当更改parent table的primary key时，Child table所对应的foreign key也会随之发生变化

• 6种foreign key constraint
  • on delete restrict
  • on delete cascade
  • on delete set null
  • on update restrict
  • on update cascade
  • on update set null