ALTER TABLE
The ALTER TABLE statement modifies a table definition.
Syntax
ALTER TABLE table-name [ IN DICTIONARY ]
[ USING 'path_name'] [ WITH REPLACE ] alter-options
table-name ::= user-defined name
path_name ::= a simple file name or relative path and file name
alter-options ::= alter-option-list1 | alter-option-list2
alter-option-list1 ::= alter-option |(alter-option [, alter-option ]...)
alter-option ::=
ADD [
COLUMN ]
column-definition |
ADD table-constraint-definition| ALTER [ COLUMN ] column-definition
| DROP [ COLUMN ] column-name
| DROP CONSTRAINT constraint-name
| MODIFY [ COLUMN ] column-definition
alter-option-list2 ::= PSQL_MOVE [ COLUMN ] column-name TO [ [ PSQL_PHYSICAL ] PSQL_POSITION ] new-column-position | RENAME COLUMN column-name TO new-column-name
column-definition ::=
column-name
data-type [
DEFAULT default-value-expression ] [
column-constraint [
column-constraint ]... [
CASE (string) |
COLLATE collation-name ]
column-name ::= user-defined name
new-column-position ::= new ordinal position (a positive integer value). The value must be greater than zero and less than or equal to the total number of columns in the table.
new-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 }
| NULL
default-literal ::= 'string' | N'string'
| number
| { d 'date-literal' }
| { t 'time-literal' }
| { ts 'timestamp-literal' }
default-literal ::= 'string' | N'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
| NOT MODIFIABLE
| REFERENCES table-name [ ( column-name ) ] [ referential-actions ]
referential-actions ::= referential-update-action [ referential-delete-action ]
| referential-delete-action [ referential-update-action ]
collation-name ::= 'string'
table-constraint-definition ::= [ CONSTRAINT constraint-name ] table-constraint
table-constraint ::=
UNIQUE (
column-name [ ,
column-name ]... )
REFERENCES table-name
[ ( column-name [ , column-name ]... ) ]
[ referential-actions ]
Remarks
See CREATE TABLE for information pertaining to primary and foreign keys and referential integrity.
Conversions between CHAR, VARCHAR, or LONGVARCHAR and NCHAR, NVARCHAR, or NLONGVARCHAR assume that CHAR values are encoded using the database code page. A column of type LONGVARCHAR cannot be altered to type NLONGVARCHAR nor NLONGVARCHAR to LONGVARCHAR.
ALTER TABLE requires an exclusive lock on a table. If the same table is being held open with another statement, ALTER TABLE fails and returns status code 88. Ensure that you execute all data definition statements before executing data manipulation statements. For an example showing this, see
PSQL_MOVE.
IN DICTIONARY
The purpose of using this keyword is to notify the database engine that you wish to make modifications to the DDFs, while leaving the underlying physical data unchanged. IN DICTIONARY is a powerful feature for advanced users. It should only be used by system administrators, and only when absolutely necessary. Normally, PSQL 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 IN DICTIONARY 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 PSQL 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.
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. (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.)
You can verify whether a table really exists by using the catalog functions (see
System Catalog Functions) or by directly querying the Xf$Name column of X$File:
SELECT * FROM X$File WHERE Xf$Name = 'table_name'
The SELECT statement returns the Xf$Loc value, which is the name of the physical file for the table. Combine the name with a data path defined for the database to get the complete path to the file.
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), 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 PSQL 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 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 and file name passed are 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 quotation marks, as shown in the grammar definition.
•Text must be 1 to 64 characters in length for V1 metadata and 1 to 250 characters for V2 metadata, and is stored in Xf$Loc in X$File. The entry is stored 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 are allowed to contain a period (‘.’ - current directory) , double period (‘..’ - parent directory), slash ‘\’, or any combination of the three. The path must contain a file name representing the SQL table name (path_name cannot end in a slash ‘\’ or a directory name). When you create a file with CREATE or ALTER TABLE, all file names, including those specified with relative paths, are relative to the first Data Path as defined in the Named Database configuration. (If you use IN DICTIONARY, the file name does not have to relative to the first data location.)
•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 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, PSQL 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 and you do not specify IN DICTIONARY, PSQL 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 PSQL allows all other ALTER TABLE options at the same time as USING. The new data file created needs to be fully populated with data from the existing table. 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 a 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 USING, PSQL automatically overwrites any existing file name with the specified file name. The file is always overwritten as long as the operating system allows it.
WITH REPLACE affects only the data file and not 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.
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 PSQL to replace an existing file (the file must reside at the location you specified in the USING clause). If you include WITH REPLACE, PSQL 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, PSQL returns a status code and does not create the new file. The status code is error -4940.
MODIFY COLUMN and ALTER COLUMN
The ability to modify the nullability or data type of a column is subject to the following restrictions:
•The target column cannot have a PRIMARY/FOREIGN KEY constraint defined on it.
•If converting the old type to the new type causes an overflow (arithmetic or size), the ALTER TABLE operation is aborted.
•If a nullable column contains NULL values, the column cannot be changed to a nonnullable column.
If you must change the data type of a primary or foreign key column, you can do so by dropping the constraint, changing the data type of the column, and adding back the constraint. 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 add back the primary key and then the foreign keys.
The ANSI standard includes the ALTER keyword. PSQL also supports use of the keyword MODIFY in the ALTER TABLE statement. The keyword COLUMN is optional. For example:
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
PSQL 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 multicolumn 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 legacy data types (Pervasive.SQL v7 or earlier) to data types natively supported by the current PSQL release. If you wish to convert new data types backward to legacy data types, contact PSQL Support.
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.
PSQL_MOVE
The PSQL_MOVE syntax allows you to keep the columns of a table at desired ordinal positions. You may change the ordinal position of existing columns or for a new column after adding it. You can move a column logically and physically.
Type of Move | Result |
Logical | Columns are rearranged when listed in a result set, but the physical order of the columns in the table does not change. For example, you can rearrange how the columns are listed in a result set with a query such as SELECT * FROM table-name. A logical move affects only queries that list the columns, such as SELECT * FROM from table-name. |
Physical | A column is physically relocated from its current position to a new position in the file. A physical move affects the data file of the table. To move a column physically, you must specify the PSQL_PHYSICAL keyword. If the PSQL_PHYSICAL keyword is omitted, a logical move occurs by default. Note that only column offsets in the DDFs are changed if IN DICTIONARY is used in the ALTER TABLE statement. Columns in the data file are not physically moved because IN DICTIONARY overrides the MOVE . . . PSQL_PHYSICAL syntax for the data file. |
Note Once you move columns logically, that order becomes the default order for listing columns in result sets. For instance, if you move columns physically after moving them logically, the logical order is used for queries such as SELECT * FROM from table-name. Logical column changes are stored in X$Attrib.
The PSQL_MOVE keyword must specify a column location greater than zero but less than the total number of columns. For example, assume that table t1 has only two columns, col1 and col2. Both of the following statement return an error:
ALTER TABLE t1 PSQL_MOVE col1 to 0
ALTER TABLE t1 PSQL_MOVE col1 to 3
The first statement attempts to move the column to position zero. The second statements attempts to move the column to position three, which is a number greater than the total number of columns (two).
ALTER TABLE requires an exclusive lock on a table. If the same table is being help open by another statement, ALTER TABLE fails and returns status code 88. Ensure that you execute all data definition statements before executing data manipulation statements.
For example, the following stored procedure fails and returns status code 88 because the INSERT statement has table t1 open, which prevents the ALTER TABLE statement from obtaining an exclusive lock.
CREATE PROCEDURE proc1() AS
BEGIN
CREATE TABLE t1(c1 INT,c2 INT,c3 INT);
INSERT INTO t1 VALUES (123,345,678);
ALTER TABLE t1 PSQL_MOVE c3 to 1;
END;
A way to resolve this is to execute the statements pertaining first to the table creation and data insertion, then call the procedure:
CREATE TABLE t1(c1 INT,c2 INT,c3 INT);
INSERT INTO t1 VALUES (123,345,678);
CALL proc1;
CREATE PROCEDURE proc1() AS
BEGIN
ALTER TABLE t1 PSQL_MOVE c3 to 1;
END;
RENAME COLUMN
Rename column allows you to change the name of a column to a new name. You cannot rename a column to the name of an existing column in the same table.
Renaming a column can invalidate objects that reference the previous name. For example, a trigger might reference column c1 in table t1. Renaming c1 to c5 results in the trigger being unable to execute successfully.
You can also use the psp_rename system stored procedure to rename columns.
Note The database engine does not check dependencies for renamed columns. If you rename a column, ensure that all objects with a dependency on the previous (changed from) name are revised appropriately.
ON DELETE CASCADE
Examples
This section provides a number of examples of ALTER TABLE.
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 returns an error.
ALTER TABLE Faculty(add CONSTRAINT c1 PRIMARY KEY(ID))
The following example creates a primary key using the default key name PK_ID on the Faculty table.
ALTER TABLE Faculty(add PRIMARY KEY(ID))
============
The following example adds the constraint UNIQUE to the columns col1 and col2. The combined value of col1 and col2 in any row is unique within the table. Neither column needs to be unique individually.
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_ID column is defined as a column 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 member’s classes. Also note that the column listed in the REFERENCES clause (ID) is optional. Columns listed in the REFERENCES clause can be included, if you choose, to improve clarity of the statement. The only columns that can be referenced in the REFERENCES clause is the primary key(s) of the referenced table.
The following statement shows how to drop the foreign key added in this example. PSQL 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. In this case, a foreign key constraint is generated internally to reference the primary key (ID) of Faculty. The column listed in the REFERENCES clause is optional. Columns listed in the REFERENCES clause can be included, if you choose, to improve clarity of the statement. The only column that can be used in the REFERENCES clause is the primary key of the referenced table.
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 shows 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 both 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 'file_path\upper.alt'
– 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.
============
The following statement logically moves column Registrar_ID from its current position to the second position when the columns are listed in a results set.
ALTER TABLE Billing PSQL_MOVE Registrar_ID TO 2
The following statement moves columns Amount_Owed and Amount_Paid from their current positions to the second and third positions, respectively, when the columns are listed in a result set.
ALTER TABLE Billing ( PSQL_MOVE Amount_Owed TO 2, PSQL_MOVE Amount_Paid TO 3 )
============
The following statement physically moves column Registrar_ID from its current position to the second column in the data file. This causes the data file to be rebuilt to reflect the change.
ALTER TABLE Billing PSQL_MOVE Registrar_ID TO PSQL_PHYSICAL 2
The following statement physically moves columns Amount_Owed and Amount_Paid from their current positions to the second and third column positions, respectively, in the data file.
ALTER TABLE Billing ( PSQL_MOVE Amount_Owed TO PSQL_PHYSICAL 2, PSQL_MOVE Amount_Paid TO PSQL_PHYSICAL 3 )
============
Assume that table t1 contains columns c1 and col2. The following statement renames column c1 to c2.
ALTER TABLE t1 RENAME COLUMN c1 TO c2
============
Assume that table t1 contains columns c1 and col2. The following statement returns an error (duplicate column name) because it attempts to rename a column (col2) to the name of an existing column (c1).
ALTER TABLE t1 (RENAME COLUMN c1 TO c2, RENAME COLUMN col2 TO c1)
Instead, you must issue two separate ALTER statements. The first to rename c1 to c2; the second to rename col2 to c1.
ALTER TABLE t1 (RENAME COLUMN c1 TO c2)
ALTER TABLE t1 (RENAME COLUMN col2 TO c1)
See Also