DB2 Constraints
Constraints help to maintain data integrity without any procedural logic(like triggers, Stored procs). Writing a constraint is quite easy and has two parts to it, namely,
the constraint Name & the check condition.
The check condition is defined by specifying basic predicates (>, <, =, <>, <=, >=), BETWEEN, IN, LIKE, and NULL
There are 4 types of constraints:
1. Data Constraint
2.NOT NULL constraint
3.Unique Constraint
4. Check Constraint
- Data Constraint: DB2 provides different built-in data types classified according to the type of data. As an example,
DOJ DATE -> Date of joining will always take DATE field as input. DB2 will not allow non-date values in DOJ field.
SALARY DECIMAL(9,2) -> DB2 will allow decimal values in SALARY column. - NOT NULL Constraint: This will ensure the column always has some value. This is a column attribute, therefore it will always be defined on column level.
column_name type NOT NULL
3. Unique Constraint: Unique constraint is an SQL rule that ensures that no two values in the same column or in a specific group of columns are the same.A unique constraint can be established using the clauses PRIMARY KEY or using the UNIQUE keyword in the CREATE TABLE or ALTER TABLE. The columns specified in a unique constraint must be defined as NOT NULL or NOT NULL WITH DEFAULT and the data type cannot be LOB.For each unique constraint, an unique index must be created
CREATE TABLE TEST.EMP
(EMPNO CHAR(6) NOT NULL UNIQUE,
NAME VARCHAR(60) NOT NULL,
DEPT CHAR(6),
CONSTRAINT UNIQUE_EMP UNIQUE (EMPNO, NAME)
)IN TABLESPACE_NAME
Until and Unless an unique index is defined on the same column names, the table definition is incomplete.
4. Check Constraints: Determines the set of values that specific columns of a base table can contain. The designated values are explicitly defined in the table DDL. This is a better way to provide data integrity than equivalent programming logic. If the same table is used by multiple programs, it removes the redundancy of coding the same logic for maintaining data integrity in multiple programs. Instead check constraints defined on DDL enforces the rule every time a row is inserted into the table.
CREATE TABLE EMPLOYEE (
EMPNO CHAR(4) NOT NULL,
GENDER CHAR(1) CONSTRAINT GENDER_CHK CHECK (GENDER IN ('M', 'F')),
SALARY DECIMAL(10,2) NOT NULL,
BONUS DECIMAL(10,2) NOT NULL,
CONSTRAINT MIN_SAL CHECK (SALARY >= 1000)
)IN TABLESPACE_NAME
Leave a comment