ALTER TABLE Statement

Home » Add Constraint Unique Key Oracle » ALTER TABLE Statement

Maybe your like

In the column-alteration, SET INCREMENT BY integer-constant, specifies the interval between consecutive values of the identity column. The next value to be generated for the identity column will be determined from the last assigned value with the increment applied. The column must already be defined with the IDENTITY attribute.

RESTART WITH integer-constant specifies the next value to be generated for the identity column. RESTART WITH is useful for a table that has an identity column that was defined as GENERATED BY DEFAULT and that has a unique key defined on that identity column. Because GENERATED BY DEFAULT allows both manual inserts and system generated values, it is possible that manually inserted values can conflict with system generated values. To work around such conflicts, use the RESTART WITH syntax to specify the next value that will be generated for the identity column. Consider the following example, which involves a combination of automatically generated data and manually inserted data:CREATE TABLE tauto(i INT GENERATED BY DEFAULT AS IDENTITY, k INT) CREATE UNIQUE INDEX tautoInd ON tauto(i) INSERT INTO tauto(k) values 1,2 The system will automatically generate values for the identity column. But now you need to manually insert some data into the identity column:INSERT INTO tauto VALUES (3,3) INSERT INTO tauto VALUES (4,4) INSERT INTO tauto VALUES (5,5) The identity column has used values 1 through 5 at this point. If you now want the system to generate a value, the system will generate a 3, which will result in a unique key exception because the value 3 has already been manually inserted. To compensate for the manual inserts, issue an ALTER TABLE statement for the identity column with RESTART WITH 6:ALTER TABLE tauto ALTER COLUMN i RESTART WITH 6

ALTER TABLE does not affect any view that references the table being altered. This includes views that have an "*" in their SELECT list. You must drop and re-create those views if you wish them to return the new columns.

Derby raises an error if you try to change the DataType of a generated column to a type which is not assignable from the type of the generation-clause. Derby also raises an error if you try to add a DEFAULT clause to a generated column.

Tag » Add Constraint Unique Key Oracle