Types of Constraints

• Data Type
• List of Values (enumerated data type)
• Unique
• Not Null

• State Constraints
• Foreign Key (Referential integrity)
• Other: (e.g., if type = mgr., salary <= 100000)
• Transition Constraints (e.g., salary(t+1) <= 1.10 * salary(t))

Enforcement in Oracle

Domain and Foreign Key Constraints

Declare Data types and constraints in tables

Example

CREATE TABLE order-detail
     (order_id Number Constraint
          fk_oid REFERENCES scott.order(order_id),
     (part_no Number Constraint
          fk_pno REFERENCES part(part_no),
     (quantity Number Constraint nn_qty Not NULL
          Constraint check_qty_low CHECK (quantity > 0),
     (cost Number Constraint
          check_cost CHECK (cost>0),
     (Constraint pk_od PRIMARY KEY (order_id, part_no))

Legal Constraints in Oracle

• NOT NULL Nulls not allowed
• UNIQUE Values must be unique, but nulls allowed
• PRIMARY KEY Values must be unique, nulls not allowed
• REFERENCES Causes a foreign key relationship
• CHECK defines a condition that must be satisfied

REFERENCES in more detail

 With the specification of a table attribute

 CONSTRAINT name REFERENCES table(attribute) [ON DELETE CASCADE]

 In a Constraint Row

 CONSTRAINT name FOREIGN KEY (attribute1) REFERENCES table(attribute) [ON DELETE CASCADE]

 The ON DELETE CASCADE clause tells Oracle to delete the child tables when the corresponding parent table row is deleted. If the ON DELETE CASCADE clause is left off, Oracle will not allow the deletion of a parent row unless all the child rows are deleted first.

 Oracle supports update restrict, delete restrict and delete cascade. It does not easily support:

update cascade

changes made to a parent key are cascaded to child

update with null

changes allowed to parent, child attribute changed to null

delete with null

parent deleted, child attribute changed to null

More Examples

CREATE TABLE dept
     (deptno NUMBER(2)
          CONSTRAINT pk_dept PRIMARY KEY
          CONSTRAINT ck_deptno
               CHECK (deptno BETWEEN 10 and 99),
     dname VARCHAR2(9)
          CONSTRAINT ck_dname CHECK (dname=UPPER(dname))
     loc VARCHAR2(10)
          CONSTRAINT ck_loc CHECK (loc IN
               (`DALLAS','BOSTON','CHICAGO')))

CREATE TABLE emp
     (empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
     ename VARCHAR2(10),
     job VARCHAR2(9),
     hiredate DATE,
     sal NUMBER(7,2)
     comm NUMBER(7,2),
     deptno NUMBER(2),
     CONSTRAINT fk_deptno FOREIGN KEY (deptno)
          REFERENCES dept(deptno),
     CONSTRAINT ck_total CHECK (sal + comm <=5000))

Triggers

Examples

CREATE TRIGGER emp_permit_changes
     BEFORE
     DELETE or INSERT or UPDATE
     ON emp
     DECLARE
          dummy INTEGER;
     BEGIN
          /* If today is a Saturday or Sunday, then error.*/
          IF (TO_CHAR(SYSDATE, `DY') = `SAT' OR
               (TO_CHAR(SYSDATE, `DY') = `SUN')
               THEN raise_application_error(-2051,
                    `May not change on weekend');
          END IF;
          /* Check holiday */
          SELECT COUNT (*)
               INTO dummy
               FROM company_holidays
               WHERE day = TRUNC(SYSDATE);
          IF dummy > 0
               THEN raise_application_error(-20501,
                    `May not change on holiday');
          END IF;
     END;

CREATE TRIGGER salary_check
     BEFORE
     INSERT OR UPDATE OF sal, job
     ON emp
     FOR EACH ROW
     WHEN (new.job<>`PRESIDENT')
     DECLARE
          minsal    NUMBER;
          maxsal    NUMBER;
     BEGIN
          /*Get the min and max salary from table*/
          SELECT minsal, maxsal
               INTO minsal, maxsal
               FROM sal_guide
               WHERE job = :new.job;
          /*Check for Error*/
          IF (:new.salmaxsal)
               THEN raise_application_error(-20601, `Salary
                    out of range for job')
          END IF;
     END;

PowerBuilder

Database Painter

• Table attribute data types
• Table attribute Edit specifications (e.g., mask, radio buttons, list box)
• Table attributes validation specification
• Foreign key specification in table
• Primary key specification in table
• Not null specification in table

Datawindow Painter

Windows and Scripts

Exit to : Oracle Hints and Tips