Infogoal Logo
GOAL DIRECTED LEARNING
Master SQL

SQL TUTORIAL HOME

SQL OVERVIEW
SQL SYNTAX
SQL BOOKREVIEWS

SQL BASICS

SQL SELECT
SQL WHERE
SQL INSERT
SQL UPDATE
SQL DELETE

SQL ADMINISTRATION

SQL CREATE DATABASE
SQL DROP DATABASE
SQL CREATE TABLE
SQL ALTER TABLE
SQL DROP TABLE
SQL CREATE INDEX
SQL DROP INDEX
SQL ADD FOREIGN KEY
SQL DROP FOREIGN KEY
SQL CREATE VIEW
SQL DROP VIEW

SQL ADVANCED

SQL CONCAT
SQL SUBSTRING
SQL TRIM
SQL AND & OR
SQL IN
SQL BETWEEN
SQL LIKE
SQL DISTINCT
SQL GROUP BY
SQL AGGREGATE
SQL HAVING
SQL ORDER BY
SQL JOIN
SQL OUTER JOIN

SQL ALTER TABLE Statement

Previous | Next

What is the SQL ALTER TABLE Statement?

The SQL ALTER TABLE statement is the SQL command that makes changes to the definition of an SQL table.

Why Use the SQL ALTER TABLE Statement?

Anytime you want to change the definition of an SQL table. For example, you could:

  • Add a column to a table
  • Change the definition of an existing column in a table
  • Drop a column from a table

How To Use the SQL ALTER TABLE Statement

The SQL ALTER TABLE command is used as follows.

SQL ALTER TABLE Statement Syntax

ALTER TABLE <table_name>
ADD <column_name1> <datatype1> <constraint1>
 
 
ALTER TABLE <table_name>
ALTER COLUMN <column_name1> <datatype1> <constraint1>
 
 
ALTER TABLE <table_name>
DROP COLUMN <column_name1> <datatype1>

SQL ALTER TABLE Statement Example 1 - Add a Column

The following example adds a new column into the person table. Before the operation takes place the following columns exists in the table:

Column NameDatatype Nullability
person_id INT NOT NULL
person_name VARCHAR(20) NOT NULL
gender_code CHAR(1) NULL
line_1_addr VARCHAR(50) NULL
line_2_addr VARCHAR(50) NULL
apartment_nbr VARCHAR(50) NULL
city_name VARCHAR(50) NOT NULL
state_code CHAR(2) NOT NULL
zip_code CHAR(9) NOT NULL

This ALTER TABLE Statement is executed:

ALTER TABLE PERSON
ADD marital_status_code CHAR(1) NULL

Results from the execution of the SQL ALTER TABLE statement are:

Column NameDatatype Nullability
person_id INT NOT NULL
person_name VARCHAR(20) NOT NULL
gender_code CHAR(1) NULL
line_1_addr VARCHAR(50) NULL
line_2_addr VARCHAR(50) NULL
apartment_nbr VARCHAR(50) NULL
city_name VARCHAR(50) NOT NULL
state_code CHAR(2) NOT NULL
zip_code CHAR(9) NOT NULL
marital_status_codeCHAR(1) NULL

SQL ALTER TABLE Statement Example 2 - Alter a Column Datatype

The following example changes the datatype of an existing column in the person table. Before the operation takes place the following columns exists in the table:

Column NameDatatype Nullability
person_id INT NOT NULL
person_name VARCHAR(20) NOT NULL
gender_code CHAR(1) NULL
line_1_addr VARCHAR(50) NULL
line_2_addr VARCHAR(50) NULL
apartment_nbr VARCHAR(50) NULL
city_name VARCHAR(50) NOT NULL
state_code CHAR(2) NOT NULL
zip_code CHAR(9) NOT NULL

This ALTER TABLE Statement is executed:

ALTER TABLE PERSON
ALTER COLUMN person_name VARCHAR(50) NOT NULL

Results from the execution of the SQL ALTER TABLE statement are:

Column NameDatatype Nullability
person_id INT NOT NULL
person_name VARCHAR(50)NOT NULL
gender_code CHAR(1) NULL
line_1_addr VARCHAR(50) NULL
line_2_addr VARCHAR(50) NULL
apartment_nbr VARCHAR(50) NULL
city_name VARCHAR(50) NOT NULL
state_code CHAR(2) NOT NULL
zip_code CHAR(9) NOT NULL
marital_status_codeCHAR(1) NULL

SQL ALTER TABLE Statement Example 3 - Drop a column

The following example removes an existing column in the person table. Before the operation takes place the following columns exists in the table:

Column NameDatatype Nullability
person_id INT NOT NULL
person_name VARCHAR(50) NOT NULL
gender_code CHAR(1) NULL
line_1_addr VARCHAR(50) NULL
line_2_addr VARCHAR(50) NULL
apartment_nbr VARCHAR(50) NULL
city_name VARCHAR(50) NOT NULL
state_code CHAR(2) NOT NULL
zip_code CHAR(9) NOT NULL

This ALTER TABLE Statement is executed:

ALTER TABLE dbo.PERSON
DROP COLUMN apartment_nbr

Results from the execution of the SQL ALTER TABLE statement are:

Column NameDatatype Nullability
person_id INT NOT NULL
person_name VARCHAR(50)NOT NULL
gender_code CHAR(1) NULL
line_1_addr VARCHAR(50) NULL
line_2_addr VARCHAR(50) NULL
city_name VARCHAR(50) NOT NULL
state_code CHAR(2) NOT NULL
zip_code CHAR(9) NOT NULL

Previous | Next


Advertisements

Advertisements:


Infogoal.com is organized to help you gain mastery.
Examples may be simplified to facilitate learning.
Content is reviewed for errors but is not warranted to be 100% correct.
In order to use this site, you must read and agree to the terms of use, privacy policy and cookie policy.
Copyright 2006-2020 by Infogoal, LLC. All Rights Reserved.

Infogoal Logo