Column Definition

A column is a set of data values of a particular simple type, one for each row of the table.

<column_definition>::=

column_name column_type [[  <default_or_shared> ] | [ <column_constraint> ]]...

 

<default_or_shared>::=

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

AUTO_INCREMENT [(seed, increment)]

 

<column_constraint>::=

NOT NULL | UNIQUE | PRIMARY KEY | FOREIGN KEY  <referential definition>

• Column Name
• Setting the Column Initial Value (SHARED, DEFAULT)
• Auto Increment

Column Name

Description

How to create a column name, see Identifier.

You can alter created column name by using RENAME COLUMN clause of the ALTER TABLE.

Example

The following is an example of creating the manager2 table that has the following two columns: full_name and age.

CREATE TABLE manager2 (full_name VARCHAR(40), age INT );

Caution

• The first character of a column name must be an alphabet. The maximum length is 255 characters.
• The column name must be unique in the table.

Setting the Column Initial Value (SHARED, DEFAULT)

Description

SHARED and DEFAULT are attributes related to the initial value of the column. You can change the value of SHARED and DEFAULT in the ALTER TABLE statement.

• SHARED : Column values are identical in all rows. If a value different from the initial value is INSERTed, the column value is updated to a new one in every row.
• DEFAULT : The initial value set when the DEFAULT attribute was defined is saved even if the column value is not specified when a new row is inserted. Note that if you set SYS_TIMESTAMP as a DEFAULT value when creating a table, the TIMESTAMP value at the point of CREATE TABLE, not the point at which the data is INSERTed, is specified by default. Therefore, you must specify the SYS_TIMESTAMP value for the VALUES of the INSERT statement when entering data.

Example

CREATE TABLE colval_tbl

( id INT, name VARCHAR SHARED 'AAA', phone VARCHAR DEFAULT '000-0000');

INSERT INTO colval_tbl(id) VALUES (1),(2);

SELECT * FROM colval_tbl;

;xr

 

=== <Result of SELECT Command in Line 3> ===

 

           id  name                  phone

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

            1  'AAA'                 '000-0000'

            2  'AAA'                 '000-0000'

 

--updating column values on every row

INSERT INTO colval_tbl(id, name) VALUES (3,'BBB');

INSERT INTO colval_tbl(id) VALUES (4),(5);

SELECT * FROM colval_tbl;

;xr

 

=== <Result of SELECT Command in Line 3> ===

 

           id  name                  phone

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

            1  'BBB'                 '000-0000'

            2  'BBB'                 '000-0000'

            3  'BBB'                 '000-0000'

            4  'BBB'                 '000-0000'

            5  'BBB'                 '000-0000'

 

5 rows selected.

 

--changing DEFAULT value in the ALTER TABLE statement

ALTER TABLE colval_tbl CHANGE phone DEFAULT '111-1111'

INSERT INTO colval_tbl(id) VALUES (6);

SELECT * FROM colval_tbl;

;xr

 

=== <Result of SELECT Command in Line 1> ===

 

           id  name                  phone

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

            1  'BBB'                 '000-0000'

            2  'BBB'                 '000-0000'

            3  'BBB'                 '000-0000'

            4  'BBB'                 '000-0000'

            5  'BBB'                 '000-0000'

            6  'BBB'                 '111-1111'

 

 

6 rows selected.

AUTO INCREMENT

Description

You can define the AUTO_INCREMENT attribute for the column to automatically give serial numbers to column values. This can be defined only for SMALLINT, INTEGER, BIGINT(p,0), and NUMERIC(p,0) domains.

DEFAULT, SHARED and AUTO_INCREMENT cannot be defined for the same column. Make sure the value entered directly by the user and the value entered by the auto increment attribute do not conflict with each other.

Syntax

AUTO_INCREMENT [(seed, increment)]

• seed : The initial value from which the number starts. Only positive integers are allowed. The default is 1.
• increment : The increment value of each row. Only positive integers are allowed. The default value 1.

Example

--AUTO_INCREMENT works only when no value is inserted on compat_mode=cubrid

CREATE TABLE auto_tbl(id INT AUTO_INCREMENT, name VARCHAR);

INSERT INTO auto_tbl VALUES(NULL, 'AAA'),(NULL, 'BBB'),(NULL, 'CCC');

INSERT INTO auto_tbl(name) VALUES ('DDD'),('EEE');

SELECT * FROM auto_tbl;

;xr

 

=== <Result of SELECT Command in Line 4> ===

 

           id  name

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

            1  'AAA'

            2  'BBB'

            3  'CCC'

            4  'DDD'

            5  'EEE'

 

5 rows selected.

Caution

• Even if a column has auto increment, the UNIQUE constraint is not satisfied.
• If NULL is specified in the column where auto increment is defined, the value of auto increment is stored.
• The initial value and the final value obtained by auto increment cannot exceed the minimum and maximum values allowed in the given domain.
• Because auto increment has no cycle, an error occurs when the maximum value of the type exceeds, and no rollback is executed. Therefore, you must delete and recreate the column in such cases.
• For example, if a table is created as below, the maximum value of A is 32767. Because an error occurs if the value exceeds 32767, you must make sure that the maximum value of the column A does not exceed the maximum value of the type when creating the initial table.
• create table tb1(A smallint auto_increment, B char(5));