Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

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

ADD COLUMN Clause

Description

You can add a new column by using the ADD COLUMN clause. You can specify the location of the column to be added by using the FIRST or AFTER keyword.

If the newly added column has the NOT NULL constraint but no DEFAULT constraint, it will have the hard default when the database server configuration parameter, add_column_update_hard_default is set to yes. However, when the parameter is set to no, the column will have NULL even with the NOT NULL constraint.

If the newly added column has the PRIMARY KEY or UNIQUE constraints, an error will be returned when the database server configuration parameter add_column_update_hard_default is set to yes. When the parameter is set to no, all data will have NULL. The default value of add_column_update_hard_default is no.

For add_column_update_hard_default and the hard default, see CHANGE Clause.

Syntax

ALTER [ TABLE | CLASS | VCLASS | VIEW ] table_name

ADD [ COLUMN | ATTRIBUTE ] [(]<column_definition>[)] [ FIRST | AFTER old_column_name ]

 

column_definition::=

column_name column_type

    { [ NOT NULL | NULL ] |

      [ { SHARED <value_specification> | DEFAULT <value_specification> }

          | AUTO_INCREMENT [(seed, increment)] ] |

      [ UNIQUE [ KEY ] |

          [ PRIMARY KEY | FOREIGN KEY REFERENCES

              [ referenced_table_name ]( column_name_comma_list )

              [ <referential_triggered_action> ... ]

          ]

      ] } ...

 

<referential_triggered_action> ::=

{ ON UPDATE <referential_action> } |

{ ON DELETE <referential_action> } |

{ ON CACHE OBJECT cache_object_column_name }

 

<referential_action> ::=

CASCADE | RESTRICT | NO ACTION | SET NULL

  • table_name: Specifies the name of a table that has a column to be added.
  • column_definition: Specifies the name, data type, and constraints of a column to be added.
  • AFTER oid_column_name: Specifies the name of an existing column before the column to be added.
Example

CREATE TABLE a_tbl;

ALTER TABLE a_tbl ADD COLUMN age INT DEFAULT 0 NOT NULL;

INSERT INTO a_tbl(age) VALUES(20),(30),(40);

ALTER TABLE a_tbl ADD COLUMN name VARCHAR FIRST;

ALTER TABLE a_tbl ADD COLUMN id INT NOT NULL AUTO_INCREMENT UNIQUE;

ALTER TABLE a_tbl ADD COLUMN phone VARCHAR(13) DEFAULT '000-0000-0000' AFTER name;

 

SELECT * FROM a_tbl;

 

  name                  phone                         age           id

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

  NULL                  '000-0000-0000'                20         NULL

  NULL                  '000-0000-0000'                30         NULL

  NULL                  '000-0000-0000'                40         NULL

 

--adding multiple columns

ALTER TABLE a_tbl ADD COLUMN (age1 int, age2 int, age3 int);