DB2 Check Vs. Default Constraint Top Differences

Here are the differences between Check and Default constraints in DB2. They act like check-post; they validate the values for constraint criteria. When the user inserts values into a Table, the constraints check those.

SQL Query for Check Constraint

The CHECK constraint ensures that only values in a certain range are allowed to be in a table column. Below example shows how to use the CHECK constraint:

CREATE TABLE employee (
    empid INTEGER,
    name VARCHAR(30),
    ssn VARCHAR(11) NOT NULL,
    salary INTEGER CHECK (salary >= 5000),
    job VARCHAR(10) CHECK (job IN ('Engineer', 'Sales', 'Manager')));

The salary column must have a value greater than or equal to 5000 when a row is inserted into or updated in the table. The job column must have one of the three values specified (case sensitive). The constraint expression can have the values as specified previously, or it could be a sub–SELECT statement that returns the set of specified valid values.

Points to Remember

Constrains define while creating the Table, not while inserting values. This is an interview question.

SQL Query for Default Constraint

The DEFAULT constraint allows you to specify a default value to a column when a row is written into the table. You specify a DEFAULT constraint as shown in the following example:

CREATE TABLE employee (
    empid INTEGER,
    name VARCHAR(30),
    ssn VARCHAR(11) WITH DEFAULT '999-99-999');

The following example INSERT statement will show how this constraint works:

INSERT INTO employee (empid, name)
    VALUES (005, 'Smith, James');

Note that the ssn field is not referenced. Normally this could cause a problem with the INSERT statement. But in this case, a default value for ssn has been specified, so no error is raised.

Summary

  1. Constraints define during create Table
  2. Constraints verify the values for criteria

Related Posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.