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
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 example shows how to create the manager2 table that has the following two columns: full_name and age.

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

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

te 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 details, 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;

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

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