Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
ALTER TABLE statementUse the ALTER TABLE statement to modify the schema of an existing table.
MODIFY Clause Use the MODIFY clause to change the data type, length, or default value of a column, to add or remove the security label of a column, to allow or disallow NULL values in a column, or to reset the serial counter of a SERIAL, SERIAL8, or BIGSERIAL column.
Adding a Constraint That Existing Rows Violate

Adding a Constraint That Existing Rows Violate

If you use the MODIFY clause to add a constraint in the enabled mode and receive an error message because existing rows would violate the constraint, take the following steps to add the constraint successfully:

Add the constraint in the disabled mode.
Issue the ALTER TABLE statement again, but this time specify the DISABLED keyword in the MODIFY clause.
Start a violations and diagnostics table for the target table with the START VIOLATIONS TABLE statement.
Issue the SET CONSTRAINTS statement to switch the database object mode of the constraint to the enabled mode.
When you issue this statement, existing rows in the target table that violate the constraint are duplicated in the violations table; however, you receive an integrity-violation error message, and the constraint remains disabled.
Issue a SELECT statement on the violations table to retrieve the nonconforming rows that are duplicated from the target table.
You might need to join the violations and diagnostics tables to get all the necessary information.
Take corrective action on the rows in the target table that violate the constraint.
After you fix all the nonconforming rows in the target table, issue the SET statement again to enable the constraint that was disabled.
Now the constraint is enabled, and no integrity-violation error message is returned because all rows in the target table now satisfy the new constraint.