Unnamed Table Constraints

Go Up to Method One: In-place Migration

Read the first two paragraphs under Column Defaults and Column Constraints to understand why you do not always need to change constraints with double quotes to single-quoted form, and why you might want to change them.

To bring unnamed table constraints that contain double quotes into compliance with the dialect 3 standard, follow these steps:

1. Back up the database.

2. Examine the metadata to detect each occurrence of an unnamed table constraint that uses double quotes.

3. For each occurrence, use SHOW TABLE to see the name that InterBase has assigned to the constraint.

4. Use ALTER TABLE to drop the old constraint, using the name given in the SHOW TABLE output and add a new constraint. For ease in future handling, give the constraint a name.

If SHOW TABLE shows that InterBase stores the unnamed constraint as “INTEG_2”, then issue the following statement to change the constraint:

ALTER TABLE foo
 DROP CONSTRAINT INTEG_2,
 ADD CONSTRAINT new_name
 CHECK (col_name IN ('val1', 'val2', 'val3'));