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>
How to create a column name, see Identifier.
You can alter created column name by using RENAME COLUMN clause of the ALTER TABLE.
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 );
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.
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'
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.
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:
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.