Alter Table in SQL | Alter Table - sql - sql tutorial - learn sql




  • The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
  • The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

Syntax

  • The basic syntax of an ALTER TABLE command to add a New Column in an existing table is as follows.
ALTER TABLE table_name ADD column_name datatype;
  • The basic syntax of an ALTER TABLE command to DROP COLUMN in an existing table is as follows.
ALTER TABLE table_name DROP COLUMN column_name;
  • The basic syntax of an ALTER TABLE command to change the DATA TYPE of a column in a table is as follows.
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
  • The basic syntax of an ALTER TABLE command to add a NOT NULL constraint to a column in a table is as follows.
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
  • The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows.
ALTER TABLE table_name 
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...); 
  • The basic syntax of an ALTER TABLE command to ADD CHECK CONSTRAINTto a table is as follows.
ALTER TABLE table_name 
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);
  • The basic syntax of an ALTER TABLE command to ADD PRIMARY KEYconstraint to a table is as follows.
ALTER TABLE table_name 
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
  • The basic syntax of an ALTER TABLE command to DROP CONSTRAINT from a table is as follows.
ALTER TABLE table_name 
DROP CONSTRAINT MyUniqueConstraint;
  • If you're using MySQL, the code is as follows −
ALTER TABLE table_name 
DROP INDEX MyUniqueConstraint;
  • The basic syntax of an ALTER TABLE command to DROP PRIMARY KEY constraint from a table is as follows.
ALTER TABLE table_name 
DROP CONSTRAINT MyPrimaryKey;
  • If you're using MySQL, the code is as follows −
ALTER TABLE table_name 
DROP PRIMARY KEY;
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Example

  • Consider the WIKITECHY_CUSTOMERS table having the following records
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00
  • Following is the example to ADD a New Column to an existing table −
ALTER TABLE WIKITECHY_CUSTOMERS ADD SEX char(1);
  • Now, the WIKITECHY_CUSTOMERS table is changed and following would be output from the SELECT statement.
ID NAME AGE ADDRESS SALARY SEX
1 Ramesh 32 Ahmedabad 2000.00 NULL
2 Khilan 25 Delhi 1500.00 NULL
3 kaushik 23 Kota 2000.00 NULL
4 Chaitali 25 Mumbai 6500.00 NULL
5 Hardik 27 Bhopal 8500.00 NULL
6 Komal 22 MP 4500.00 NULL
7 Muffy 24 Indore 10000.00 NULL
  • Following is the example to DROP sex column from the existing table.
ALTER TABLE WIKITECHY_CUSTOMERS DROP SEX;
  • Now, the WIKITECHY_CUSTOMERS table is changed and following would be the output from the SELECT statement.
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to Alter Table in SQL | Alter Table

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add wikitechy.com to your ad blocking whitelist or disable your adblocking software.

×