Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 



CHANGE/MODIFY Clauses

Description

The CHANGE clause changes column names or changes the types and the attributes. If the existing column name and a new column name are the same, only the type and the attribute will be changed.

The MODIFY clause can modify the types and the attributes of columns but cannot change the names.

If you set the type and the attribute to apply to a new column with the CHANGE clause or the MODIFY clause, the attribute that is currently defined will not be passed to the attribute of the new column.

When you change data types using the CHANGE clause or the MODIFY clause, the data can be modified. For example, if you shorten the length of a column, the character string may be truncated.

Note ALTER TABLE <table_name> CHANGE <column_name> DEFAULT <default_value> syntax supported in CUBRID 2008 R3.1 or earlier version is no longer supported.

Syntax

ALTER TABLE tbl_name table_options;

 

table_options :

     table_option[, table_option]

 

table_option :

    CHANGE [COLUMN | CLASS ATTRIBUTE ] old_col_name new_col_name column_definition

             [FIRST | AFTER col_name]

  | MODIFY [COLUMN | CLASS ATTRIBUTE] col_name column_definition

             [FIRST | AFTER col_name]

  • tbl_name : Specifies the name of the table including the column to change.
  • old_col_name : Specifies the existing column name.
  • new_col_name : Specifies the column name to change
  • column_definition : Specifies the type and the attribute of the column to change.
  • col_name : Specifies the column name to which the type and the attribute of the column to apply changes.
Example 1

CREATE TABLE t1 (a INTEGER);

ALTER TABLE t1 CHANGE a b INTEGER;

 

-- changing a column's constraint

ALTER TABLE t1 CHANGE a a INTEGER NOT NULL;

ALTER TABLE t1 MODIFY a INTEGER NOT NULL;

 

-- changing acolumn's type - "DEFAULT 1" constraint is removed.

CREATE TABLE t1 (col1 INT DEFAULT 1);

ALTER TABLE t1 MODIFY col1 BIGINT;

 

-- changing acolumn's type - "DEFAULT 1" constraint is kept.

CREATE TABLE t1 (col1 INT DEFAULT 1);

ALTER TABLE t1 MODIFY col1 BIGINT DEFAULT 1;

Example 2

-- changing the name and position of a column  

CREATE TABLE t1(i1 int,i2 int);  

INSERT INTO t1 VALUE (1,11),(2,22),(3,33);  

SELECT * FROM t1 ORDER BY 1;

            i1           i2

==========================

             1           11

             2           22

             3           33

 

ALTER TABLE t1 CHANGE i2 i0 INTEGER FIRST;  

SELECT * FROM t1 ORDER BY 1;

            i0           i1

==========================

            11            1

            22            2

            33            3

Example 3

-- adding NOT NULL constraint (strict)

-- alter_table_change_type_strict=yes

 

CREATE TABLE t1(i int);

INSERT INTO t1 values (11),(NULL),(22);

 

ALTER TABLE t1 change i i1 integer not null;

 

In the command from line 1,

 

ERROR: Cannot add NOT NULL constraint for attribute "i1": there are existing NULL values for this attribute.

Example 4

-- adding NOT NULL constraint

-- alter_table_change_type_strict=no

 

CREATE TABLE t1(i int);

INSERT INTO t1 VALUES (11),(NULL),(22);

 

ALTER TABLE t1 CHANGE i i1 INTEGER NOT NULL;

 

SELECT * FROM t1;

 

           i1

=============

           22

            0

           11

Example 5

-- change the column's data type (no errors)

 

CREATE TABLE t1 (i1 int);

INSERT INTO t1 VALUES (1),(-2147483648),(2147483647);

 

ALTER TABLE t1 CHANGE i1 s1 CHAR(11);

 

SELECT * FROM t1;

 

  s1

======================

  '2147483647 '

  '-2147483648'

  '1          '

Example 6

-- change the column's data type (errors), strict mode

-- alter_table_change_type_strict=yes

 

CREATE TABLE t1 (i1 int);

INSERT INTO t1 VALUES (1),(-2147483648),(2147483647);

 

ALTER TABLE t1 CHANGE i1 s1 CHAR(4);

 

In the command from line 1,

 

ERROR: ALTER TABLE .. CHANGE : changing to new domain : cast failed, current configuration doesn't allow truncation or overflow.

 

-- change the column's data type (errors)

-- alter_table_change_type_strict=no

 

CREATE TABLE t1 (i1 INT);

INSERT INTO t1 VALUES (1),(-2147483648),(2147483647);

 

ALTER TABLE t1 CHANGE i1 s1 CHAR(4);

 

SELECT * FROM t1;

 

  s1

======================

  '    '

  '    '

  '1   '

 

-- hard default values have been placed instead of signaling overflow

Syntax Operation According to Column Attributes
  • Type Change : If the value of the system parameter alter_table_change_type_strict is set to no, then changing values to other types is allowed, but if it is set to yes then changing is not allowed. The default value of the parameter is no. You can change values to all types allowed by the CAST operator. Changing object types is allowed only by the upper classes (tables) of the objects.
  • NOT NULL
    • If the NOT NULL constraint is not specified, it will be removed from a new table even though it is present in the existing table.
    • If the NOT NULL constraint is specified in the column to change, the result varies depending on the configuration of the system parameter, alter_table_change_type_strict.
      • If alter_table_change_type_strict is set to yes, the column values will be checked. If NULL exists, an error will occur, and the change will not be executed.
      • If the alter_table_change_type_strict is set to no, every existing NULL value will be changed to a hard default value of the type to change.
  • DEFAULT : If the DEFAULT attribute is not specified in the column to change, it will be removed from a new table even though it is present in the existing table.
  • AUTO_INCREMENT : If the AUTO_INCREMENT attribute is not specified in the column to change, it will be removed from a new table even though it is present in the existing table.
  • FOREIGN KEY : You cannot change the column with the foreign key constraint that is referred to or refers to.
  • Single Column PRIMARY KEY
    • If the PRIMARY KEY constraint is specified in the column to change, a PRIMARY KEY is re-created only in which a PRIMARY KEY constraint exists in the existing column and the type is upgraded.
    • If the PRIMARY KEY constraint is specified in the column to change but doesn't exist in the existing column, a PRIMARY KEY will be created.
    • If a PRIMARY KEY constraint exists but is not specified in the column to change, the PRIMARY KEY will be maintained.
  • Multicolumn PRIMARY KEY : If the PRIMARY KEY constraint is specified and the type is upgraded, a PRIMARY KEY will be re-created.
  • Single Column UNIQUE KEY 
    • If the type is upgraded, a UNIQUE KEY will be re-created.
    • If a UNIQUE KEY exists in the existing column and it is not specified in the column to change, it will be maintained.
    • If a UNIQUE KEY exists in the existing column to change, it will be created.
  • Multicolumn UNIQUE KEY : If the column type is changed, an index will be re-created.
  • Column with a Non-unique Index : If the column type is changed, an index will be re-created.
  • Partition Column: If a table is partitioned by a column, the column cannot be changed. Partitions cannot be added.
  • Column with a Class Hierarchy : You can only change the tables that do not have a lower class. You cannot change the lower class that inherits from an upper class. You cannot change the inherited attributes.
  • Trigger and View : You must redefine triggers and views directly because they are not changed according to the definition of the column to change.
  • Column Sequence : You can change the sequence of columns.
  • Name Change : You can change names as long as they do not conflict.
Syntax Operation According to the System Parameter, alter_table_change_type_strict

The alter_table_change_type_strict parameter determines whether the value conversion is allowed according to the type change. If the value is no, it can be changed when you change a column type or add a NOT NULL constraint. The default value is no.

When the value of the parameter, alter_table_change_type_strict is no, it will operate depending on the conditions as follows:

  • Overflow Occurred while Converting Numbers or Character Strings to Numbers: The minimum value or the maximum value is specified according to the result type conditions, and the warning message will be recorded in the log for the record where overflow has occurred.
    • If input values are numbers, their signs will be written to the log.
    • If input values are character strings, the signs of the values converted to DOUBLE types will be written in the log.
  • Character Strings to Convert to Shorter Ones: The record will be updated to the hard default value of the type that is defined and the warning message will be recorded in a log.
  • Conversion Failure Due to Other Reasons : The record will be updated to the hard default value of the type that is defined and the warning message will be recorded in a log.

If the value of the alter_table_change_type_strict parameter is yes, an error message will be displayed and the changes will be rolled back.

The ALTER CHANGE statement checks the possibility of type conversion before updating a record but the type conversion of specific values may fail. For example, if the value format is not correct when you convert VARCHAR to DATE, the conversion may fail. In this case, the hard default value of the DATE type will be assigned.

The hard default value is a value that will be used when you add columns with the ALTER TABLE … ADD  COLUMN statement, add or change by converting types with the ALTER TABLE … CHANGE/MODIFY statement. The operation will vary depending on the system parameter, add_column_update_hard_default in the ADD COLUMN statement.

Hard Default Value by Type

Type

Existence of Hard Default Value

Hard Default Value

INTEGER

Yes

0

FLOAT

Yes

0

DOUBLE

Yes

0

SMALLINT

Yes

0

DATE

Yes

date'01/01/0001'

TIME

Yes

time'00:00'

DATETIME

Yes

datetime'01/01/0001 00:00'

TIMESTAMP

Yes

timestamp'00:00:01 AM 01/01/1970' (GMT)

MONETARY

Yes

0

NUMERIC

Yes

0

CHAR

Yes

''

VARCHAR

Yes

''

NCHAR

Yes

N''

VARNCHAR

Yes

N''

SET

Yes

{}

MULTISET

Yes

{}

SEQUENCE

Yes

{}

BIGINT

Yes

0

BIT

Yes

 

VARBIT

No

 

OBJECT

No

 

BLOB

No

 

CLOB

No

 

ELO

No