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