Altering Tables

Introduction

In MS SQL, the ALTER TABLE statement is used to modify an existing table's structure without losing data. Alterations can include adding, deleting, or modifying columns, changing constraints, and updating data types. These changes are essential for evolving database design to accommodate new requirements or improve performance.

In this section, we will cover various scenarios of altering tables, focusing on the syntax and practical examples in customer management.

Common table alterations

1. Adding Columns

You can add new columns to a table using the ALTER TABLE statement. This is useful when new data needs to be tracked or stored.

Syntax:

ALTER TABLE table_name 
ADD column_name data_type [constraint];

Example: Add an mailing_address column to the Customers table.

ALTER TABLE Customers 
ADD mailing_address VARCHAR(255);

Now, each customer can have an mailing_address address stored.

2. Modifying Existing Columns

You may need to change a column's data type, size, or constraints after it's been created.

Syntax:

ALTER TABLE table_name 
ALTER COLUMN column_name new_data_type [constraint];

Example: Increase the length of the Phone column in the Customers table.

ALTER TABLE Customers 
ALTER COLUMN Phone VARCHAR(20);

This change allows longer phone numbers to be stored, ensuring future compatibility.

Note: When altering column data types, ensure the data can be converted to the new type. Otherwise, an error will occur, and data might be lost.

3. Dropping Columns

If certain columns become obsolete or unnecessary, you can remove them from the table.

Syntax:

ALTER TABLE table_name 
DROP COLUMN column_name;

Example: Drop the fax_number column from the Customers table.

ALTER TABLE Customers 
DROP COLUMN fax_number;

This will permanently remove the column and its associated data from the table.

Important: Use caution when dropping columns, as the data will be lost and cannot be recovered unless backed up.

4. Renaming Columns

In MS SQL, renaming columns can be done using the sp_rename system stored procedure, as ALTER TABLE does not directly support renaming.

Syntax:

EXEC sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';

Example: Rename the Address column to street_address in the Customers table.

EXEC sp_rename 'Customers.Address', 'street_address', 'COLUMN';

This change helps to clarify the purpose of the column.

5. Adding Constraints

Constraints enforce rules on the data in a table. You can add constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, or DEFAULT using the ALTER TABLE statement.

Syntax:

ALTER TABLE table_name 
ADD CONSTRAINT constraint_name constraint_type (column_name);

Example: Add a unique constraint to ensure no duplicate emails are stored in the Customers table.

ALTER TABLE Customers 
ADD CONSTRAINT UC_Email UNIQUE (Email);

Example: Add a CHECK constraint to ensure customers are at least 18 years old.

ALTER TABLE Customers 
ADD CONSTRAINT CHK_CustomerAge CHECK (Age >= 18);

6. Dropping Constraints

You may need to remove a constraint if it's no longer applicable.

Syntax:

ALTER TABLE table_name 
DROP CONSTRAINT constraint_name;

Example: Remove the unique constraint on the Email column.

ALTER TABLE Customers 
DROP CONSTRAINT UC_Email;

This allows for flexibility in cases where rules need to be relaxed or adjusted.

7. Changing a Column’s Default Value

The ALTER TABLE command can also change a column's default value, which provides a value if none is supplied during data entry.

Syntax:

ALTER TABLE table_name 
ADD CONSTRAINT constraint_name DEFAULT default_value FOR column_name;

Example: Set the default value of the Country column to 'USA'.

ALTER TABLE Customers 
ADD CONSTRAINT DF_Country DEFAULT 'USA' FOR Country;

Conclusion

Altering tables is a fundamental aspect of database administration, allowing you to adapt your table structures without losing valuable data. Whether adding new columns, modifying existing ones, or enforcing rules with constraints, MS SQL provides flexible tools for managing tables efficiently.

Asked in Interview

• What is the purpose of the ALTER TABLE statement in SQL?
• How can you modify the datatype of an existing column?
• How do you add a foreign key constraint to an existing table?
• Can you change the default value of a column in a table?
• Can you remove a constraint from a table? If so, how?
• Can you drop a primary key constraint from a table?