Narayana Tutorial

Online Java Tutorial Blog

SQL ALTER

SQL ALTER

The structure of the table can be modified by using ALTER TABLE command. ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, then the original table is deleted and the new one is renamed.

  1. ALTER TABLE changing the structure of an existing table
  2. ALTER TABLE add or delete columns
  3. ALTER TABLE create or destroy indexes
  4. ALTER TABLE change the data type of existing columns
  5. ALTER TABLE rename columns or table itself

Note

  1. To use ALTER TABLE, the ALTER, INSERT and CREATE privileges for the table are required
  2. While ALTER TABLE IS executing, the original table is still readable by users of the oracle

Adding new columns

Syntax

ALTER TABLE <TableName> ADD(<NewColumnName 1> <DataType> (<Size>), 
<NewColumnName 2> <DataType> (<Size>), .............. )

Example

ALTER TABLE EMPLOYEE ADD (COUNTRY VARCHAR2(25));

Drop column from table

Syntax

ALTER TABLE <TableName> DROP COLUMN <ColumnName>

Example

ALTER TABLE  EMPLOYEE  DROP COLUMN COUNTRY

Modify existing column in table

Syntax

ALTER TABLE <TableName> MODIFY ( <ColumnName> <NewDataType> (<NewSize>));

Example

ALTER TABLE EMPLOYEE  MODIFY ( COUNTRY VARCHAR2(30));

Note

The following tasks can not be performed when using the ALTER TABLE clause.

  1. Change the name of the column.
  2. Change the name of the table
  3. Decrease the size of a column if the table data exists

 

Narayanaswamy

Hello! I am Narayanaswamy founder and admin of narayanatutorial.com. I have been working in IT industry more than 7 years. NarayanaTutorial is my web technologies blog. My specialties are Java / J2EE, Spring, Hibernate, Struts, Webservices, PHP, Oracle, MySQL, SQLServer, Web Hosting and Website Development.

I am a self learner and passionate about training and writing. I am always trying my best to share my knowledge through my blog.

The Author

Narayanaswamy

Narayanaswamy

Hello! I am Narayanaswamy founder and admin of narayanatutorial.com. I have been working in IT industry more than 7 years. NarayanaTutorial is my web technologies blog. My specialties are Java / J2EE, Spring, Hibernate, Struts, Webservices, PHP, Oracle, MySQL, SQLServer, Web Hosting and Website Development. I am a self learner and passionate about training and writing. I am always trying my best to share my knowledge through my blog.

Leave a Reply

Narayana Tutorial © 2016 Frontier Theme
Show Button
Hide Button