CREATE

 

The CREATE statements allows creating of tables, views, indexes and roles (see the chapter GRANT/REVOKE for more details).

 

Syntax of the CREATE statement

Creating a view:

CREATE VIEW name AS select_clause

 

Creating a table:

CREATE TABLE name (
   {attr_name data_type [NOT] [NULL|UNIQUE] [PRIMARY KEY] [CHECK condition]} 
   [, {CONSTRAINT cons_name [PRIMARY KEY|UNIQUE|NOT NULL {attributes}]
      [FOREIGN KEY fk_table REFERENCES ({fk_attrs}) 
         [ON DELETE|UPDATE CASCADE|NO ACTION|SET NULL|SET DEFAULT]]}])

 

Creating an index:

CREATE [UNIQUE] INDEX name ON table_name ({attr_name [ASC|DESC]})

 

Creating a role:

CREATE ROLE name

 

Comments

name A name of table, view, index or role to be created. A view cannot contain more than one SELECT clause and this clause may not contain DISTINCT, GROUP BY and HAVING.
select_clause The SELECT statement defining the view. I.e. which records will be accessible with taken of this view.
attr_name An attribute's name.
data_type An attribute's data type. See below for more details.
[NOT] NULL, UNIQUE, PRIMARY KEY Determines properties of an attribute. Whether it can be null and/or unique value, or primary key.
CHECK condition Determines values, which an attribute can hold. See the chapter WHERE for more details.
CONSTRAINT Gives an option to define any additional constraints. E.g. when a primary key is consisted from more than one attribute.
cons_name A constraint's name
attributes Attributes used in the constraint definition
FOREIGN KEY Gives an option to set up reference integrity restrictions.
fk_table The name of a table where the fk_attrs are located.
fk_attrs Attributes making a foreign key. They have to be same as attributes from the declaration point of view.
ON DELETE|UPDATE Determines an event for CASCADE|NO ACTION.
CASCADE, NO ACTION, SET NULL, SET DEFAULT Determines what to do to keep reference integrity. CASCADE deletes all related records. NO ACTION does nothing. I.e. you have to keep the reference integrity by yourself. SET NULL sets related attributes in all related records to null value. SET DEFAULT sets related attributes in all related records to their default values.
table_name A name of a table on which the index is to be created.

 

Data types

CHAR(n) Character string of fixed string length n.
VARCHAR(n) Variable-length character string with a maximum string length n.
LONG VARCHAR Variable length character data. Maximum length depends on used DBMS.
DECIMAL(p,s) Signed, exact, numeric value with a precision of at least p and scale s. (The maximum precision is driver-defined.) (1 <= p <= 15; s <= p).
NUMERIC(p,s) Signed, exact, numeric value with a precision p and scale s (1 <= p <= 15; s <= p).
SMALLINT Exact numeric value with precision 5 and scale 0 (signed: –32,768 <= n <= 32,767, unsigned: 0<= n <= 65,535).
INTEGER

Exact numeric value with precision 10 and scale 0 (signed: –2[31] <= n <= 2[31] – 1, unsigned: 0 <= n <= 2[32] – 1).

REAL Signed, approximate, numeric value with a binary precision 24 (zero or absolute value 10[–38] to 10[38]).
FLOAT(p) Signed, approximate, numeric value with a binary precision of at least p. (The maximum precision is driver-defined.)
DOUBLEPRECISION Signed, approximate, numeric value with a binary precision 53 (zero or absolute value 10[–308] to 10[308]).
BIT Single bit binary data.
TINYINT Exact numeric value with precision 3 and scale 0 (signed: –128 <= n <= 127, unsigned: 0<= n <= 255).
BIGINT Exact numeric value with precision 19 (if signed) or 20 (if unsigned) and scale 0 (signed: –2[63] <= n <= 2[63] – 1, unsigned: 0 <= n <= 2[64] – 1).
BINARY(n) Binary data of fixed length n.
VARBINARY(n) Variable length binary data of maximum length n. The maximum is set by the user.
LONG VARBINARY Variable length binary data. Maximum length is data source–dependent.
DATE Year, month, and day fields, conforming to the rules of the Gregorian calendar.
TIME(p) Hour, minute, and second fields, with valid values for hours of 00 to 23, valid values for minutes of 00 to 59, and valid values for seconds of 00 to 61. Precision p indicates the seconds precision.
TIMESTAMP(p) Year, month, day, hour, minute, and second fields, with valid values as defined for the DATE and TIME data types.
INTERVAL MONTH(p) Number of months between two dates; p is the interval leading precision.
INTERVAL YEAR(p) Number of years between two dates; p is the interval leading precision.
INTERVAL YEAR(p) TO MONTH Number of years and months between two dates; p is the interval leading precision.
INTERVAL DAY(p) Number of days between two dates; p is the interval leading precision.
INTERVAL HOUR(p) Number of hours between two date/times; p is the interval leading precision.
INTERVAL MINUTE(p) Number of minutes between two date/times; p is the interval leading precision.
INTERVAL SECOND(p,q) Number of seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
INTERVAL DAY(p) TO HOUR Number of days/hours between two date/times; p is the interval leading precision.
INTERVAL DAY(p) TO MINUTE Number of days/hours/minutes between two date/times; p is the interval leading precision.
INTERVAL DAY(p) TO SECOND(q) Number of days/hours/minutes/seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
INTERVAL HOUR(p) TO MINUTE Number of hours/minutes between two date/times; p is the interval leading precision.
INTERVAL HOUR(p) TO SECOND(q) Number of hours/minutes/seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
INTERVAL MINUTE(p) TO SECOND(q) Number of minutes/seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.

 

Examples