Assertions and Deferability of Constraints

January 10, 2019 | By | Add a Comment

Constraints are rules that you establish to restrict the values that can be placed in your columns. In basic SQL, constraints are contained in the definitions of base tables and are of two kinds: column constraints and table constraints. The former is part of a column definition and check their rules whenever a statement attempts to insert or change a value in that column. The latter is part of the table definition and therefore can accommodate rules that involve checking multiple columns of the table. In both kinds, the constraints can be either of certain predefined kinds, not null, Unique, Check constraints. If the expression is false the constraint is not satisfied and the statement is rejected. In check constraints, if a condition is not satisfied then a statement is rejected.

An extension of this concept is assertion – constraints that exist in the schema as independent objects, not in a table. This means that they can refer to multiple tables in their predicates. They can also be used to ensure that a table is never empty, which cannot be done within the table itself. Assertions allow you to design general principles that your data must meet, for example, to design validity checks. Assertions can be created and dropped. You can also put constraints in domain.

SQL constraints are named, either explicitly or automatically by DBMS engine. This enables them to be deferred of dropped from tables. In a short we can say that, using naming conversion we can manage constraint easily. You can drop constraints using ALTER TABLE statement.

You have the option of deferring the checking constraints until the end of the transaction or for long time. This gets rather sophisticated. Basically, a constraint can be checked at any of the following time.

After every statement that affects the table to which it refers.

At the end of every transaction that contains one of more statements that affect the table to which it refers.

At any time between or above situation when the user or application decides it should be checked and therefore force the issue.

When you define a constraint, you specify whether it must be checked immediately after each SQL statement or may be deferred until the end of the transaction. If you choose the latter, you can also specify whether it will default to being checked immediately or being deferred. Then, during your transaction, you can change this default by setting a constraint mode for all constraints at once or for specified ones. If you set your constraint mode for all.

Be Sociable, Share!

Tags: ,

Category: sql, sql tutorial

Leave a Reply

You must be logged in to post a comment.