Pervasive logo

Prev SQL Engine Reference Next

ALTER TABLE

The ALTER TABLE statement modifies a table definition.

Syntax 

ALTER TABLE table-name [ IN DICTIONARY ] 
	[ USING 'path_name'] [ WITH REPLACE ] alter-option-list 
 
table-name ::= user-defined-name 
 
alter-option-list ::= alter-option 

|(alter-option [, alter-option}...) 


 
alter-option ::= ADD [ COLUMN ] column-definition 

| ADD table-constraint-definition 
| DROP [ COLUMN ] column-name 
| DROP CONSTRAINT constraint-name 
| DROP PRIMARY KEY 
| MODIFY [ COLUMN ] column-definition 
| ALTER [ COLUMN ] column-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 ] 
 


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

Remarks

Refer to CREATE TABLE for information pertaining to primary and foreign keys and referential integrity.

Modifying Column Data Types

You cannot change a column with INTEGER data type into one with an IDENTITY data type using an ALTER TABLE statement. If you attempt to do so, you will receive status code -3018.

IN DICTIONARY

The purpose of using this keyword is to notify the SQL Relational Database Engine (SRDE) that you wish to make modifications to the DDFs, while leaving the underlying physical data unchanged. IN DICTIONARY is a very powerful and advanced feature. It should only be used by system administrators or when absolutely necessary. Normally, the SRDE keeps DDFs and data files totally synchronized, but this feature allows users the flexibility to force table dictionary definitions to match an existing data file. This can be useful when you want to create a definition in the dictionary to match an existing data file, or when you want to use a USING clause to change the data file path name for a table.

You cannot use this keyword on a bound database.

IN DICTIONARY is allowed on CREATE and DROP TABLE, in addition to ALTER TABLE. IN DICTIONARY affects dictionary entries only, no matter what CREATE/ALTER options are specified. Since Pervasive.SQL allows multiple options (any combination of ADD, DROP, ADD CONSTRAINT, and so on), IN DICTIONARY is honored under all circumstances to guarantee only the DDFs are affected by the schema changes.

Tables that exist in the DDFs only (the data file does not exist) are called detached entries. These tables are inaccessible via queries or other operations that attempt to open the physical underlying file. For this reason, IN DICTIONARY was added to DROP TABLE, because it is now possible to create detached entries using CREATE TABLE.

The error "Table not found" results if you query a detached table or a table that does not exist. If you determine that a table exists but you receive the "Table not found" error, the error resulted because the data file could not be opened. This indicates a detached table.

You may verify whether a table really exists by using SQLTables or directly querying the Xf$Name column of X$File: 

SELECT * FROM X$File WHERE Xf$Name = 'table_name'

It is possible for a detached table to cause confusion, so the IN DICTIONARY feature must be used with extreme care. It is crucial that it should be used to force table definitions to match physical files, not to detach them. Consider the following examples, assuming that the file test123.btr does not exist. (USING is explained below, in the next subtopic.) 

CREATE TABLE t1 USING 't1.btr' (c1 INT) 
ALTER TABLE t1 IN DICTIONARY USING 'test123.btr'

Or, combining both statements: 

CREATE TABLE t1 IN DICTIONARY USING 'test123.btr' (c1 INT)

If you then attempt to SELECT from t1, you receive an error that the table was not found. Confusion can arise, because you just created the table-how can it not be found? Likewise, if you attempt to DROP the table without specifying IN DICTIONARY, you receive the same error. These errors are generated because there is no data file associated with the table.

Whenever you create a relational index definition for an existing Btrieve data file (for example, by issuing an ALTER TABLE statement to add 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.

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 and name of an existing data file to associate with an existing table. A USING clause also allows you to create a new data file at a particular location using an existing dictionary definition. (The string supplied in the USING clause is stored in the Xf$Loc column of the dictionary file X$File.) The original data file must be available when you create the new file since some of the file information must be obtained from the original.

In the DEMODATA sample database, the Person table is associated with the file PERSON.MKD. If you create a new file named PERSON2.MKD, the statement in the following example changes the dictionary definition of the Person table so that the table is associated with the new file. 

ALTER TABLE Person IN DICTIONARY USING 'person2.mkd'

You must use either a simple file name or a relative path in the USING clause. If you specify a relative path, Pervasive.SQL interprets it relative to the first data file path associated with the database name.

The USING clause can be specified in addition to any other standard ALTER TABLE option. This means columns can be manipulated in the same statement that specifies the USING path.

If you specify a data file name that differs from the data file name currently used to store the table data, the SRDE creates the new file and copies all of the data from the existing file into the new file. For example, suppose person.mkd is the current data file that holds the data for table Person. You then alter table Person using data file person2.mkd, as shown in the statement above. The contents of person.mkd are copied into person2.mkd. Person2.mkd then becomes the data file associated with table Person and database operations affect person2.mkd. Person.mkd is not deleted, but it is not associated with the database any more.

The reason for copying the data is because Pervasive.SQL allows all other ALTER TABLE options at the same time as USING. The new data file created needs to be fully populated with the existing table's data. The file structure is not simply copied, but instead the entire contents are moved over, similar to a Btrieve BUTIL -CREATE and BUTIL -COPY. This can be helpful for rebuilding an SQL table, or compressing a file that once contained a large number of records but now contains only a few.

Note
ALTER TABLE USING copies the contents of the existing data file into the newly specified data file, leaving the old data file intact but unlinked.

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:

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.

Include WITH REPLACE in a USING clause to instruct Pervasive.SQL to replace an existing file (the file must reside at the location you specified in the USING clause). If you include WITH REPLACE, Pervasive.SQL creates a new file and copies all the data from the existing file into it. 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 the new file. The status code is SRDE error -4940, Btrieve error 59.

MODIFY COLUMN and ALTER COLUMN

The ability to modify the nullability or data type of a column is subject to the following restrictions:

If you must change the data type of a key column, you can do so by dropping the key, changing the data type, and re-adding the key. Keep in mind that you must ensure that all associated key columns remain synchronized. For example, if you have a primary key in table T1 that is referenced by foreign keys in tables T2 and T3, you must first drop the foreign keys. Then you can drop the primary key. Then you need to change all three columns to the same data type. Finally, you must re-add the primary key and then the foreign keys.

The ANSI standard includes the ALTER keyword. Pervasive.SQL allows both keywords (ALTER and MODIFY) in the ALTER TABLE statement: 

ALTER TABLE T1 MODIFY C1 INTEGER 
ALTER TABLE T1 ALTER C1 INTEGER 
ALTER TABLE T1 MODIFY COLUMN C1 INTEGER 
ALTER TABLE T1 ALTER COLUMN C1 INTEGER

Pervasive.SQL allows altering a column to a smaller length if the actual data does not overflow the new, smaller length of the column. This behavior is similar to that of Microsoft SQL Server.

You can add, drop, or modify multiple columns on a single ALTER TABLE statement. Although it simplifies operations, this behavior is not considered ANSI-compliant. The following is a sample multi-column ALTER statement. 

ALTER TABLE T1 (ALTER C2 INT, ADD D1 CHAR(20), DROP C4, ALTER C5 LONGVARCHAR, ADD D2 LONGVARCHAR NOT NULL)

You can convert all legacy data types (Pervasive.SQL v7 or earlier) to data types that are natively supported by Pervasive.SQL V8. But the new data types cannot be converted backwards to legacy data types.

To add a LONGVARCHAR/LONGVARBINARY column to a legacy table that contains a NOTE/LVAR column, the NOTE/LVAR column first has to be converted to a LONGVARCHAR or LONGVARBINARY column. After converting the NOTE/LVAR column to LONGVARCHAR/LONGVARBINARY, you can add more LONGVARCHAR/LONGVARBINARY columns to the table. Note that the legacy engine does not work with this new table because the legacy engine can work with only one variable length column per table.

Examples

The following statement adds the Emergency_Phone column to the Person table 

ALTER TABLE person add Emergency_Phone NUMERIC(10,0)

The following statement adds two integer columns col1 and col2 to the Class table. 

ALTER TABLE class(add col1 INT, add col2 INT)

To drop a column from a table definition, specify the name of the column in a DROP clause. The following statement drops the emergency phone column from the Person table. 

ALTER TABLE person drop Emergency_Phone

The following statement drops col1 and col2 from the Class table. 

ALTER TABLE class(drop col1, drop col2)

The following statement drops the constraint c1 in the Faculty table. 

ALTER TABLE Faculty(drop CONSTRAINT c1)

This example adds an integer column col3 and drops column col2 to the Class table 

ALTER TABLE class(add col3 INT, drop col2 )

The following example creates a primary key named c1 on the ID field in the Faculty table. Note that you cannot create a primary key on a Nullable column. Doing so generates the error, "Nullable columns are not allowed in primary keys". 

ALTER TABLE Faculty(add CONSTRAINT c1 PRIMARY KEY(ID))

The following example creates a primary key PK_ID in the Faculty table. 

ALTER TABLE Faculty(add PRIMARY KEY(ID))

The following example adds the constraint UNIQUE to the columns col1 and col2. 

ALTER TABLE Class(add UNIQUE(col1,col2))

The following example drops the primary key in the Faculty table. Because a table can have only one primary key, you cannot add a primary key to a table that already has a primary key defined. To change the primary key of a table, delete the existing key then add the new primary key. 

ALTER TABLE Faculty(drop PRIMARY KEY)

Before you can drop a primary key from a parent table, you must drop any corresponding foreign keys from dependent tables.

The following example adds a new foreign key to the Class table. The Faculty column is defined as an index that does not include NULL values. You cannot create a foreign key on a Nullable column. 

ALTER TABLE Class ADD CONSTRAINT Teacher FOREIGN KEY (Faculty_ID) REFERENCES Faculty (ID) ON DELETE RESTRICT

In this example, the restrict rule for deletions prevents someone from removing a faculty member from the database without first either changing or deleting all of that faculty's classes.

If you add a foreign key to a table that already contains data, use the Referential Integrity (RI) test to find any data that does not conform to the new referential constraint. The RI test is run from the Check Database wizard in the Pervasive Control Center (PCC).

The following statement shows how to drop the foreign key added in this example. Pervasive.SQL drops the foreign key from the dependent table and eliminates the referential constraints between the dependent table and the parent table. 

ALTER TABLE Class DROP CONSTRAINT Teacher

The following example adds a foreign key to the Class table without using the CONSTRAINT clause. 

ALTER TABLE Class ADD FOREIGN KEY (Faculty_ID) REFERENCES Faculty (ID) ON DELETE RESTRICT

This creates foreign key FK_Faculty_ID. To drop the foreign key, specify the CONSTRAINT keyword: 

ALTER TABLE Class DROP CONSTRAINT FK_Faculty_ID

The following example illustrates multiple adding and dropping of constraints and columns in a table. This statement drops column salary, adds a column col1 of type integer, and drops constraint c1 in the Faculty table. 

ALTER TABLE Faculty(DROP salary, ADD col1 INT, DROP CONSTRAINT c1)

The following examples illustrate altering the data type of multiple columns. 

ALTER TABLE T1 (ALTER C2 INT, ADD D1 CHAR(20), DROP C4, ALTER C5 LONGVARCHAR, ADD D2 LONGVARCHAR NOT NULL) 
ALTER TABLE T2 (ALTER C1 CHAR(50), DROP CONSTRAINT MY_KEY, DROP PRIMARY KEY, ADD MYCOLUMN INT)

The following examples illustrate how the column default and alternate collating sequence can be set or dropped with the ALTER or MODIFY column options. 

CREATE TABLE T1 (c1 INT DEFAULT 10, c2 CHAR(10)) 
ALTER TABLE T1 ALTER c1 INT DEFAULT 20
- resets column c1 default to 20 
ALTER TABLE T1 ALTER c1 INT
- drops column c1 default 
ALTER TABLE T1 ALTER c2 CHAR(10) 
COLLATE 'c:\pvsw\samples\upper.alt'
- on a Windows platform, sets alternate collating sequence on column c2 
ALTER TABLE T1 ALTER c2 CHAR(10) 
COLLATE '\\Odin\sys:pvsw\samples\upper.alt'
- on NetWare, sets alternate collating sequence on column c2 
ALTER TABLE T1 ALTER c2 CHAR(10)
- drops alternate collating sequence on column c2

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.

Prev
ALL		 Contents
Up
Check for Revisions 		Next
ANY