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