INTEGRITY CONSTRAINTS

- An IC describes conditions that must be satisfied by every legal instance of a (set of) relation.

- Inserts/deletes/updates that violate IC's are disallowed.

- Help to express application semantics (eg ssn is a primary key)

- Used to prevent data inconsistencies (eg sname has to be string, age should be less than 40)

Different types of constraints

- Key constraints (primary key or candidate key)

- Referential integrity constraints (foreign key constraints)

- Domain constraints (or value constraints)

• Primary Key ( => pk) definition

  CREATE TABLE customer (cid NUMBER(8) CONSTRAINT customer_cid_pk PRIMARY KEY,
  name VARCHAR2(20), address VARCHAR2(30));

  Remark: primary key field is assumed to be NOT NULL and UNIQUE.
  

• Composite primary key

  CREATE TABLE works_on (ssn CHAR(14), pno CHAR(2), hours NUMBER(2),
  CONSTRAINT customer_cid_pk PRIMARY KEY (ssn, pno));

• Candidate key definition

  CREATE TABLE bank_account
  (acc# CHAR(10) PRIMARY KEY, name VARCHAR2(20), address VARCHAR2(30),
  ssn CHAR(9) UNIQUE); -- this is a candidate key

• Composite candidate key

  CREATE TABLE city_weather
  (city VARCHAR2(13) NOT NULL, sdate DATE NOT NULL, noon_temp NUMBER (4,1),
  UNIQUE (city, sdate)); /* composite candidate key */

• Foreign key definition (Specifying Referential Integrity constraint)

  [CONSTRAINT <constraint name>] REFERENCES <referenced table name> [(<reference column>)]

  CREATE TABLE cust_order (ordid NUMBER(4) PRIMARY KEY, odate DATE,
  custid NUMBER(8) CONSTRAINT cust_order_custid_fk REFERENCES customer(cid));

  - Enforces referential integrity - NULL or a matching value
  - Referenced column must have a UNIQUE specification
  - When referenced column name is not specified, the primary key is used as the referenced column
  

• Composite foreign key

  CREATE TABLE custwork (ssn CHAR(14) NOT NULL, pno NUMBER(4), name VARCHAR2(30),
  CONSTRAINT custwork_fk FOREIGN KEY(ssn, pno) REFERENCES works_on);

• Value (or domain) Constraints

  - To restrict what data can be entered into a given field and to specify a default value.

  [CONSTRAINT <constraint name>] CHECK (<search condition>)

  CREATE TABLE customer (cid NUMBER(5) CONSTRAINT customer_cid_pk PRIMARY KEY,
  ccredit NUMBER(2) CONSTRAINT customer_ccredit_cc CHECK ((ccredit > 0) AND (ccredit < 12));
  

Other value constraints: NOT NULL, UNIQUE, CHECK (condition), DEFAULT

NOT NULL
Forces the user to enter a value into a column.
Use when the column cannot be left blank.

UNIQUE
Use when no duplicate values are allowed in the column.

Remark:
NULL values, however, may appear in several rows if NOT NULL is not used in conjunction with UNIQUE
UNIQUE and PRIMARY KEY are not used together

Example - create table with column constraints :

CREATE TABLE Salesperson
(empid NUMBER(5) CONSTRAINT Salesperson_empid_pk PRIMARY KEY,
ename CHAR(15) CONSTRAINT
Salesperson_ename_nn NOT NULL,
rank NUMBER(1) DEFAULT 1 CONSTRAINT
Salesperson_rank_cc CHECK (rank IN (1,2,3)),
salary NUMBER(7,2) CONSTRAINT
Salesperson_salary_nn NOT NULL
CONSTRAINT Salesperson_salary_cc
CHECK (salary >= 1000.00));

eg: without constraint names:

CREATE TABLE Customer
(custid NUMBER(5) PRIMARY KEY,
cname CHAR(15) NOT NULL,
caddress VARCHAR2(25) NOT NULL,
credit CHAR(1) NOT NULL
CHECK (credit IN (‘A’, ‘B’, ‘C’)));