CREATE TABLE

The CREATE TABLE statement creates a new table in a database.

CREATE TABLE contains functionality that goes beyond minimal or core SQL conformance. CREATE TABLE supports Referential Integrity features. PSQL conforms closely to SQL 92 with the exception of ColIDList support.

You can also create temporary tables with the CREATE TABLE statement. See CREATE (temporary) TABLE.

Caution In the same directory, no two files should share the same file name and differ only in their file name extension. For example, do not create a table (data file) Invoice.btr and another one Invoice.mkd in the same directory. This restriction applies because the database engine uses the file name for various areas of functionality while ignoring the file name extension. Since only the file name is used to differentiate files, files that differ only in their file name extension look identical to the database engine.

Syntax

CREATE TABLE table-name [ option ] [ USING ]

[ USING 'path_name'] [ WITH REPLACE ]

( table-element [ , table-element ]... )

table-name ::= user-defined-name

option ::= DCOMPRESS | PCOMPRESS | PAGESIZE = size | LINKDUP = number

number ::= user-defined value (sets the number of pointers to reserve for the addition of linked duplicates index keys)

table-element ::= column-definition | table-constraint-definition

column-definition ::= column-name data-type [ DEFAULT default-value-expression ] [ column-constraint [ column-constraint ]... [ CASE (string) | COLLATE collation-name ]

column-name ::= user-defined-name

data-type ::= data-type-name [ (precision [ , scale ] ) ]

precision ::= integer

scale ::= integer

default-value-expression ::= default-value-expression + default-value-expression

| default-value-expression - default-value-expression

| default-value-expression * default-value-expression

| default-value-expression / default-value-expression

| default-value-expression & default-value-expression

| default-value-expression | default-value-expression

| default-value-expression ^ default-value-expression

| ( default-value-expression )

| -default-value-expression

| +default-value-expression

| ~default-value-expression

| ?

| literal

| scalar-function

| { fn scalar-function }

| USER

| NULL

literal ::= 'string' | N'string'

| number

| { d 'date-literal' }

| { t 'time-literal' }

| { ts 'timestamp-literal' }

scalar-function ::= see Scalar Functions

column-constraint ::= [ CONSTRAINT constraint-name ] col-constraint

constraint-name ::= user-defined-name

col-constraint ::= NOT NULL

| NOT MODIFIABLE

| UNIQUE

| PRIMARY KEY

| REFERENCES table-name [ ( column-name ) ] [ referential-actions ]

table-constraint-definition ::= [ CONSTRAINT constraint-name ] table-constraint

table-constraint ::= UNIQUE ( column-name [ , column-name ]... )

| PRIMARY KEY ( column-name [ , column-name ]... )

| FOREIGN KEY ( column-name [ , column-name ] )

REFERENCES table-name [ ( column-name [ , column-name ]... ) ] [ referential-actions ]

referential-actions ::= referential-update-action [ referential-delete-action ]

| referential-delete-action [ referential-update-action ]

referential-update-action ::= ON UPDATE RESTRICT

referential-delete-action ::= ON DELETE CASCADE

| ON DELETE RESTRICT

collation-name ::= 'string'

Remarks

The only indexes that can be created with the CREATE TABLE statement are IDENTITY, SMALLIDENTITY, or BIGIDENTITY, primary keys, and foreign keys. All other indexes must be created with the CREATE INDEX statement.

Foreign key constraint names must be unique in the dictionary. All other constraint names must be unique within the table in which they reside and must not have the same name as a column.

If the primary key name is omitted, the name of the first column in the key prefixed by “PK_” is used as the name of the constraint.

If a reference column is not listed, the reference becomes, by default, the primary key of the table referenced. If a primary key is unavailable, a “Key not found” error returns. You can avoid this situation by enumerating the target column.

If the foreign key name is omitted, the name of the first column in the key prefixed by “FK_” is used as the name of the constraint.

If the UNIQUE constraint is omitted, the name of the first column in the key prefixed by “UK_” is used as the name of the constraint.

If the NOT MODIFIABLE constraint is omitted, the name of the first column in the key prefixed by “NM_” is used as the name of the constraint. (If NOT MODIFIABLE is used, a not-unique, not-modifiable index is created on the column. The index is named NM_column_name.)

If the NOT NULL constraint is omitted, the name of the first column in the key prefixed by “NN_” is used as the name of the constraint.

A foreign key may reference the primary key of the same table (known as a self-referencing key).

If CREATE TABLE succeeds and a USING clause was not specified, the data file name for the created table is xxx.mkd, where xxx is the specified table name. If the physical file, xxx.mkd, already exists, a new file names xxxnnn.mkd is created, where nnn is a unique number. If the table already exists, it is not replaced, and error -1303, “Table already exists” is returned. You must drop the table before replacing it.

Limitations on Record Size

The total size of the fixed-length portion of any data record may not exceed 65535 bytes. The fixed-length portion of any data record is made up of the following:

•all the columns that have a fixed sized (all columns except for LONGVARCHAR, LONGVARBINARY and NLONGVARCHAR)

•one byte for each column that allows null values

•8 bytes for each variable-length column (column of type LONGVARCHAR, LONGVARBINARY or NLONGVARCHAR).

If you attempt to create a table that exceeds this limit, or if you attempt modifications that would cause a table to exceed the limit, PSQL returns Status Code -3016, “The maximum fixed-length rowsize for the table has been exceeded.”

To determine the size in bytes of the fixed-length portion of a record before you attempt to create a new table, you can use the following calculation:

(sum of the storage sizes in bytes for the fixed-length column ) + (number of nullable columns) + ( 8 * number of variable-length columns) = record size in bytes

If you want to determine the size of the fixed-length portion of the record for an existing data file, you can use the BUTIL -STAT command to display a report that includes this information.

Example of Limitation on Record Size

Assume you have a table with the following columns defined:

Type	Number of Columns of This Type	Nullable?
VARCHAR(216)	1	Yes
VARCHAR(213)	5	All columns
CHAR(42)	1494	All columns

Each VARCHAR has two extra bytes reserved for it. One bite for the preceding NULL indicator and one trailing byte because VARCHAR is implemented as a ZSTRING. Each CHAR has a preceding byte reserved for the NULL indicator.

Therefore, the record size is 1 x 218 + 5 x 215 + 1494 x 43 = 65535 bytes

In this example, you could not add another column of any length without exceeding the fixed-length limit.

Delete Rule

You can include an ON DELETE clause with a foreign key constraint to define the delete rule PSQL enforces for an attempt to delete the parent row to which a foreign key value refers. The delete rules you can choose are as follows:

•If you specify CASCADE, PSQL uses the delete cascade rule. When a user deletes a row in the parent table, the database engine deletes the corresponding rows in the dependent table.

•If you specify RESTRICT, PSQL enforces the delete restrict rule. A user cannot delete a row in the parent table if a foreign key value refers to it.

If you do not specify a delete rule, PSQL applies the restrict rule by default.

Use caution with delete cascade. PSQL allows a circular delete cascade on a table that references itself. See examples in Delete Cascade in Advanced Operations Guide.

Update Rule

PSQL enforces the update restrict rule. This rule prevents the addition of a row containing a foreign key value if the parent table does not contain the corresponding primary key value. This rule is enforced whether or not you use the optional ON UPDATE clause, which allows you to specify the update rule explicitly.

IN DICTIONARY

See the discussion of IN DICTIONARY for ALTER TABLE.

USING

The USING keyword allows you to associate a CREATE TABLE or ALTER TABLE action with a particular data file.

Because PSQL requires a Named Database to connect, the path_name provided must always be a simple file name or relative path and file name. Paths are always relative to the first Data Path specified for the Named Database to which you are connected.

The path/file name passed is partially validated when the statement is prepared.

The following rules must be followed when specifying the path name:

•The text must be enclosed in single quotes, as shown in the grammar definition.

•Text must not exceed the length limit for the version of metadata being used. The entry is stored in Xf$Loc in exactly as typed (trailing spaces are truncated and ignored). See Xf$Loc (for V1 metadata) and Xf$Loc (for V2 metadata).

•The path must be a simple, relative path. Paths that reference a server or volume are not allowed.

•Relative paths containing a period (‘.’ - current directory), double-period (‘..’ - parent directory), slash ‘\’, or any combination of the three are allowed. The path must contain a file name representing the SQL table name (path_name cannot end in a slash ‘\’ or a directory name). All file names, including those specified with relative paths, are relative to the first Data Path as defined in the Named Database configuration.

•Root-based relative paths are allowed. For example, assuming that the first data path is D:\mydata\demodata, PSQL interprets the path name in the following statement as D:\temp\test123.btr.

CREATE TABLE t1 USING '\temp\test123.btr' (c1 int)

•Slash (‘\’) characters in relative paths may be specified either Linux style (‘/’) or in the customary back slash notation (‘\’), depending on your preference. You may use a mixture of the two types, if desired. This is a convenience feature since you may know the directory structure scheme, but not necessarily know (or care) what type of server you are connected to. The path is stored in X$File exactly as typed. PSQL engine converts the slash characters to the appropriate platform type when utilizing the path to open the file. Also, since data files share binary compatibility between all supported platforms, this means that as long as the directory structure is the same between platforms (and path-based file names are specified as relative paths), the database files and DDFs can be moved from one platform to another with no modifications. This makes for a much simpler cross-platform deployment with a standardized database schema.

•If specifying a relative path, the directory structure in the USING clause must first exist. PSQL does not create directories to satisfy the path specified in the USING clause.

Include a USING clause to specify the physical location of the data file associated with the table. This is necessary when you are creating a table definition for an existing data file, or when you want to specify explicitly the name or physical location of a new data file.

If you do not include a USING clause, PSQL generates a unique file name (based on the table name with the extension .MKD) and creates the data file in the first directory specified in the data file path associated with the database name.

If the USING clause points to an existing data file, PSQL creates the table in the DDFs and returns SQL_SUCCESS_WITH_INFO. The informational message returned indicates that the dictionary entry now points to an existing data file. If you want CREATE TABLE to return only SQL_SUCCESS, specify IN DICTIONARY on the CREATE statement. If WITH REPLACE is specified (see below), then any existing data file with the same name is destroyed and overwritten with a newly created file.

Note PSQL returns a successful status code if you specify an existing data file.

Whenever you create a relational index definition for an existing data file (for example, CREATE TABLE USING with a column definition of type IDENTITY), PSQL automatically checks the Btrieve indexes defined on the file to determine whether an existing Btrieve index offers the set of parameters in the relational index definition. If an existing Btrieve index matches the new definition, then an association is created between the relational index definition and the existing Btrieve index. If there is no match, then PSQL creates a new index definition and, if IN DICTIONARY is not used, a new index in the file.

WITH REPLACE

Whenever WITH REPLACE is specified with the USING keyword, PSQL automatically overwrites any existing file name with the specified file name. The file is always overwritten if the operating system allows it. WITH REPLACE affects only the data file. It does not affect the DDFs.

The following rules apply when using WITH REPLACE:

•WITH REPLACE can only be used with USING.

•When used with IN DICTIONARY, WITH REPLACE is ignored because IN DICTIONARY specifies that only the DDFs are affected.

If you include WITH REPLACE in your CREATE TABLE statement, PSQL creates a new data file to replace the existing file (if the file exists at the location you specified in the USING clause). PSQL discards any data stored in the original file with the same name. If you do not include WITH REPLACE and a file exists at the specified location, PSQL returns a status code and does not create a new file. The table definition is added to the DDFs, however.

WITH REPLACE affects only the data file. It does not affect the table definition in the dictionary.

DCOMPRESS

The DCOMPRESS option specifies that the data file for a table use record compression to reduce the file size on disk. The following example creates a table with record compression and page size 1024 bytes:

CREATE TABLE t1 DCOMPRESS PAGESIZE=1024 (c1 INT DEFAULT 10, c2 CHAR(10) DEFAULT 'abc')

For details, see Record and Page Compression in Advanced Operations Guide.

PCOMPRESS

The PCOMPRESS option specifies that the data file for the specified table should use page compression. The following example creates a table with page compression and page size 1024 bytes:

CREATE TABLE t1 PCOMPRESS PAGESIZE=1024 (c1 INT DEFAULT 10, c2 CHAR(10) DEFAULT 'abc')

For details, see Record and Page Compression in Advanced Operations Guide.

PAGESIZE

The PAGESIZE option specifies that the data file for the specified table should use pages of size bytes. The value of size can be any of the following depending on file version:

•512–4096 for file versions prior to 9.0 (a multiple of 512 bytes up to 4096)

•512, 1024, 1536, 2048, 2560, 3072, 3584, 4096, or 8192 for file version 9.0

•1024, 2048, 4096, 8192, or 16384 for file version 9.5

•4096, 8192, or 16384 for file version 13.0

The following example creates a table with file compression and page size 8192 bytes, specifying creation of the particular data file identified by the relative path, ..\data1.mkd:

CREATE TABLE t1 DCOMPRESS PAGESIZE=8192 USING '..\data1.mkd' (c1 INT DEFAULT 10, c2 CHAR(10) DEFAULT 'abc')

LINKDUP

Multiple records may carry the same duplicated value for index keys. The two methods to keep track of the records with duplicate key values are called linked duplicates (linkdup) and repeating duplicates. For a detailed discussion of linked duplicates and repeating duplicates, see Methods for Handling Duplicate Keys in Advanced Operations Guide.

If the LINKDUP keyword is not specified, a CREATE INDEX statement uses the repeating duplicates method.

Each linked duplicate index requires 8 extra bytes in the physical record. The LINKDUP keyword allows you to reserve these extra bytes for use in linked duplicated indexes that are subsequently created.

Thus, if the LINKDUP keyword is specified, the following applies:

•A CREATE INDEX statement uses the linked duplicates method up to the value specified for the number of pointers

•Once the value specified for the number of pointers is reached, a CREATE INDEX statement uses the repeating duplicates method

•If the value specified for the number of pointers has been reached and a linked-duplicate index is dropped, a CREATE INDEX statement uses the linked duplicates method for the next key

•A CREATE INDEX statement cannot create a repeating-duplicate key if pointers are still reserved for linked-duplicate keys.

Examples

The following examples demonstrate various uses of CREATE TABLE.

Syntax like the following creates a table named Billing with columns Student_ID, Transaction_Number, Log, Amount_Owed, Amount_Paid, Registrar_ID and Comments, using the specified data types.

CREATE TABLE Billing

(Student_ID UBIGINT,

Transaction_Number USMALLINT,

Log TIMESTAMP,

Amount_Owed DECIMAL(6,2),

Amount_Paid DECIMAL(6,2),

Registrar_ID DECIMAL(10,0),

Comments LONGVARCHAR)

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

This example creates a table named Faculty in the database with columns ID, Dept_Name, Designation, Salary, Building_Name, Room_Number, Rsch_Grant_Amount, and a primary key based on column ID.

CREATE TABLE Faculty

(ID UBIGINT,

Dept_Name CHAR(20) CASE,

Designation CHAR(10) CASE,

Salary CURRENCY,

Building_Name CHAR(25) CASE,

Room_Number UINTEGER,

Rsch_Grant_Amount DOUBLE,

PRIMARY KEY (ID))

The following example creates an index on the Name column and designates that index as not modifiable. Data in the Name column cannot be changed.

CREATE TABLE organizations

(Name LONGVARCHAR NOT MODIFIABLE,

Advisor CHAR(30),

Number_of_people INTEGER,

Date_started DATE,

Time_started TIME,

Date_modified TIMESTAMP,

Total_funds DOUBLE,

Budget DECIMAL(2,2),

Avg_funds REAL,

President VARCHAR(20) CASE,

Number_of_executives SMALLINT,

Number_of_meetings TINYINT,

Office UTINYINT,

Active BIT,)

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

In the next example, assume that you need a table called StudentAddress to contain student addresses. You need to alter the Student table id column to be a primary key and then create a StudentAddress table that references Student as the primary table. (The Student table is part of the Demodata sample database.) Four ways are shown to create the StudentAddress table.

First, make the id column of table Student a primary key.

ALTER TABLE Student ADD PRIMARY KEY (id)

This next statement creates a StudentAddress table to have a foreign key referencing the id column of table Student with the DELETE CASCADE rule. This means that whenever a row is deleted from the Student table (Student is the parent table in this case), all rows in the StudentAddress table with that same id are also deleted.

CREATE TABLE StudentAddress (id UBIGINT REFERENCES Student (id) ON DELETE CASCADE, addr CHAR(128))

This next statement creates a StudentAddress table to have a foreign key referencing the id column of table Student with the DELETE RESTRICT rule. This means that whenever a row is deleted from the Student table and there are rows in the StudentAddress table with that same id, an error occurs. You need to explicitly delete all the rows in StudentAddress with that id before the row in the Student table, the parent table, can be deleted.

CREATE TABLE StudentAddress (id UBIGINT REFERENCES Student (id) ON DELETE RESTRICT, addr CHAR(128))

This next statement creates a StudentAddress table to have a foreign key referencing the id column of table Student with the UPDATE RESTRICT rule. This means that an error occurs if a row is added to the StudentAddress table with an ID that does not occur in the Student table. In other words, you must have a parent row before you can have foreign keys refer to that row. This is the default behavior of PSQL.

Moreover, PSQL does not support any other UPDATE rules. Thus, whether this rule is stated explicitly makes no difference. Also, since a DELETE rule is not explicitly stated, DELETE RESTRICT is assumed.

CREATE TABLE StudentAddress (id UBIGINT REFERENCES Student (id) ON UPDATE RESTRICT, addr CHAR(128))

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

This example shows how to use an alternate collating sequence (ACS) when you create a table. The ACS file used is the sample one provided with PSQL.

CREATE TABLE t5 (c1 CHAR(20) COLLATE 'file_path\upper.alt')

Upper.alt treats upper and lower case letters the same for sorting. For example, if a database has values abc, ABC, DEF, and Def, inserted in that order, then the sorting with upper.alt returns as abc, ABC, DEF, and Def.

The values abc and ABC, and the values DEF and Def are considered duplicates and are returned in the order in which they were inserted. Normal ASCII sorting sequences upper case letters before lower case, such that the sorting would return as ABC, DEF, Def, abc. Also, the statement SELECT c1 FROM t5 WHERE c1 = 'Abc' returns both abc and ABC.

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

The following example creates a table, t1, and reserves the number of pointers to use for linked duplicate keys to four. The CREATE INDEX statements create index keys for the table.

DROP table t1

CREATE table t1 LINKDUP=4 (c1 int, c2 int, c3 int)

CREATE INDEX link_1 on t1(c1,c2)

CREATE INDEX link_2 on t1(c1,c3)

CREATE UNIQUE INDEX link_3 on t1(c3)

CREATE INDEX link_4 on t1(c1)

CREATE INDEX link_5 on t1(c2)

CREATE INDEX link_6 on t1(c2,c3)

The results of the CREATE INDEX statements are the following:

•Linked duplicate keys: link_1, link_2, link_4, link_5

•Repeating duplicate keys: link_6 (because the number of pointers to use for linked duplicate keys reached the specified value, four)

DROP INDEX link_2

CREATE INDEX link_7 on t1(c3,c1)

These two statements result in the following:

•Linked duplicate keys: link_1, link_4, link_5, link_7 (because the DROP INDEX statement reduced the number of pointers to use for linked duplicate keys to three, which allowed link_7 to become the fourth linked duplicates index key)

•Repeating duplicate keys: link_6

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

The following statement creates a table and specifies that the columns should not allow NULL values (that is, null indicator bytes are not added).

CREATE TABLE NoNulls

(ID UBIGINT NOT NULL,

Name CHAR(20) NOT NULL CASE,

Amount DOUBLE NOT NULL)

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

If you need to create all columns as NOT NULL, you can first use the SET TRUENULLCREATE statement to disable the creation of true nulls, then create the table. This allows you to avoid specifying the NOT NULL attribute on each column. (See SET TRUENULLCREATE.) Note, however, that the resulting legacy table does not enforce a NOT NULL attribute on any columns. NULL is allowed even if NOT NULL is explicitly specified for the column. The following statements create the same table as in the previous example.

SET TRUENULLCREATE=OFF

CREATE TABLE NoNulls2

(ID BIGINT,

Name CHAR(20) CASE,

Amount DOUBLE)

SET TRUENULLCREATE=ON

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

CREATE TABLE supports the specification of a DEFAULT value for columns. This is used when rows are inserted without an explicitly specified value for that column. The next statement creates a table with defaults matching the column data types. Note that IDENTITY columns have an implied default of zero, which automatically generates the next highest value.

CREATE TABLE Defaults

(ID IDENTITY,

Name CHAR(20) DEFAULT 'none',

Amount DOUBLE DEFAULT 0.00,

EntryDay DATE DEFAULT CURDATE(),

EntryTime TIME DEFAULT CURTIME())

The next statements insert two rows using the defaults.

INSERT INTO Defaults (ID) VALUES (0)

INSERT INTO Defaults (ID, Name, Amount) VALUES (0, 'Joquin', '100')

A SELECT statement returns the results, containing default values.

SELECT * FROM Defaults

ID Name Amount EntryDay EntryTime

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

1 none 0.0 curdate curtime

2 Joquin 100.0 curdate curtime

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

The following example assumes that you have a table Legacydata that contains columns with legacy data types in data file olddata.dat. New databases cannot create tables with the legacy data types. You could, however, create a DDF definition in a new database for Legacydata with the IN DICTIONARY clause.

CREATE TABLE "Legacydata" IN DICTIONARY USING 'olddata.dat' (

"col1" LSTRING(10) NOT NULL,

"col2" VARCHAR(9) NOT NULL,

"col3" LOGICAL NOT NULL,

"col4" LOGICAL2 NOT NULL,

"col5" NOTE(100) NOT NULL);

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

This example demonstrates the default creation of a Btrieve data file if a table is created without specifying either a USING clause or REPLACE. The default name of the file is the table name with the extension .mkd. If that file name already exists, a different name is generated using the table name followed by a number and then the .mkd extension.

To create the table xyz, which generates the data file xyz.mkd:

CREATE TABLE xyz (c1 int, c2 char(5))

Now, delete the table using IN DICTIONARY, so the data file is not deleted:

DROP TABLE xyz in dictionary

Finally, if you create table xyz again:

CREATE TABLE xyz (c1 int, c2 char(5))

It creates the table xyz and the data file xyz000.mkd.