integrity constraint

Integrity rules

1. candidate keys& primary key constraints
   unique
   PK - not null and unique constraint checks
2. foreign key constraint
   any FK can only contain either null values or values from a parent table’s PK or a candidate key
3. not null constraint
4. domain constraints
   a set of legal scalar values for an attribute
   an attribute type can be date, integer, enumerated list (red, white, black)
   可以直接在create table时候插入

CONSTRAINT `ck_staffemail` CHECK ((`StaffEmail` like '%@hlhl.com'))	

或者create domain - alter domain - drop domain

create domain sexType as char(1)
default 'M'
check (value in ('M', 'F'))

但是

create assertion staffnothavingtoomuch
check (not exists (select staffNo from propertyforrent group by staffno
having count(*)>100))

?为什么这样不行

field check
a. data type restrictions: attributes can be restricted to contain only certain types of values
e.g. numeric attributes, date attributes
b. limit or range checks: attribute values to fall between a minimum and maximum value
e.g. hours worked in a day must fall between 0 and 24
completeness checks: all values contain the maximum number of characters
e.g. card number require 16 characters, a completeness check would reject any values with less than 16 characters
format /pattern checks: some attributes must exhibit specified combinations of characters
e.g. account number code - NI233
set membership (enumeration) check: attributes only take on one of a limited set of valeus
e.g. gender attribute should only contain F or M
check digit: used when accuracy is extremely important

5. business rules constraint
   (general constraint)?

record checks: examine the relationship between the values of 2+ attributes within a record
completeness checks: determine if values for all attributes required for the stage of the record are present

order(purchase_order_no, vendor_no, order_date, order_amount, received_date, received_amount, paid_date, paid_amount)
e.g. when the order is received, received date and received amount must have values

check (qty_received >=0 and 
	(qty_received <=1.1*qty_ordered 
	or 
	qty_received = qty_ordered))

customer master(current_balance, credit limit)

check (current_balance - charge >= credit_limit)

reasonableness: if the relationship of the values of 2+ attributes is plausible
e.g. order_date > received _date