ALTER TABLE statement
Wed, 03/04/2009 - 20:02 | 
hdanzl
Change the definition of a table.
Syntax
|
ALTER TABLE [ schema-name. ] table-name <alter table action> |
|
| | | | | |
SET DESCRIPTION <character string literal> |
|
ALTER [ COLUMN ] column-name <alter column action> |
|
| | | | | | | |
SET <default clause> DROP DEFAULT ADD CONSTRAINT NOT NULL DROP CONSTRAINT NOT NULL CAST [ AS ] <DataType> [IGNORERESTRICT [WITH BESTFIT]] RENAME [ TO ] <SqlName> [IGNORERESTRICT] SET DESCRIPTION <character string literal> |
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: |
| ♦ | DEFAULT value |
| ♦ | NOT NULL constraint |
| ♦ | DESCRIPTION |
| ♦ | 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. |
| ♦ | Use the CAST clause to change the type of a column. The types need to be comparable. IGNORERESTRICT allows to cast without checking on other constraints. WITH BESTFIT sets the loss action to best fit and proceeds in case of data conversion errors (default behaviour is to fail). |
| ♦ | Use the RENAME clause to change the name of a column. The new name needs to conform with the rules of identifiers. IGNORERESTRICT allows to rename without checking on other constraints. |
| ♦ | 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 |
Tags:
Questions? Problems?
- Interested to buy but something put you off?
- Can't find what you're looking for?
- Have a suggestion?
Let us know. If you're having trouble contacting us, please check here.
Site Updates
- Jun 3 2013 - 1:28pm
- Jun 3 2013 - 12:48pm
- Jun 3 2013 - 12:43pm
- Jun 3 2013 - 12:35pm
- May 2 2013 - 12:06am