Dynamic constraint management

SQL Guide : Using SQL for database administration : Referential integrity : Dynamic constraint management

Constraints can be managed dynamically with the ALTER TABLE clause. The sub-clauses that can be used are:

▪ADD CONSTRAINT. This clause adds a named constraint to a table.

▪DROP CONSTRAINT. This clause removes a named constraint from a table.

Note In solidDB^®, when the keyword CONSTRAINT is used, the constraint name is mandatory.

▪CHECK. This constraint allows you to specify rules to your tables or table columns. Each rule is a condition that must not be false for any row in the table on which it is defined. Otherwise the table cannot be updated.

The rules are Boolean expressions. The rule can check, for example, a range of values, equity, or the rule can be a simple comparison. You can use several checks in one statement. The following expressions and operators are available:

Expression	Explanation
<	less than
>	greater than
—	equal to
<=	less than or equal to
>=	greater than or equal to
<>	not equal to
AND	conjunction
ANY	in the list that follows or in the table specified
BETWEEN	between
IN	in the list that follows or in the table specified
MAX	maximum value
MIN	minimum value
NOT	negation
OR	disjunction
XOR	exclusive or

▪UNIQUE. The UNIQUE constraint requires that no two rows in a table contain the same value in a given column or list of columns. You can create a unique constraint at either the table level or the column level. Note that primary keys contain the unique constraint.

▪FOREIGN KEY. The FOREIGN KEY constraint requires that each value in the foreign key column must have a matching value in the referenced table.

Note solidDB^® automatically generates names for unnamed constraints. If you want to view the names, use the command soldd -x hiddennames.

For constraint syntax information and examples, see the CREATE TABLE and ALTER TABLE.