DB2 provides several ways to control what data can be stored in a column. These features are called constraints or rules that the database manager enforces on a data column or set of columns.
DB2 provides three types of constraints: unique, referential integrity, and check constraints.
The following sections provide detailed descriptions of each type of constraint.
Unique constraints are used to ensure that values in a column are unique. Unique constraints can be defined over one or more columns. Each column included in the unique constraint must be defined as NOT NULL.
Unique constraints can be defined either as the
UNIQUE constraint. These are defined when a
table is created as part of the
statement, or added after the table is created using the
ALTER TABLE statement.
When do you define a
PRIMARY KEY, and when
do you define a
UNIQUE key? This depends on
the nature of the data. Generally it is good practice to define a
primary key on each table if possible. In the previous example, the
BOOKS table has a BOOKID column which is used to uniquely identify a
book, and so is defined as the primary key. This value is also used in
other tables that contain information related to this book. DB2 allows
only one primary key to be defined on a table.
The ISBN number column needs to be unique but is not a value that is
used in other tables. In this case, the ISBN column can be defined as
UNIQUE, as shown in Listing 22.
Listing 22. Defining a UNIQUE key
CREATE TABLE BOOKS ( BOOKID INTEGER NOT NULL PRIMARY KEY, BOOKNAME VARCHAR(100), ISBN CHAR(10) NOT NULL CONSTRAINT BOOKSISBN UNIQUE);
The CONSTRAINT keyword lets you specify a name for the constraint. In
this example, the name of the unique constraint is BOOKSISBN. Use this
name in the
ALTER TABLE statement if you
want to drop the specific constraint.
DB2 lets you define only one primary key on a table; however, you can define multiple unique constraints.
Whenever you define a
PRIMARY KEY or
UNIQUE constraint on a column, DB2 creates
a unique index to enforce uniqueness on the column. DB2 does not let
you create more than one unique index defined on the same columns.
Therefore, you cannot define a
UNIQUE constraint on the same columns.
For example, both of the following statements against the BOOKS table
fail because a
PRIMARY KEY already exists,
as shown in Listing 23.
Listing 23. Failed statements due to PRIMARY KEY
ALTER TABLE BOOKS ADD CONSTRAINT UNIQUE (BOOKID); CREATE UNIQUE INDEX IBOOKS ON BOOKS (BOOKID);
Referential integrity constraints are used to define relationships between tables and ensure that these relationships remain valid.
Suppose you have one table that holds information about authors and another table that lists the books that those authors have written. There is a relationship between the BOOKS table and the AUTHORS table, where each book has an author and that author must exist in the AUTHOR table. Each author has a unique identifier stored in the AUTHORID column. The AUTHORID is used in the BOOKS table to identify the author of each book. To define this relationship, define the AUTHORID column of the AUTHORS table as a primary key and then define a foreign key on the BOOKS table to establish the relationship with the AUTHORID column in the AUTHORS table, as shown in Listing 24.
Listing 24. Establishing a relationship with the AUTHORID column in the AUTHORS table
CREATE TABLE AUTHORS ( AUTHORID INTEGER NOT NULL PRIMARY KEY, LNAME VARCHAR(100), FNAME VARCHAR(100)); CREATE TABLE BOOKS ( BOOKID INTEGER NOT NULL PRIMARY KEY, BOOKNAME VARCHAR(100), ISBN CHAR(10), AUTHORID INTEGER REFERENCES AUTHORS);
The table AUTHOR has a primary key that is mentioned within another table called BOOKS. AUTHOR is called the parent table in the relationship, and BOOKS the dependent table in the relationship.
The constraint can be added when the dependent table is created, or the dependent table can be altered to add the constraint later.
You can also define relationships between rows of the same table. In such a case, the parent table of the relationship and dependent table are the same, as shown in Listing 25.
Listing 25. Define relationships between rows
CREATE TABLE EMPLOYEE ( PERSONID CHAR(10) NOT NULL PRIMARY KEY, MANAGER CHAR(10) REFERENCES EMPLOYEE);
Similarly, if you added a column FIRSTBOOK to the AUTHORS table, there could be a second relationship between the two tables where the roles are reversed, since a particular BOOKID would then be mentioned within the AUTHORS table for each author as his/her FIRSTBOOK. In that relationship the BOOKS table would be the parent, as shown in Listing 26.
Listing 26. BOOKS table as parent
ALTER TABLE AUTHORS ADD FIRSTBOOK INTEGER REFERENCES BOOKS;
When you define referential constraints on a set of tables, DB2 enforces referential integrity rules on those tables when update operations are performed against them.
- DB2 ensures that only valid data is inserted into columns where referential integrity constraints are defined. This means that you must always have a row in the parent table with a key value that is equal to the foreign key value in the row that you are inserting into a dependent table. For example, if a new book is being inserted into the BOOKS table with an AUTHORID of 437, then there must already be a row in the AUTHORS table where AUTHORID is 437.
- DB2 also enforces rules when rows that have dependent rows in a dependent table are deleted from a parent table. The action DB2 takes depends on the delete rule defined on the table. There are four rules that can be specified: RESTRICT, NO ACTION, CASCADE and SET NULL.
- If RESTRICT or NO ACTION is specified, DB2 does not allow the parent row to be deleted. The rows in dependent tables must be deleted before the row in the parent table.
- If CASCADE is specified, then deleting a row from the parent table automatically also deletes dependent rows in all dependent tables.
- If SET NULL is specified, then the parent row is deleted from the parent table and the foreign key value in the dependent rows is set to null (if null able).
- When updating key values in the parent table, there are two rules that can be specified: RESTRICT and NO ACTION. RESTRICT does not allow a key value to be updated if there are dependent rows in a dependent table. NO ACTION causes the update operation on a parent key value to be rejected if, at the end of the update, there are dependent rows in a dependent table that do not have a parent key in the parent table.
Check constraints are used to verify that column data conforms rules defined for the column. DB2 ensures that the constraint is not violated during inserts and updates.
Suppose that you add a column to the BOOKS table for a book type, and the values that you wish to allow are F (fiction) and N (nonfiction). You can add a column BOOKTYPE with a check constraint as shown in Listing 27.
Listing 27. Add column BOOKTYPE with check constraint
ALTER TABLE BOOKS ADD BOOKTYPE CHAR(1) CHECK (BOOKTYPE IN ('F','N') );
You can define check constraints when you create the table or add them
later using the
ALTER TABLE SQL statement.
You can modify check constraints by dropping and then recreating them
ALTER TABLE SQL statement.