Constraints 

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.

Prerequisites

You require the demo data for the SQL Tutorial.

Start the query tool SQL Studio as database administrator MONA with password RED and log on to the demo database instance DEMODB.

SQL Reference Manual, CONSTRAINT Definition (constraint_definition)

 

Creating a Constraint

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

Simple Constraints

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

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

...

       1.      If necessary, drop the city table.
DROP TABLE hotel.city

       2.      Create the city table as follows:
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.

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

When you define a constraint, you specify implicitly that the NULL value is not permitted as an input.

 

See also:

SQL Reference Manual, CREATE TABLE Statement (create_table_statement)

 

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.

 

Complex Constraints

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

 

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

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)

SQL Reference Manual, 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 cno_cons constraint has changed. The customer number must be greater than 0.

 

See also:

SQL Reference Manual, ALTER Definition (alter_definition)

Evaluating System Tables, CONSTRAINTS

 

Deleting a Constraint

You can use the ALTER TABLE statement to delete a constraint.

 

ALTER TABLE hotel.reservation DROP CONSTRAINT staying

 

See also:

SQL Reference Manual, DROP Definition (drop_definition)

More examples for Data Definition