Integrity constraints in tables

Internal Integrity Constraints

Integrity constraints allow you to specify a condition on column values for each table's record. If the constraint is broken after manipulating data in table (insert and update) in some record, an error 40005 (CHECK_CONSTRAIN) occurs and transaction is rolled back.

If the error is in the integrity constraint notation, only a warning (ERROR_IN_CONSTRS) occurs and transaction is not rolled back.

Integral constraint is specified as follows:

internal_integrity_constraint ::= CHECK ( condition ) [ evaluation ]

You can alternatively describe internal integrity constraint concerning only a single column as a column property.

Example: a table with a condition on a number >0 and active referential integrity to the company classifier (superior table COMPANIES)

CREATE TABLE `ORDERS` (
`COMPANY_ID` INTEGER ,
`ORDERED` NUMERIC(14,2) ,
...
CONSTRAINT `Companies` INDEX (`COMPANY_ID`),
CONSTRAINT `Check_gt0` CHECK (ordered>0) INITIALLY IMMEDIATE,
CONSTRAINT `Company-Obj` FOREIGN KEY (`COMPANY_ID`) REFERENCES `COMPANIES`(`COMPANY_ID`) 
	ON UPDATE CASCADE ON DELETE CASCADE)

When are the Integrity Constraints Checked?

Server may check integrity constraints either at the statement completion or at the transaction completion. The check time is specified with the INITIALLY IMMEDIATE or INITIALLY DEFERRED keywords.

Database Table Indexes

Database Tables

Tables Referential Integrity