Integrity Constraints

An integrity constraint guards your databse from accidental damage by checking all inserted data for consistency

Constraints on a single relation

not null

• an attribute cannot be null.
• name varchar(20) NOT NULL

unique

• no two values under this attribute can be equal
• this makes the attribute a candidate key
• this candidate key is allowed to be null, as opposed to a primary key

check

• ensures that any insertion obeys a certain predicate
• check (season in ('Fall', 'Winter', 'Spring', 'Summer'))

Referential Integrity

• ensures that each foreign key has two ends.
• we do this by defining attribute A in table R as a foreign key pointing to primary key P in table S
• FOREIGN KEY (dept_name) REFERENCES department (dept_name) is how we declare a foreign key

Integrity violation response

• In response to any violation of referential integrity, the DBMS can respond in the following ways
  • reject the action that causes the violation
  • cascade that action to the referencers
  • set the value to null
  • set the value to a preset default

Inserting without causing violations

You can do this by: * insert parent before child * set parents to null initially, update on inserting child * defer constraint checking