Change the definition of a table.
Syntax
<alter table statement> ::=
<alter table action> ::=
<add column definition> ::= ADD [ COLUMN ] <column definition>
<alter column definition> ::=
<alter column action> ::=
<drop column definition> ::= DROP [ COLUMN ] column-name [ RESTRICT ]
<add table constraint definition> ::= ADD <table constraint definition>
<drop table constraint definition> ::= DROP CONSTRAINT [ IF EXISTS ] constraint-name [ RESTRICT ]
Usage
The ALTER TABLE statement is used to change the definition of an existing table in the database, and requires exclusive access to that table.
Notes
§
|
The current database is implicit if a schema name is not specified.
|
§
|
Use the SET DESCRIPTION clause to change the content of the table description attribute.
|
§
|
Use the ADD COLUMN clause to add a new column to the table.
|
§
|
Use the ALTER COLUMN clause to change one of the following column attributes:
|
§
|
Use the DROP COLUMN clause to remove a column from the table. RESTRICT, which is implicit if not explicitly specified, prevents the column from being removed if the column is referenced by other database objects.
|
§
|
Use the ADD table constraint clause to add a PRIMARY KEY, UNIQUE or FOREIGN KEY constraint to the table.
|
§
|
Use the DROP CONSTRAINT clause to remove a table constraint.
|
§
|
The IF EXISTS clause can be specified to avoid an exception when attempting to delete a constraint that does not exist.
|
§
|
RESTRICT, which is implicit if not explicitly specified, prevents a PRIMARY KEY or UNIQUE constraint from being removed if the constraint is referenced by a FOREIGN KEY constraint in another table.
|
§
|
The ALTER TABLE statement requires exclusive access to the table being altered.
|
Examples
1)
|
The following example adds a new column to the students table:
|
ALTER TABLE students
ADD COLUMN picture IMAGE
2)
|
The following example adds a NOT NULL constraint to the lastName column of the students table:
|
ALTER TABLE students
ALTER COLUMN lastName
ADD CONSTRAINT NOT NULL
3)
|
The following example removes the picture column from the students table:
|
ALTER TABLE students
DROP COLUMN picture
4)
|
The following example adds a FOREIGN KEY constraint to the enrolls table:
|
ALTER TABLE enrolls
ADD CONSTRAINT fk_students_studentID FOREIGN KEY ( studentID ) REFERENCES students ( studentID )
5)
|
The following example removes the constraint named fk_students_studentID from the enrolls table:
|
ALTER TABLE enrolls
DROP CONSTRAINT fk_students_studentID
Conformance
SQL:2003 standard
NexusDB extensions
|
-
-
-
-
-
-
|
Core SQL
Feature F381 "Extended schema manipulation"
ADD CONSTRAINT NOT NULL
DROP CONSTRAINT NOT NULL
SET DESCRIPTION
IF EXISTS clause
|
|
