Checking for Specific Data Conditions- Tables and Constraints

A check constraint works well for lookups when you have a short list of fairly static values, such as a column that can be either Y or N. In this situation, the list of values most likely won’t change, and no information needs to be stored other than Y or N, so a check constraint is the appropriate solution.

If you have a long list of values that needs to be periodically updated, then a table and a foreign key constraint are a better solution.

Also, a check constraint works well for a business rule that must always be enforced and that can be written with a simple SQL expression. If you have sophisticated business logic that must be validated, then the application code is more appropriate.

You can define a check constraint when you create a table. The following enforces the ST_FLG column to contain either a 0 or 1:

SQL> create table emp( emp_id number, emp_name varchar2(30),

st_flg number(1) CHECK (st_flg in (0,1)) );

A slightly better method is to give the check constraint a name:

SQL> create table emp( emp_id number, emp_name varchar2(30),

st_flg number(1) constraint st_flg_chk CHECK (st_flg in (0,1)) );

A more descriptive way to name the constraint is to embed information in the constraint name that describes the condition that was violated; for example,

SQL> create table emp( emp_id number, emp_name varchar2(30),

st_flg number(1) constraint “st_flg must be 0 or 1” check (st_flg in (0,1)) );

You can also alter an existing column to include a constraint. The column must not contain any values that violate the constraint being enabled:

SQL> alter table emp add constraint “st_flg must be 0 or 1” check (st_flg in (0,1));

Note The check constraint must evaluate to a true or unknown (NULL) value in the row being inserted or updated.You cannot use subqueries or sequences in a check constraint.Also, you can’t reference the SQL functions UID, USER, SYSDATE, or USERENV or the pseudocolumns LEVEL or ROWNUM.

Enforcing NOT NULL Conditions

Another common condition to check for is whether a column is null; you use the NOT NULL constraint to do this. The NOT NULL constraint can be defined in several ways. The simplest technique is shown here:

SQL> create table emp( emp_id number, emp_name varchar2(30) not null);

A slightly better approach is to give the NOT NULL constraint a name that makes sense to you. Naming the constraint will allow you to see what the constraint is for instead of a system-generated constraint name, which might be confused with a primary or foreign key constraint:

SQL> create table emp( emp_id number, emp_name varchar2(30) constraint emp_name_nn not null);

Use the ALTER TABLE command if you need to modify a column for an existing table. For the following command to work, there must not be any NULL values in the column being defined as NOT NULL:

SQL> alter table emp modify(emp_name not null);

Note If there are currently NULL values in a column that is being defined as NOT NULL, you must first update the table so that the column has a value in every row.