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;

 

           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;

 

           id  name                  phone

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

            1  'BBB'                 '000-0000'

            2  'BBB'                 '000-0000'

            3  'BBB'                 '000-0000'

            4  'BBB'                 '000-0000'

            5  'BBB'                 '000-0000'

 

--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;

 

           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'

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.

You can change the initial value of AUTO_INCREMENT by using the ALTER TABLE statement. For more information, see AUTO_INCREMENT Statement of ALTER TABLE.

Syntax

CREATE TABLE table_name (id int AUTO_INCREMENT[(seed, increment)]) |

CREATE TABLE table_name (id int AUTO_INCREMENT) AUTO_INCREMENT = seed;

• 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.

When you use the CREATE TABLE table_name (id int AUTO_INCREMENT) AUTO_INCREMENT = seed; statement, the constraints are as follows:

• You should define only one column with the AUTO_INCREMENT attribute.
• Don't use (seed, increment) and AUTO_INCREMENT = seed together.

Example

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;

 

           id  name

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

            1  'AAA'

            2  'BBB'

            3  'CCC'

            4  'DDD'

            5  'EEE'

 

CREATE TABLE tbl (id int AUTO_INCREMENT, val string) AUTO_INCREMENT = 3;

INSERT INTO tbl VALUES (NULL,'cubrid');

 

SELECT * FROM tbl;

           id  val

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

            3  'cubrid'

 

CREATE TABLE t (id int AUTO_INCREMENT, id2 int AUTO_INCREMENT) AUTO_INCREMENT = 5; ERROR: To avoid ambiguity, the AUTO_INCREMENT table option requires the table to  have exactly one AUTO_INCREMENT column and no seed/increment specification.

CREATE TABLE t (i int AUTO_INCREMENT(100, 2)) AUTO_INCREMENT = 3; ERROR: To avoid ambiguity, the AUTO_INCREMENT table option requires the table to  have exactly one AUTO_INCREMENT column and no seed/increment specification.

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));