Pervasive logo

Prev SQL Engine Reference Next

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.SQL conforms closely to SQL 92 with the exception of ColIDList support.

Syntax 

CREATE TABLE table-name [ option ] [ IN DICTIONARY ] 
	[ USING 'path_name'] [ WITH REPLACE ]  
	( table-element [ , table-element ]... ) 
 
option ::= DCOMPRESS | PAGESIZE = size  
 
table-name ::= user-defined-name 
 
table-element ::= column-definition | table-constraint-definition 
 
column-definition ::= column-name data-type [ DEFAULT default-value ] [ column-constraint [ column-constraint ]... [CASE | COLLATE collation-name ] 
 
column-name ::= user-defined-name 
 
data-type ::= data-type-name [ (precision [ , scale ] ) ] 
 
precision ::= integer 
 
scale ::= integer 
 
default-value ::= literal 
 
literal ::= 'string' 

| number 
| { d 'date-literal' } 
| { t 'time-literal' } 
| { ts 'timestamp-literal' } 
 


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

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

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

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, the SRDE 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:

 
Table 3-2

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.SQL 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 do not specify a delete rule, Pervasive.SQL applies the restrict rule by default.

Update Rule

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

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.SQL 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, the SRDE 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.SQL 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), the SRDE 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 the SRDE 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.SQL 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:

If you include WITH REPLACE in your CREATE TABLE statement, Pervasive.SQL creates a new data file to replace the existing file (if the file exists at the location you specified in the USING clause). Pervasive.SQL 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.SQL 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 data compression to reduce the file size on disk.

The following example creates a table with file compression and page size 1024 bytes: 

CREATE TABLE t1 DCOMPRESS 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 multiple of 512 from 512 to 4096 bytes. Default value is 4096 bytes. Generally speaking, most applications perform best with the default page size.

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

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

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.SQL. Moreover, Pervasive.SQL 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.SQL parser accepts this syntax with RI rules. However, as stated above, the UPDATE RESTRICT rule is redundant since Pervasive.SQL 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.SQL.

On Windows platforms: 

CREATE TABLE t5 (c1 CHAR(20) COLLATE 'c:\pvsw\samples\upper.alt')

On NetWare: 

CREATE TABLE t5 (c1 CHAR(20) COLLATE '\\Odin\sys:pvsw\samples\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.

See Also

DROP TABLE

Prev
CREATE PROCEDURE		 Contents
Up
Check for Revisions 		Next
CREATE TRIGGER