Microsoft SQL Server

Chapter 5 - DDL (Data Definition Language)

ALTER TABLE

The ALTER TABLE statement allows you to make changes to the structure of an existing table. This can include adding new columns, modifying existing columns, or dropping columns. It can also be used to add or remove constraints. Below are examples and explanations for common modifications.

Key Concepts

1. Adding a New Column

  • Use ALTER TABLE ... ADD to add a new column to an existing table.

2. Modifying an Existing Column

  • Use ALTER TABLE ... ALTER COLUMN to change the data type or properties of an existing column.

3. Dropping a Column

  • Use ALTER TABLE ... DROP COLUMN to remove a column from the table.

4. Adding Constraints

  • You can add new constraints like PRIMARY KEY, FOREIGN KEY, CHECK, or UNIQUE.

5. Dropping Constraints

  • Use ALTER TABLE ... DROP CONSTRAINT to remove constraints from the table.

Code Samples

1. Adding a New Column

  • Add a LastUpdated column to the Store table.
ALTER TABLE Store
ADD LastUpdated DATETIME;
  • Add a Description column to the Products table.
ALTER TABLE Products
ADD Description VARCHAR(255);

2. Modifying an Existing Column

  • Change the Location column in the Store table to accept a maximum of 200 characters.
ALTER TABLE Store
ALTER COLUMN Location VARCHAR(200);
  • Modify the Price column in the Products table to allow up to 15 digits with 4 decimal places.
ALTER TABLE Products
ALTER COLUMN Price DECIMAL(15, 4);

3. Dropping a Column

  • Remove the PhoneNumber column from the Customer table.
ALTER TABLE Customer
DROP COLUMN PhoneNumber;
  • Remove the Description column from the Products table.
ALTER TABLE Products
DROP COLUMN Description;

4. Adding Constraints

  • Add a CHECK constraint to the StockQuantity column in the Products table to ensure it is always positive.
ALTER TABLE Products
ADD CONSTRAINT chk_StockQuantity
CHECK (StockQuantity >= 0);
  • Add a FOREIGN KEY constraint to the Sales table to reference the StoreID in the Store table.
ALTER TABLE Sales
ADD CONSTRAINT fk_Store
FOREIGN KEY (StoreID)REFERENCES Store(StoreID);

5. Dropping Constraints

  • Drop the CHECK constraint from the Products table.
ALTER TABLE Products
DROP CONSTRAINT chk_StockQuantity;
  • Drop the FOREIGN KEY constraint from the Sales table.
ALTER TABLE Sales
DROP CONSTRAINT fk_Store;

Combined Code Sample

Here is all the code combined for easier reference:

-- Adding a New Column
ALTER TABLE Store
ADD LastUpdated DATETIME;

ALTER TABLE Products
ADD Description VARCHAR(255);

-- Modifying an Existing Column

ALTER TABLE Store
ALTER COLUMN Location VARCHAR(200);

ALTER TABLE Products
ALTER COLUMN Price DECIMAL(15, 4);

-- Dropping a Column

ALTER TABLE Customer
DROP COLUMN PhoneNumber;

ALTER TABLE Products
DROP COLUMN Description;

-- Adding Constraints

ALTER TABLE Products
ADD CONSTRAINT chk_StockQuantity
CHECK (StockQuantity >= 0);

ALTER TABLE Sales
ADD CONSTRAINT fk_Store
FOREIGN KEY (StoreID)
REFERENCES Store(StoreID);

-- Dropping Constraints

ALTER TABLE Products
DROP CONSTRAINT chk_StockQuantity;

ALTER TABLE Sales
DROP CONSTRAINT fk_Store;
Tansy MSSQL Course | CREATE TABLE | Chapter 5 | Lesson 1 - Video Thumbnail
Previous Next
Comments(0 comments)

Comments Not Found

Most visited
Microsoft SQL Server SELECT * 142 views
Microsoft SQL Server WHERE Clause 74 views
Microsoft SQL Server TOP FETCH OFFSET 60 views
Microsoft SQL Server DDL ASSIGNMENT1 - Store Management System 68 views
Microsoft SQL Server Comparison Operators 65 views
Microsoft SQL Server COUNT, SUM, AVG, MIN, MAX Functions 62 views