Constraints

Use

You can use constraints to restrict the domain of the data type in a column.

With just a few exceptions, you can formulate in a CONSTRAINT definition everything that also counts as a search condition. You can use the AND, OR and NOT operators to link together several conditions. You can address as many columns as required. Note, however, that constraints reduce the speed of the database system when changes are made to entries in the table.

Simple Constraints: You can formulate constraints that refer to one table column only.

Complex Constraints: In a complex constraint, you formulate conditions that refer to several columns in the table.

Prerequisites

You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

SQL Reference Manual, CONSTRAINT Definition (constraint_definition)

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

Creating a Constraint

You can specify a constraint when you define the table or add it to an existing table at a later stage.

Creating a Constraint During Table Definition

You can use the CREATE TABLE statement to define constraints when you create a table.

Delete the city table, if necessary.

DROP TABLE hotel.city
Create the city table.

CREATE TABLE hotel.city

(zip CHAR(5) PRIMARY KEY CONSTRAINT zip_cons CHECK

SUBSTR(zip,1,1) BETWEEN '0' AND '9' AND

SUBSTR(zip,2,1) BETWEEN '0' AND '9' AND

SUBSTR(zip,3,1) BETWEEN '0' AND '9' AND

SUBSTR(zip,4,1) BETWEEN '0' AND '9' AND

SUBSTR(zip,5,1) BETWEEN '0' AND '9',

name CHAR(30) NOT NULL,

state CHAR(2) NOT NULL)

The city table is created with a simple constraint in the zip code column. This constraint ensures that only digits from 0 to 9 can be entered for the zip code. All other characters will be rejected. When you define a constraint, you specify implicitly that the NULL value is not permitted as an input.

Specifying NOT NULL has the effect that a value has to be assigned to a column. This column is then designated as mandatory.

See also: SQL Reference Manual, CREATE TABLE Statement (create_table_statement)

Creating Constraints for an Existing Table Retroactively

You can use the ALTER TABLE statement to add constraints to existing tables.

ALTER TABLE hotel.customer ADD CONSTRAINT zip_cons CHECK

SUBSTR(zip,1,1) BETWEEN '0' AND '9' AND

SUBSTR(zip,2,1) BETWEEN '0' AND '9' AND

SUBSTR(zip,3,1) BETWEEN '0' AND '9' AND

SUBSTR(zip,4,1) BETWEEN '0' AND '9' AND

SUBSTR(zip,5,1) BETWEEN '0' AND '9'

A simple constraint is added to the zip code column in the customer table. This constraint ensures that only digits from 0 to 9 can be entered for the zip code. All other characters will be rejected.

ALTER TABLE hotel.customer ADD CONSTRAINT cno_cons CHECK cno > 10

//

ALTER TABLE hotel.customer ADD CONSTRAINT title_cons CHECK title IN ('Mr', 'Mrs', 'Company')

Additional simple constraints are defined for the customer table. The customer number must be greater than 10; you are permitted to enter only one of the character sets Mr, Mrs or Company in the title column.

ALTER TABLE hotel.reservation ADD CONSTRAINT staying CHECK departure > arrival

A complex constraint has been defined for the reservation table. In the reservation table, check that the arrival date is before the departure date.

See also: SQL Reference Manual, ALTER TABLE Statement (alter_table_statement), ADD Definition (add_definition)

Changing a Constraint

You can use the ALTER TABLE statement to change existing constraints.

ALTER TABLE hotel.customer ALTER CONSTRAINT cno_cons CHECK cno > 0

For the customer table, the simple constraint cno_cons has changed. The customer number must be greater than 0.