Database Tutorial

Week #2: Articles

Learning Outcomes

• Learn how to create a column with a NOT NULL constraint.
• Learn how to modify a column with a NOT NULL constraint.

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

NOT NULL Constraint

A NOT NULL constraint prevents the insertion of any null value in a column. A NOT NULL constraint changes the default optional nature of the column to a mandatory criteria. You can insert or update any value or no value in an optional column. A mandatory column requires you to insert or update a NOT NULL constrained column with a real value.

The cardinality of all columns are optional by default. You represent cardinality of an optional column by specifying the minimum and maximum value, like 0..1. You represent cardinality of a mandatory column by specifying the minimum cardinality as 1..1. A NOT NULL constraint is the only way to change the minimum cardinality from optional, or zero, to mandatory, or one.

A CHECK constraint can mimic a NOT NULL constraint. Technically, a CHECK constraint checks whether the value meets the criteria of an expression, like:

column_name <> NULL

While a CHECK constraint imposes a minimum cardinality of 1, the check occurs after the SQL statement engine tries to insert a row. Also, CHECK constraints aren’t available in many relational database management systems.

A table definition shows a NOT NULL constrained column but doesn’t show a CHECK constrained column. That means developers may believe they can insert null values into a column with only a CHECK constraint because the column appears to be optional rather than mandatory.

The following examples demonstrate how you implement a NOT NULL constraint in the Oracle database. You have two options to make a column mandatory when you create a table and one option after you create a table.

• CREATE Statement:
• You define the NOT NULL constraint on the same line as you define the column, which is called an inline constraint.

  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_nn NOT NULL );

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

  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 NOT NULL VARCHAR2(20)

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

  You should note that the PRIMARY KEY constraint makes a column applies three rules. It 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.

• You can’t define the NOT NULL constraint as a table-level or out-of-line constraint. That’s because you must define NOT NULL constraints as in-line constraints. For reference, a constraint defined after the definition of the list of columns is a table-level or out-of-line constraint.
• ALTER Statement:
• You redefine the column by including an inline NOT NULL constraint. You can actually perform a very similar command to remove a constraint. Leveraging the sample table from the earlier step, the following command removes the sample_text column’s NOT NULL constraint.

  ALTER TABLE sample MODIFY (sample_text VARCHAR2(20) NULL);

  ALTER TABLE sample MODIFY (sample_text VARCHAR2(20) NULL);

  Describing 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 can reapply the NOT NULL constraint with this command:

  ALTER TABLE sample MODIFY (sample_text VARCHAR2(20) CONSTRAINT sample_nn NOT NULL);

  ALTER TABLE sample MODIFY (sample_text VARCHAR2(20) CONSTRAINT sample_nn NOT NULL);

  Re-describe the sample table in SQL*Plus, you’ll see:

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

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

  Naturally, you can’t apply a NOT NULL constraint to a column in a table when there is one or more rows where that column value contains a null value.