6. DDL Deep Dive — ALTER, DROP & SSMS Table Designer

Getting Set Up in SSMS

Open SSMS and connect to your server. In the Object Explorer on the left, expand Databases and then expand VoidX_Academy. Make sure you can see the Tables folder with dbo.Department and dbo.Student inside it. These are the tables you created in Module 5.

Click New Query at the top of SSMS to open a fresh editor window, then tell SQL Server to use your database:

USE VoidX_Academy;

Press F5. The database name in the top-left dropdown of the SSMS toolbar should now show VoidX_Academy. You are ready.

ALTER TABLE — Adding a New Column

The most common modification to an existing table is adding a column that was not in the original design. This happens constantly in real projects — a requirement was missed, the business changed, or new information needs to be tracked.

The command is ALTER TABLE followed by the table name, then ADD followed by the new column definition. The column definition is written exactly the same way as when you define columns inside CREATE TABLE.

Add a phone number column to the Student table:

ALTER TABLE Student
ADD PhoneNumber VARCHAR(20);

Press F5. Now go to Object Explorer, expand VoidX_Academy, expand Tables, expand dbo.Student, and expand Columns. Right-click the Columns folder and click Refresh. You will see PhoneNumber now listed at the bottom of the column list.

You can add a column with a constraint at the same time. Add a column for the student's nationality with a default value:

ALTER TABLE Student
ADD Nationality VARCHAR(50) DEFAULT 'Ghanaian';

Press F5. Every existing row in the Student table now has the value Ghanaian in the Nationality column automatically — because of the DEFAULT constraint. Any new row inserted without specifying nationality will also receive this value automatically.

ALTER TABLE — Modifying an Existing Column

Sometimes a column already exists but its data type or size needs to change. For example, you created PhoneNumber as VARCHAR(20) but realise international phone numbers with country codes can be longer. Use ALTER COLUMN to change it.

ALTER TABLE Student
ALTER COLUMN PhoneNumber VARCHAR(30);

Press F5. The column now accepts up to 30 characters instead of 20. The existing data in that column is not affected — only the rule about what it can hold going forward changes.

Important: You cannot shrink a column's size if existing data would be cut off. For example, changing VARCHAR(100) to VARCHAR(5) when some rows already contain 20-character values will fail. SQL Server protects your existing data.

ALTER TABLE — Dropping a Column

Dropping a column permanently removes it and all its data from every row in the table. This cannot be undone. Use it carefully.

Remove the PhoneNumber column:

ALTER TABLE Student
DROP COLUMN PhoneNumber;

Press F5. Refresh the Columns folder in Object Explorer — PhoneNumber is gone. Every row in the table no longer has that column, and all the data that was in it is permanently deleted.

ALTER TABLE — Adding a Constraint to an Existing Table

You can add constraints to a table that already exists — even if it already has data in it. This is useful when you realise a rule needs to be enforced that was not set up during CREATE TABLE.

Add a CHECK constraint to enforce that GPA must be between 0 and 4 — but only if the column does not already have one:

ALTER TABLE Student
ADD CONSTRAINT CHK_StudentGPA CHECK (GPA >= 0.0 AND GPA <= 4.0);

Press F5. The constraint is now active. Any future INSERT or UPDATE that violates it will be rejected.

Add a UNIQUE constraint to ensure no two students share the same email:

ALTER TABLE Student
ADD CONSTRAINT UQ_StudentEmail UNIQUE (Email);

Notice the naming convention: CHK_ for check constraints and UQ_ for unique constraints. Naming your constraints clearly is important because it makes error messages meaningful — when a constraint is violated, SQL Server displays the constraint name in the error. A name like CHK_StudentGPA immediately tells you what rule was broken.

ALTER TABLE — Dropping a Constraint

To remove a constraint from a table, you use ALTER TABLE with DROP CONSTRAINT followed by the constraint name. This is why naming constraints matters — you cannot drop a constraint you cannot name.

Drop the check constraint you just added:

ALTER TABLE Student
DROP CONSTRAINT CHK_StudentGPA;

Press F5. The rule is removed. You can now insert any GPA value without it being checked. To see all the constraints currently on a table, expand the table in Object Explorer and look inside the Constraints folder.

DROP TABLE — Removing a Table Permanently

DROP TABLE permanently deletes an entire table — its structure, all its columns, all its data, and all its constraints. There is no undo. The table simply ceases to exist.

First create a temporary table to practise with:

CREATE TABLE TempTest (
  TestID INT PRIMARY KEY,
  TestNote VARCHAR(100)
);

Press F5 and verify it appears in Object Explorer under Tables. Now drop it:

DROP TABLE TempTest;

Press F5. Refresh the Tables folder in Object Explorer — TempTest is gone completely.

There is one critical situation where DROP TABLE will fail: if another table has a FOREIGN KEY that references the table you are trying to drop. SQL Server will refuse to drop a parent table while child tables still depend on it. You must either drop the child table first, or drop the foreign key constraint from the child table before dropping the parent.

For example, you cannot drop the Department table while Student still has a FOREIGN KEY pointing to it. The correct order would be to drop Student first, then drop Department — or drop the foreign key constraint from Student, then drop Department.

DROP DATABASE — Removing an Entire Database

DROP DATABASE permanently removes the entire database — every table, every row of data, every stored procedure, every view, everything. This is the most destructive command in SQL. It cannot be undone without a backup.

First create a test database:

CREATE DATABASE DropTest;

Press F5. Verify it appears in Object Explorer under Databases. Now drop it. Before you can drop a database, you must not be using it yourself and no other connections can be active on it. Switch to master first:

USE master;

DROP DATABASE DropTest;

Press F5. Refresh the Databases folder — DropTest is gone.

Using the SSMS Table Designer — No SQL Required

SSMS has a built-in graphical Table Designer that allows you to create and modify tables by clicking and typing in a visual grid instead of writing SQL. This is especially useful when you want to see all your columns, types, and constraints laid out visually at once.

To create a new table using the Table Designer, go to Object Explorer. Right-click the Tables folder under VoidX_Academy and select New, then Table. A grid opens in the main SSMS window with three columns: Column Name, Data Type, and Allow Nulls.

Type a column name in the first row under Column Name. Then click the Data Type cell for that row and type the data type — or click the dropdown to select from a list. The Allow Nulls checkbox controls whether the column accepts NULL values — unchecking it is the same as adding NOT NULL.

To set a column as the Primary Key, click the grey box to the left of that column's row to select the whole row, then click the small yellow key icon in the Table Designer toolbar at the top. A key icon will appear next to the column name.

To set a Default Value, click the column row and look at the Column Properties panel below the grid. Find the Default Value or Binding field and type your default value there.

When you are done designing the table, press Ctrl + S to save it. A dialog asks you to name the table. Type the name and click OK. The table is created in the database.

Using the SSMS Table Designer — Modifying an Existing Table

To open an existing table in the Table Designer, go to Object Explorer, expand VoidX_Academy, expand Tables, right-click on dbo.Student, and select Design. The Table Designer opens showing all the current columns.

From here you can click into any cell to change a column name or data type. You can add a new row at the bottom of the grid to add a new column. You can right-click a row and select Delete Column to remove it.

When you are finished making changes, press Ctrl + S to save. SSMS will warn you if the changes require the table to be dropped and recreated internally — this happens when a change is too complex to apply directly. Accept the warning only if you are sure there is no data in the table you cannot afford to lose, or if you have a backup.

IDENTITY — Understanding Auto-Generated IDs

You used IDENTITY(1,1) in Module 5. Now that you understand ALTER TABLE, it is important to know that you cannot add or remove an IDENTITY property from a column after the table is created. IDENTITY must be defined when the column is first created.

To check whether IDENTITY is active on a column, expand the table in Object Explorer, expand Columns, and look at the column name. An IDENTITY column will have (PK, int, not null) in its description and a key icon. You can also right-click the table, select Design, click the column, and look at the Column Properties panel — the Identity Specification section shows Yes or No.

If you need to reset the IDENTITY counter — for example after deleting all rows — use the DBCC CHECKIDENT command:

DBCC CHECKIDENT ('Student', RESEED, 0);

This resets the counter to zero, so the next inserted row gets ID 1. This is useful during development and testing when you want clean sequential IDs after clearing a table.