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

Syntax

CREATE TABLE table-name [ option ] [ IN DICTIONARY ] 
	[ 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'  
| 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 
| 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' | user-defined-name

Remarks

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 PK 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. This is different behavior from previous versions of Pervasive PSQL.

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

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

The maximum length of a constraint name is 20 characters. Pervasive PSQL uses the left most 20 characters of the name after the prefix, if any, has been prepended.

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

If CREATE TABLE succeeds, the data file name for the created table is xxx.mkd, where xxx is the specified table name. If the table already exists, it is not replaced, and error -1303, "Table already exists" is signalled. The user 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 and LONGVARBINARY)

one byte for each column that allows null values

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

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, Pervasive 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 fixed-length column sizes in bytes) + (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

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 to define the delete rule Pervasive 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, Pervasive PSQL uses the delete cascade rule. When a user deletes a row in the parent table, the database engine deletes the corresponding row in the dependent table.

If you specify RESTRICT, Pervasive 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, Pervasive PSQL applies the restrict rule by default.

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

Update Rule

Pervasive 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 forALTER TABLE .

USING

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

Because Pervasive 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 SQLPrepare is called. 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 be 1 to 64 characters in length, such that the entry as specified fits in Xf$Loc in X$File. The entry is stored in Xf$Loc exactly as typed (trailing spaces are truncated and ignored).

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 also allowed. For example, assuming that the first data path is D:\mydata\DEMODATA, Pervasive 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. Pervasive 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. Pervasive 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, Pervasive 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, Pervasive 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
Pervasive 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, by issuing a CREATE TABLE USING statement with a column definition of type IDENTITY), Pervasive PSQL automatically checks the Btrieve indexes defined on the file to determine whether an existing Btrieve index offers the set of parameters requested by the relational index definition. If an existing Btrieve index matches the new definition being created, then an association is created between the relational index definition and the existing Btrieve index. If there is no match, then Pervasive PSQL creates a new index definition and, if IN DICTIONARY is not specified, a new index in the data file.

WITH REPLACE

Whenever WITH REPLACE is specified with the USING keyword, Pervasive PSQL automatically overwrites any existing file name with the specified file name. The existing file is always overwritten as long as the operating system allows it.

WITH REPLACE affects only the data file, it never affects 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, Pervasive PSQL creates a new data file to replace the existing file (if the file exists at the location you specified in the USING clause). Pervasive 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, Pervasive PSQL returns a status code and does not create a new file.

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

Note
No data is lost or discarded if WITH REPLACE is used with ALTER TABLE. The newly created data file, even if overwriting an existing file, still contains all data from the previous file. You cannot lose data by issuing an ALTER TABLE command.

DCOMPRESS

The DCOMPRESS option specifies that the data file for the specified table should 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')

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')

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 versions 9.5 and newer.

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 both methods, see Methods for Handling Duplicate Keys in Advanced Operations Guide.)

The following conditions apply to the use of the LINKDUP keyword.

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

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.

The ALTER TABLE statement does not support the LINKDUP option.

Examples

The following example 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)

The following 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 is similar to the one just discussed, except the ID column, which is the primary key, is designated as UNIQUE.

CREATE TABLE organizations  
(ID UBIGINT UNIQUE, 
Name LONGVARCHAR, 
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, 

PRIMARY KEY(ID))

In the next example, assume that you need a table called StudentAddress to contain students' addresses. You need to alter the Student table's id column to be a primary key and then create a StudentAddress table. (The Student table is part of the DEMODATA sample database.) Four ways are shown how 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 if a row is added to the StudentAddress table that has an id that does not occur in the Student table, an error occurs. In other words, you must have a parent row before you can have foreign keys referring to that row. This is the default behavior of Pervasive PSQL. Moreover, Pervasive PSQL does not support any other UPDATE rules. Thus, whether this rule is stated explicitly or not makes no difference.

CREATE TABLE StudentAddress (id UBIGINT REFERENCES 
Student (id) ON UPDATE 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 DELETE RESTRICT and UPDATE RESTRICT rules. The Pervasive PSQL parser accepts this syntax with RI rules. However, as stated above, the UPDATE RESTRICT rule is redundant since Pervasive PSQL does not behave any other way with respect to UPDATE rules.

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

This next 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 Pervasive 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 ordered, 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.

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