Database Tutorial

Week #2: Articles

Learning Outcomes

• Learn how to create a column with a CHECK constraint.
• Learn how to modify a column with a CHECK constraint.

• NOT NULL Constraint
• CHECK Constraint
• UNIQUE Constraint
• PRIMARY KEY Constraint
• FOREIGN KEY Constraint

CHECK Constraint

CHECK constraints are not available in all relational databases. CHECK constraints let you check the value of a column value before allowing you to insert or update a row.

Oracle lets you implement a CHECK constraint in a CREATE statement as either an in-line or out-of-line constraint. You also may use the ALTER statement to add a constraint after you create the table.

The following examples demonstrate how you implement a CHECK constraint in the Oracle database. You have two options. One uses an in-line syntax and the other a table-level or out-of-line syntax.

• CREATE Statement:
• You can define a CHECK constraint on the same line as you define the column. This approach is called placing an inline constraint on a column.

  The following sample table has two named inline constraints:

  CREATE TABLE sample ( sample_id NUMBER CONSTRAINT sample_pk PRIMARY KEY , sample_text VARCHAR2(20) CONSTRAINT sample_ck CHECK (sample_text IS NOT NULL));

  CREATE TABLE sample ( sample_id NUMBER CONSTRAINT sample_pk PRIMARY KEY , sample_text VARCHAR2(20) CONSTRAINT sample_ck CHECK (sample_text IS NOT NULL));

  The first inline constraint is a PRIMARY KEY constraint on the sample_id column. You should note that the PRIMARY KEY constraint makes a column applies three rules. It verifies the sample_id column is mandatory and unique in the set of rows inside the sample table, and designates the sample_id column as a primary key that you can refer to from foreign key columns.

  The second is a CHECK constraint that restricts the sample_text column value to any not null value. Any attempt to add a row with a null value in the sample_text column would return the following error:

  * ERROR AT line 1: ORA-02290: CHECK CONSTRAINT (STUDENT.SAMPLE_CK) violated

  * ERROR at line 1: ORA-02290: check constraint (STUDENT.SAMPLE_CK) violated

  If you describe the sample table with the DESCRIBE command in SQL*Plus, you’ll see:

  Name Null? Type ----------------------------------------- -------- ---------------------------- SAMPLE_ID NOT NULL NUMBER SAMPLE_TEXT VARCHAR2(20)

  Name Null? Type ----------------------------------------- -------- ---------------------------- SAMPLE_ID NOT NULL NUMBER SAMPLE_TEXT VARCHAR2(20)

  You should note that the PRIMARY KEY constraint applies three rules to a column. The PRIMARY KEY constraint verifies the column is mandatory and unique in the set of rows inside the table, and designates it as a primary key that you can refer to from foreign key columns.

  While the example shows you how to create a CHECK constraint that mimics a NOT NULL constraint, you shouldn’t use a CHECK constraint in lieu of a NOT NULL constraint. A more reasonable in-line constraint would restrict the values you insert or update to a specific value or a set of values

  CREATE TABLE customer ( customer_id NUMBER CONSTRAINT customer_pk PRIMARY KEY , salutation VARCHAR2(4) CONSTRAINT customer_ck CHECK (salutation = 'Ms.') , customer_name VARCHAR2(40));

  CREATE TABLE customer ( customer_id NUMBER CONSTRAINT customer_pk PRIMARY KEY , salutation VARCHAR2(4) CONSTRAINT customer_ck CHECK (salutation = 'Ms.') , customer_name VARCHAR2(40));

  The CHECK constraint on the salutation column ensures that the only salutation is 'Ms.', which doesn’t typically meet most business needs. You can modify the CHECK constraint on the salutation column so that it checks for 'Mr.', 'Ms.', 'Miss', 'Mrs.', or 'Dr.' by using a lookup operator, like:

  CREATE TABLE customer ( customer_id NUMBER CONSTRAINT customer_pk PRIMARY KEY , salutation VARCHAR2(4) CONSTRAINT customer_ck CHECK (salutation IN ('Mr.','Ms.','Miss','Mrs.','Dr.')) , customer_name VARCHAR2(40));

  CREATE TABLE customer ( customer_id NUMBER CONSTRAINT customer_pk PRIMARY KEY , salutation VARCHAR2(4) CONSTRAINT customer_ck CHECK (salutation IN ('Mr.','Ms.','Miss','Mrs.','Dr.')) , customer_name VARCHAR2(40));

• You can define a CHECK constraint on a different line after you define all of the columns. A constraint after the definition of the list of columns is a table-level or out-of-line constraint. They can be very helpful when you want to add a default value for a column, like the salutation column. The following example uses a 'Mr.' as the default value and a table-level constraint with a list of possible values.

  CREATE TABLE customer ( customer_id NUMBER CONSTRAINT customer_pk PRIMARY KEY , salutation VARCHAR2(4) DEFAULT 'Mr.' , customer_name VARCHAR2(40) , CONSTRAINT customer_ck CHECK (salutation IN ('Mr.','Ms.','Miss','Mrs.','Dr.')));

  CREATE TABLE customer ( customer_id NUMBER CONSTRAINT customer_pk PRIMARY KEY , salutation VARCHAR2(4) DEFAULT 'Mr.' , customer_name VARCHAR2(40) , CONSTRAINT customer_ck CHECK (salutation IN ('Mr.','Ms.','Miss','Mrs.','Dr.')));

  If you forget to define a CHECK constraint when you creating the table, you can also use the ALTER statement to add, change, or remove CHECK constraints.

• ALTER Statement:
• You can add or drop a CHECK constraint when you want to modify an existing CHECK constraint. There is no syntax to modify an existing CHECK constraint.

  You drop an existing customer_ck CHECK constraint with the following syntax:

  ALTER TABLE customer DROP CONSTRAINT customer_ck;

  ALTER TABLE customer DROP CONSTRAINT customer_ck;

• After you drop the customer_ck CHECK constraint, you can add a new version of the customer_ck CHECK constraint with the following syntax:

  ALTER TABLE customer ADD CONSTRAINT customer_ck CHECK (salutation IN ('Mr.','Ms.','Miss','Mrs.'));

  ALTER TABLE customer ADD CONSTRAINT customer_ck CHECK (salutation IN ('Mr.','Ms.','Miss','Mrs.'));