Change the definition of a table.

 

Syntax

 

<alter table statement> ::=

 

 

 

ALTER TABLE [ schema-name. ] table-name <alter table action>

 

<alter table action> ::=

 

<add column definition> ::= ADD [ COLUMN ] <column definition>

 

<alter column definition> ::=

 

 

 

ALTER [ COLUMN ] column-name <alter column action>

 

<alter column action> ::=

 

|

|

|

|

|

 

SET <default clause>

DROP DEFAULT

ADD CONSTRAINT NOT NULL

DROP CONSTRAINT NOT NULL

SET DESCRIPTION <character string literal>

CAST [AS] <data type> [ WITH BESTFIT ]

 

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



 


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.




 

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