SQL-J Language Reference
Page 9 of 121

CONSTRAINT clause

A CONSTRAINT clause is an optional part of a CREATE TABLE statement or ALTER TABLE statement. A constraint is a rule to which data must conform. Constraint names are optional.

A CONSTRAINT can be one of the following:

  • a column-level constraint

    Column-level constraints refer to a single column in the table and do not specify a column name (except check constraints). They refer to the column that they follow.

  • a table-level constraint

    Table-level constraints refer to one or more columns in the table. Table-level constraints specify the names of the columns to which they apply. Table-level CHECK constraints can refer to 0 or more columns in the table.

Column constraints include:

  • NOT NULL

    Specifies that this column cannot hold NULL values (constraints of this type are not nameable).

  • NULL

    The opposite of NOT NULL (not really a constraint), it specifies that the column can hold NULL values. Specifying NULL is the same as saying nothing at all, except when the column is included in a PRIMARY KEY constraint.

  • PRIMARY KEY

    Specifies the column that uniquely identifies a row in the table. Also gives the column an implicit NOT NULL constraint (in a CREATE TABLE statement, not in an ALTER TABLE statement).

  • UNIQUE

    Specifies that values in the column must be unique. A NULL value is allowed.

  • FOREIGN KEY

    Specifies that the values in the column must correspond to values in a referenced primary key or unique key column or that they are NULL.

  • CHECK

    Specifies rules for values in the column.

Table constraints include:

  • PRIMARY KEY

    Specifies the column or columns that uniquely identify a row in the table. NULL values are not allowed.

  • UNIQUE

    Specifies that values in the columns must be unique.

  • FOREIGN KEY

    Specifies that the values in the columns must correspond to values in referenced primary key or unique columns or that they are NULL.

NOTE: If the foreign key consists of multiple columns, and any column is NULL, the whole key is considered NULL. The insert is permitted no matter what is on the non-null columns.

  • CHECK

    Specifies a wide range of rules for values in the table.

Column constraints and table constraints have the same function; the difference is in where you specify them. Table constraints allow you to specify more than one column in a PRIMARY KEY, UNIQUE, CHECK, or FOREIGN KEY constraint definition. Column-level constraints (except for check constraints) refer to only one column.

Columns that store Java objects cannot be part of a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraints unless they are orderable.

Syntax

Column-Level Constraint

{
    NOT NULL |
    [ [CONSTRAINT ConstraintName]
    {
        CHECK ( searchCondition) |
        {
            PRIMARY KEY |
            UNIQUE |
             ReferencesSpecification
        } [ PROPERTIES clause ]
    }
}

Table-Level Constraint

[CONSTRAINT ConstraintName]
{
    CHECK ( searchCondition) |
    {
        PRIMARY KEY ( SimpleColumnName [ , SimpleColumnName ]* ) |
        UNIQUE ( SimpleColumnName [ , SimpleColumnName ]* ) |
        FOREIGN KEY ( SimpleColumnName [ , SimpleColumnName ]* )
             ReferencesSpecification
    } [ PROPERTIES clause ]
}

ReferencesSpecification

REFERENCES TableName [ ( SimpleColumnName [ , SimpleColumnName ]* ) ]

searchCondition

A searchCondition is any Boolean expression that meets the requirements specified in Requirements for Search Condition.

If a ConstraintName is not specified, Cloudscape generates a unique constraint name (for either column or table constraints). Constraint names are not generated for NOT NULL constraints.

NOTE: Cloudscape recommends that you name all constraints.

Primary Key and Unique Constraints

A primary key defines the set of columns that uniquely identifies rows in a table.

When you create a primary key, none of the columns can have NULL constraints; that is, they must not permit NULL values. When you add columns without explicit NOT NULL or NULL constraints to a primary key, they gain implicit NOT NULL constraints.

A unique constraint enforces the uniqueness of data. Unique constraints allow NULLs.

A table can have at most one PRIMARY KEY constraint, but can have multiple UNIQUE constraints.

Primary key and unique constraints are permitted on Java data types only if they are orderable (see Orderable Java Data Types).

Foreign Key Constraints

Foreign keys provide a way to enforce the referential integrity of a database. A foreign key is a column or group of columns within a table that references a key in some other table (or sometimes, though rarely, the same table). The foreign key must always include the columns of which the types exactly match those in the referenced primary key or unique constraint.

For a table-level foreign key constraint in which you specify the columns in the table that make up the constraint, you cannot use the same column more than once.

If there is a column list in the ReferencesSpecification (a list of columns in the referenced table), it must correspond either to a unique constraint or to a primary key constraint in the referenced table. The ReferencesSpecification can omit the column list for the referenced table if that table has a declared primary key.

If there is no column list in the ReferencesSpecification and the referenced table has no primary key, a statement exception is thrown. (This means that if the referenced table has only unique keys, you must include a column list in the ReferencesSpecification.)

Foreign key constraints are permitted on Java data types only if they are orderable (see Orderable Java Data Types).

A foreign key constraint is satisfied if there is a matching value in the referenced unique or primary key column, or if the value is NULL. If the foreign key consists of multiple columns, the foreign key value is considered NULL if any of its columns contains a NULL.

NOTE: It is possible for a foreign key consisting of multiple columns to allow one of the columns to contain a value for which there is no matching value in the referenced columns, per the SQL-92 standard. To avoid this situation, create NOT NULL constraints on all of the foreign key's columns.

Foreign Key Constraints and DML

When you insert into or update a table with an enabled foreign key constraint, Cloudscape checks that the row does not violate the foreign key constraint by looking up the corresponding referenced key in the referenced table. If the constraint is not satisfied, Cloudscape rejects the insert or update with a statement exception.

When you update or delete a row in a table with a referenced key (a primary or unique constraint referenced by a foreign key), Cloudscape checks every foreign key constraint that references the key to make sure that the removal or modification of the row does not cause a constraint violation. If removal or modification of the row would cause a constraint violation, the update or delete is not permitted and Cloudscape throws a statement exception.

You can temporarily disable foreign key and check constraints. See SET CONSTRAINTS statement.

Cloudscape performs constraint checks at the time the statement is executed, not when the transaction commits.

Backing Indexes

UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints generate indexes that enforce or "back" the constraint (and are sometimes called backing indexes). UNIQUE and PRIMARY KEY constraints generate unique indexes. FOREIGN KEY constraints generate non-unique indexes. Therefore, if a column or set of columns has a UNIQUE, PRIMARY KEY, or FOREIGN KEY constraint on it, you do not need to create an index on those columns for performance. Cloudscape has already created it for you. See Indexes and Constraints.

You can specify storage properties for the indexes backing the PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints with a PROPERTIES clause. A PROPERTIES clause allows you to specify page size for the backing index on a constraint and to override the values for those properties set on a database-wide basis. For backing indexes, you can specify only page size. For more information, see PROPERTIES clause.

These indexes are available to the optimizer for query optimization (see CREATE INDEX statement) and have system-generated names.

You cannot drop backing indexes with a DROP INDEX statement; you must drop the constraint or the table.

Check Constraints

A check constraint can be used to specify a wide range of rules for the contents of a table. A search condition (which is a boolean expression) is specified for a check constraint. This search condition must be satisfied for all rows in the table. The search condition is applied to each row that is modified on an INSERT or UPDATE at the time of the row modification. The entire statement is aborted if any check constraint is violated.

NOTE: According to the SQL-92 standard, if the boolean expression returns an UNKNOWN, it satisfies the search condition. This means that if you have a check constraint such as "c = 3", and a row is inserted in which the value in column c is null, c=3 evaluates to UNKNOWN for that column. This means that the insert is allowed. If this situation is not acceptable to you, use the IS expression (see "IS" in Table 1-10).

Requirements for Search Condition

The search condition can reference any column in the current row of the table to which the constraint is being applied. The search condition must always return the same value if applied to the same values. Thus, it cannot contain any of the following:

  • Dynamic parameters (?)
  • Date/Time Functions (CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP)
  • RunTimeStatistics()
  • Subqueries
  • User-defined aggregates

About Java Methods and Fields in CHECK Constraints

Use Java methods and fields with care. They should return the same results given the same circumstances; they should not depend on anything except the arguments. For example, methods could potentially return random results. Such methods are not appropriate. It is also possible to use methods that execute SQL. Some SQL is appropriate, but some is not, because it might depend on something other than the arguments to the method (for example, data in another table). Cloudscape does not check the appropriateness of Java methods and fields in check constraints. It is up to the database designer to ensure that the use of the method or field is appropriate.

Here is an extended example showing you how seemingly harmless SQL could get you into trouble.

Subqueries are not allowed in check constraints. Suppose that is the functionality that you need. You want to check that t1.c1 is in t2.c1, and you cannot do it with a foreign key because there is no primary key or unique constraint on t2.c1. You could add a method call that contains SQL to the check constraint.

If subqueries were allowed, here's how you would like to define the table:

-- CREATE TABLE t1(c1 INT CHECK (c1 IN SELECT c1 FROM t2),
    -- c2 INT)

This statement is not allowed. Instead, you could do the following:

-- CREATE TABLE t1(c1 INT CHECK(booleanMethod(c1)),
    -- c2 INT)

where booleanMethod essentially performs the subquery.

The problem with this approach is that the contents of t2 can change independently of the contents of t1. Consider the following scenario:

INSERT INTO t2 (c1) VALUES 1

-- succeeds since 1 is in t2
INSERT INTO t1 VALUES (1, 1)

-- nothing to stop the delete
DELETE FROM t2 WHERE c1 = 1

-- fails since check constraint will fail
-- even though you are not changing the contents of the row
UPDATE t1 SET c2 = c2 where c1 = c2

Disabling Constraints

You can temporarily disable foreign key and check constraints. For example, you might want to disable such constraints before loading a lot of data. See SET CONSTRAINTS statement.

CREATE TABLE Examples

CREATE TABLE Cities
-- column-level primary key, named constraint;
    (city_id INT CONSTRAINT CITIES_PK PRIMARY KEY,
    city SERIALIZE(JBMSTours.serializabletypes.City),
    country_ISO_code CHAR(2))

CREATE TABLE HotelAvailability (hotel_id INT ,
    booking_date DATE,
    rooms_taken INT,
    -- the table-level primary key definition allows you to
    -- include two columns in the primary key definition
    PRIMARY KEY (hotel_id, booking_date))

-- Use a column-level constraint for an arithmetic check;
-- use a column-level constraint to make sure that the
-- tour level is one of the correct values (by referencing
--
static final fields), and use a table-level constraint
-- to make sure that a group's running total does not
-- exceed its budget
-- in this example, Tour is a class alias
-- for JBMSTours.serializabletypes.Tour
CREATE TABLE Groups(
    group_id INT CONSTRAINT Groups_PK PRIMARY KEY,
    number_kids INT,
    number_adults INT,
    number_people INT CONSTRAINT people_check
        CHECK(number_people=number_kids+number_adults),
    main_person INT,
    number_rooms INT,
    city_id INT,
    address VARCHAR(100),
    phone VARCHAR(15),
    tour_level SMALLINT CONSTRAINT level_check2
        CHECK (tour_level IN (
        Tour->ECONOMYTOURLEVEL,
        Tour->STANDARDTOURLEVEL,
        Tour->FIRSTCLASSTOURLEVEL)),
    budget DECIMAL(11,2),
    running_total DECIMAL(11,2)
    CONSTRAINT spending_check CHECK (running_total <= budget))

-- use a check constraint to allow only appropriate
-- abbreviations for the meals
CREATE TABLE Flights
    (flight_id CHAR(6),
    segment_number INT,
    orig_airport CHAR(3),
    depart_time TIME,
    dest_airport CHAR(3),
    arrive_time TIME,
    meal CHAR(1)
    CONSTRAINT MEAL_CONSTRAINT CHECK
      (meal IN ('B', 'L', 'D', 'S')))

-- create a table whose city_id column references the
-- primary key in the Cities table
-- using a column-level foreign key constraint
CREATE TABLE hotels
    (hotel_id INT CONSTRAINT hotels_PK PRIMARY KEY,
    hotel_name VARCHAR(40) NOT NULL,
    city_id INT CONSTRAINT city_foreign_key REFERENCES Cities)

-- create a table with a table-level primary key constraint
-- and a table-level foreign key constraint
CREATE TABLE FlightAvailability
    (flight_id CHAR(6),
    segment_number INT,
    flight_date DATE,
    economy_seats_taken INT,
    business_seats_taken INT,
    firstclass_seats_taken INT,
    CONSTRAINT FLIGHTAVAILABILITY_PK PRIMARY KEY (flight_id,
        segment_number, flight_date),
    CONSTRAINT flights_foreign_key2
        FOREIGN KEY(flight_id, segment_number)
        REFERENCES Flights(flight_id, segment_number))

-- add a unique constraint to a column
-- that stores an orderable Java data type
ALTER TABLE CITIES
ADD CONSTRAINT c_uc UNIQUE(city)

Dependency System

INSERT and UPDATE statements depend on all constraints on the target table. DELETEs depend on unique, primary key, and foreign key constraints. These statements are invalidated if a constraint is added to or dropped from the target table.

CREATE Statements