Defining Integrity Constraints

The create table statement helps control a database’s integrity through a series of integrity constraints as defined by the SQL standards.

  • These integrity constraint clauses restrict the data that users can insert into a table. You can also use defaults, rules, indexes, and triggers to enforce database integrity.

    Integrity constraints offer the advantages of defining integrity controls in one step during the table creation process and of simplifying the creation of those integrity controls. However, integrity constraints are more limited in scope and less comprehensive than defaults, rules, indexes, and triggers.

  • You must declare constraints that operate on more than one column as table-level constraints; declare constraints that operate on only one column as column-level constraints. Although the difference is rarely noticed by users, column-level constraints are checked only if a value in the column is being modified, while the table-level constraints are checked if there is any modification to a row, regardless of whether or not it changes the column in question.

    Place column-level constraints after the column name and datatype, before the delimiting comma. Enter table-level constraints as separate comma-delimited clauses. The SAP ASE server treats table-level and column-level constraints the same way; neither way is more efficient than the other.

  • You can create the following types of constraints at the table level or the column level:
    • A unique constraint does not allow two rows in a table to have the same values in the specified columns. In addition, a primary key constraint disallows null values in the column.

    • A referential integrity (references) constraint requires that the data being inserted or updated in specific columns has matching data in the specified table and columns.

    • A check constraint limits the values of the data inserted into the columns.

    You can also enforce data integrity by restricting the use of null values in a column (using the null or not null keywords) and by providing default values for columns (using the default clause).

  • You can use sp_primarykey, sp_foreignkey, and sp_commonkey to save information in system tables, which can help clarify the relationships between tables in a database. These system procedures do not enforce key relationships or duplicate the functions of the primary key and foreign key keywords in a create table statement. For a report on keys that have been defined, use sp_helpkey. For a report on frequently used joins, execute sp_helpjoins.

  • Transact-SQL provides several mechanisms for integrity enforcement. In addition to the constraints you can declare as part of create table, you can create rules, defaults, indexes, and triggers. This table summarizes the integrity constraints and describes the other methods of integrity enforcement:

    In create table

    Other Methods

    unique constraint

    create unique index (on a column that allows null values)

    primary key constraint

    create unique index (on a column that does not allow null values)

    references constraint

    create trigger

    check constraint (table level)

    create trigger

    check constraint (column level)

    create trigger or create rule and sp_bindrule

    default clause

    create default and sp_bindefault

    The method you choose depends on your requirements. For example, triggers provide more complex handling of referential integrity (such as referencing other columns or objects) than those declared in create table. Also, the constraints defined in a create table statement are specific for that table; unlike rules and defaults, you cannot bind them to other tables, and you can only drop or change them using alter table. Constraints cannot contain subqueries or aggregate functions, even on the same table.

  • create table can include many constraints, with these limitations:
    • The number of unique constraints is limited by the number of indexes that a table can have.

    • A table can have only one primary key constraint.

    • You can include only one default clause per column in a table, but you can define different constraints on the same column.

    For example:
    create table discount_titles
     (title_id   varchar (6)   default "PS7777" not null
            unique clustered
            references titles (title_id)
            check (title_id like "PS%"),
                new_price   money)

    Column title_id of the new table discount_titles is defined with each integrity constraint.

  • You can create error messages and bind them to referential integrity and check constraints. Create messages with sp_addmessage and bind them to the constraints with sp_bindmsg.

  • The SAP ASE server evaluates check constraints before enforcing the referential constraints, and evaluates triggers after enforcing all the integrity constraints. If any constraint fails, the SAP ASE server cancels the data modification statement; any associated triggers do not execute. However, a constraint violation does not roll back the current transaction.

  • In a referenced table, you cannot update column values or delete rows that match values in a referencing table. Update or delete from the referencing table first, then try updating or deleting from the referenced table.

  • You must drop the referencing table before you drop the referenced table; otherwise, a constraint violation occurs.

  • For information about constraints defined for a table, use sp_helpconstraint.