Developing By Me

Project Development and Design

ALTER Statement

    The ALTER statement is used when a table needs to be updated. After the creation of the initial table there may be a need to add, update or remove different fields without rebuilding the whole table. The ALTER statement allows this to happen and it has three options ADD, MODIFY, and DROP.

 

ALTER TABLE ADD

    The ADD syntax is identical to the CREATE TABLE except that it only affects a single field. Like the CREATE TABLE syntax the DBA can include options like NOT NULL and DEFAULT. Depending on the database adding an IDENTITY cannot be done without rebuilding the table. The following syntax will create the additional fields or constraints.

 

SQL Syntax Description SQL Database
ALTER TABLE table1 ADD field10 datatype Adds a field to the table MySQL, SQL Server, Oracle, PostgreSQL
ALTER TABLE table_name ADD PRIMARY KEY (id)  Adds a PRIMARY KEY to the table MySQL, SQL Server, Oracle, PostgreSQL
ALTER TABLE table_name ADD CONSTRAINT index_name PRIMARY KEY (id)  Adds a PRIMARY KEY to the table MySQL, SQL Server, Oracle
ALTER TABLE table1 ADD FOREIGN KEY (table2_id) REFERENCES table2(id) Adds a FOREIGN KEY to the table MySQL, SQL Server, Oracle
ALTER TABLE table1 ADD CONSTRAINT index_name FOREIGN KEY (table2_id) REFERENCES table2(id) Adds a FOREIGN KEY to the table MySQL, SQL Server, Oracle, PostgreSQL
ALTER TABLE table1 ADD UNIQUE(field1) Adds a UNIQUE index to the table MySQL, SQL Server, Oracle
ALTER TABLE table1 ADD CONSTRAINT index_name UNIQUE(field1) Adds a UNIQUE index to the table MySQL, SQL Server, Oracle, PostgreSQL
ALTER TABLE table1 ADD CHECK (id > 0) Adds a CHECK to the field on the table MySQL, SQL Server, Oracle
ALTER TABLE table1 ADD CONSTRAINT index_name CHECK (id > 0 AND city = ‘Some City’) Adds a CHECK to the field on the table MySQL, SQL Server, Oracle, PostgreSQL

 

ALTER TABLE MODIFY

    The MODIFY syntax varies based on which database engine is being used. However the syntax is nearly identical and the action is the same. Like ADD some changes cannot be made through the modify process such as changing an IDENTITY or changing any indexed column. These changes require the removal of the indexing prior to making the change. In some cases it is possible that the table will have to be mirrored to a temp table, then dropped and rebuilt before the required modifications can be made. The following is the syntax script that can be used to MODIFY a COLUMN or field. The following is the standard syntax on modifying fields.

 

SQL Syntax
ALTER TABLE table1 ALTER COLUMN field1 datatype

 

ALER TABLE DROP

    The DROP column option should only be used during database cleanup as a last resort. This is because if the column is referenced in any area there will be an error. This does not affect the database unless an insert or update is referencing the field however it has a larger impact on a system that is reading from the database. For the most part DROP is used when the DBA is designing the table structure since it can change several times prior to deployment. The last thing to know is that when a column is dropped all data is lost if there is not a backup made prior to the action. The following is the standard syntax for removing fields.

 

SQL Syntax Description SQL Database
ALTER TABLE table_name DROP field1 Removes a field from the table MySQL, Oracle, PostgreSQL
ALTER TABLE table_name DROP COLUMN field1 Removes a field from the table SQL Server
ALTER TABLE table_name DROP PRIMARY KEY  Removes the PRIMARY KEY from the table MySQL
ALTER TABLE table_name DROP FOREIGN KEY index_name Removes a FOREIGN KEY from the table MySQL
ALTER TABLE table1 DROP INDEX index_name Removes an INDEX from the table MySQL
ALTER TABLE table_name DROP CONSTRAINT index_name Removes a CONSTRAINT from the table SQL Server, Oracle, PostgreSQL
ALTER TABLE table_name DROP CHECK index_name Removes a CHECK from the table MySQL

 

%d bloggers like this: