Wiki Home

Alter Table SQL


Namespace: WIN_COM_API
Used to change the structure of an existing table.

ALTER TABLE TableName1
  ADD | ALTER [COLUMN] FieldName1
     FieldType [(nFieldWidth [, nPrecision])]
     [NULL | NOT NULL]
     [CHECK lExpression1 [ERROR cMessageText1]]
     [DEFAULT eExpression1]
     [PRIMARY KEY | UNIQUE]
     [REFERENCES TableName2 [TAG TagName1]]
     [NOCPTRANS]
     [NOVALIDATE]
-or-
ALTER TABLE TableName1
  ALTER [COLUMN] FieldName2
     [NULL | NOT NULL]
     [SET DEFAULT eExpression2]
     [SET CHECK lExpression2 [ERROR cMessageText2]]
     [DROP DEFAULT]
     [DROP CHECK]
     [NOVALIDATE]
-or-
ALTER TABLE TableName1
  [DROP [COLUMN] FieldName3]
  [SET CHECK lExpression3 [ERROR cMessageText3]]
  [DROP CHECK]
  [ADD PRIMARY KEY eExpression3 TAG TagName2 [FOR lExpression4]]
  [DROP PRIMARY KEY]
  [ADD UNIQUE eExpression4 [TAG TagName3 [FOR lExpression5]]]
  [DROP UNIQUE TAG TagName4]
  [ADD FOREIGN KEY [eExpression5] TAG TagName4 [FOR lExpression6]
     REFERENCES TableName2 [TAG TagName5]]
  [DROP FOREIGN KEY TAG TagName6 [SAVE]]
  [RENAME COLUMN FieldName4 TO FieldName5]
  [NOVALIDATE]

TableName1 can be an alias or a filename.

The following program demonstrates the difference.
** assumes a folder c:\test\ exists
CREATE TABLE c:\test\test_1 (i1 i)
CREATE TABLE c:\test\test_2 (i1 i)
USE IN test_1
USE IN test_2

USE c:\test\test_1 ALIAS dummy  IN 0
USE c:\test\test_2 ALIAS test_1 IN 0

** alias specified
** alters the table named test_2.dbf, because its alias is "test_1".
ALTER TABLE test_1 ADD COLUMN i2 i

** fully pathed filename specified
** alters the table named test_1.dbf, while it is in use under alias "dummy".
ALTER TABLE c:\test\test_1 RENAME COLUMN i1 TO i3

-- Willson De Veas

Category VFP Commands
( Topic last updated: 2008.01.03 07:31:45 PM )