Skip to content

DDL: Constraints

A constraint is a restriction on the data that can be stored in a table. Like the datatype of a column, constraints form part of the definition of a table and so appear in the CREATE TABLE statement. Essentially there are two types of constraint: integrity constraints which control the structural aspects of the data, and data constraints which define the domain of a column. These correspond to the two types of business rule discussed in week 5.

In most cases, a constraint refers only to a single column in the table and can be included as part of the column definition in the CREATE TABLE statement. These are referred to as column constraints. Sometimes, however, a constraint needs to reference more than one column - an example would be a composite primary key - and then it is referred to as a table constraint. The definition of a table constraint appears at the end of the list of columns in the CREATE TABLE statement. The following discussion provides examples of both types of constraint.

Integrity constraints

Constraints are used to maintain entity and referential integrity. That is, they define primary and foreign keys. We will use the following schema to show how this is done:

Students and modules

Notice that REGISTRATION is a link table which resolves the *:* relationship between STUDENT and MODULE.

Because the primary key of STUDENT consists of a single column, it can simply be identified as such as part of the column definition:

1
2
3
4
5
6
    CREATE TABLE student (
        matric_no     VARCHAR2(8) PRIMARY KEY,
        first_name    VARCHAR2(20),
        last_name     VARCHAR2(20),
        date_of_birth DATE
    );

In contrast, the primary key of REGISTRATION includes two columns and must therefore be defined as a table constraint:

1
2
3
4
5
6
    CREATE TABLE registration (
        matric_no    VARCHAR2(8),
        module_code  VARCHAR2(8),
        result       NUMBER(3),
        PRIMARY KEY (matric_no, module_code)
    );

Notice that the table constraint definition appears as part of the column list. That is, it is separated from the last column definition with a comma and appears inside the brackets.

The columns matric_no and module_code in the REGISTRATION table are also foreign keys which correspond to primary keys in the other two tables. They also need to be identified in the CREATE TABLE statement:

1
2
3
4
5
6
    CREATE TABLE registration (
        matric_no    VARCHAR2(8) REFERENCES student(matric_no),
        module_code  VARCHAR2(8) REFERENCES module(module_code),
        result       NUMBER(3),
        PRIMARY KEY (matric_no, module_code)
    );

Notice that the relationship is defined by identifying the foreign key. This means that there is no mention of the relationship in the related table. After the REFERENCES keyword, the names of both the related table and the primary key column are required.

Data constraints

There are three kinds of data constraint:

  • NOT NULL: the column must have a value
  • UNIQUE: the column value must be unique in the table
  • CHECK: the column value conforms to an arbitrary condition

NOT NULL constraints can only apply to single columns and therefore always appear as column constraints:

1
2
3
4
5
6
    CREATE TABLE module (
        module_code    VARCHAR2(8) PRIMARY KEY,
        module_title   VARCHAR2(40) NOT NULL,
        level          NUMBER(2) NOT NULL,
        credits        NUMBER(2) NOT NULL
    );

In the example above, a NOT NULL constraint is not required for module_code because it is already implied by the PRIMARY KEY constraint.

When the UNIQUE constraint is applied to a column, it essentially defines an alternative key for the table. Although looking through the University's list of modules will show that there are in fact modules in different schools with the same name, it might be more sensible to insist that module titles are also unique:

1
2
3
4
5
6
    CREATE TABLE module (
        module_code    VARCHAR2(8) PRIMARY KEY,
        module_title   VARCHAR2(40) NOT NULL UNIQUE,
        level          NUMBER(2) NOT NULL,
        credits        NUMBER(2) NOT NULL
    );

Notice that the module_title column now has two constraints which simple appear one after the other in the column definition line. The order in which they are mentioned is not important.

The CHECK constraint allows the designer to be very specific about the domain of a column by applying an arbitrary condition to the data. In Scotland, the level of a module is defined by the Scottish Credit and Qualifications Framework (SCQF). Levels 7, 8, 9 and 10 correspond to the four years of an undergraduate degree, and level 11 corresponds to Masters programmes. We could build this into the definition of the table as shown below:

1
2
3
4
5
6
7
    CREATE TABLE module (
        module_code    VARCHAR2(8) PRIMARY KEY,
        module_title   VARCHAR2(40) NOT NULL UNIQUE,
        level          NUMBER(2) NOT NULL
                         CHECK (level BETWEEN 7 AND 11),
        credits        NUMBER(2) NOT NULL
    );

Notice that the condition in the CHECK constraint has the same form as a condition in the WHERE clause of a query.

The standard size of a module is 20 credits; however, at Masters level it is possible to have half modules worth only 10 credits. This could also be made part of the table definition, but because two columns are referenced it would have to be a table constraint as shown below:

1
2
3
4
5
6
7
8
    CREATE TABLE module (
        module_code    VARCHAR2(8) PRIMARY KEY,
        module_title   VARCHAR2(40) NOT NULL UNIQUE,
        level          NUMBER(2) NOT NULL
                         CHECK (level BETWEEN 7 AND 11),
        credits        NUMBER(2) NOT NULL,
        CHECK (credits = 20 OR (level = 11 AND credits = 10))
    );

See the Oracle documentation for further examples.

Constraint independence

The examples above illustrate how to define constraints at the same time as the associated table. However they can be created and dropped independently by using the ALTER TABLE statement. This can be useful when creating a new schema to avoid problems when you come to insert data into the tables, or when there are parallel relationships between tables. Say for example you wanted to create the REGISTRATION table, insert some data, and only then enable the foreign key constraints, you could use the following piece of DDL:

1
2
3
    ALTER TABLE registration
    ADD CONSTRAINT registration_fk1
    FOREIGN KEY (matric_no) REFERENCES student(matric_no);

Notice that in this example, we give the constraint a name which follows the CONSTRAINT keyword. This is optional, and has been omitted from the earlier examples; however, it is needed if you want to be able to drop a constraint independently of its table:

1
2
    ALTER TABLE registration
    DROP CONSTRAINT registration_fk1;

Further reading

Constraint

SQL Constraints