LuisLlamas Logo
creacion-modificacion-tablas

Creating and Modifying Tables in SQL Server

  • 5 min
  • Beginner

Once we have our database created, it’s time to define its internal structure. In the relational model, this means creating tables.

But furthermore, in the real world, requirements change. What is a 50-character text field today might need to be 200 characters tomorrow. That’s why, as important as knowing how to create tables is knowing how to modify them, without losing the data they contain 😊.

In this article, we will learn how to structure our data and evolve it using T-SQL.

The CREATE TABLE Statement

To create a new table we will use the CREATE TABLE instruction. For this, we must specify three things:

  1. The name of the table
  2. The names of its columns
  3. The data types and properties of each column

For example, suppose we want to store information about the employees of our company.

USE MiEmpresa; -- Important: make sure you are in the correct database
GO

CREATE TABLE Empleados (
    EmpleadoID INT PRIMARY KEY,
    Nombre NVARCHAR(50) NOT NULL,
    Apellido NVARCHAR(50) NOT NULL,
    FechaNacimiento DATE,
    Email VARCHAR(100)
);
GO
Copied!

In this simple example, we already see several key concepts:

  • Data types: INT, NVARCHAR, DATE. (We will delve deeper into them in the next article).
  • Nullability (NULL / NOT NULL): We define if the field is mandatory. If we set NOT NULL, SQL Server will not let us save the record if that data is missing.
  • PRIMARY KEY Constraint: We mark EmpleadoID as the unique identifier.

The IDENTITY Property

In the previous example, we would have to manually insert each employee’s ID (1, 2, 3…). This is tedious and error-prone.

In SQL Server, the standard way to create a numeric primary key that generates itself is to use the IDENTITY (auto-increment) property.

CREATE TABLE Pedidos (
    PedidoID INT IDENTITY(1,1) PRIMARY KEY,
    FechaPedido DATETIME DEFAULT GETDATE(),
    ClienteID INT NOT NULL
);
Copied!

Breaking down IDENTITY(seed, increment):

  • Seed (1): The first record will start at 1.
  • Increment (1): Each new record will add 1 to the previous one (1, 2, 3…).

If you try to manually insert a value into an IDENTITY column, SQL Server will give you an error. The system takes care of managing that sequence for you, so it doesn’t let you set it manually.

Constraints in Table Creation

Constraints are rules applied to columns or the table to ensure data integrity.

Some of the most common constraints are:

  • PRIMARY KEY: Uniquely identifies each row in the table.
  • FOREIGN KEY: Establishes a relationship with another table.
  • UNIQUE: Ensures all values in the column are unique.
  • CHECK: Applies a condition that must be met for the data.
  • DEFAULT: Sets a default value for the column if no value is provided.
  • NOT NULL: Prevents the column from containing null values.

For example

CREATE TABLE Pedidos (
    PedidoID INT PRIMARY KEY,
    ClienteID INT NOT NULL,
    FechaPedido DATE DEFAULT GETDATE(),
    Total DECIMAL(10, 2) CHECK (Total >= 0),
    FOREIGN KEY (ClienteID) REFERENCES Clientes(ClienteID)
);
Copied!

In this example:

  • PedidoID is the primary key.
  • ClienteID is a foreign key referencing the Clientes table.
  • FechaPedido has a default value of the current date (GETDATE()).
  • Total has a CHECK constraint ensuring the value is greater than or equal to 0.

Modifying Tables ALTER TABLE

Software design is a living process. It is very likely that, after creating the Empleados table, you realize you forgot to save the phone number or that the Email field is too short.

For this, we use the ALTER TABLE instruction.

Adding New Columns ADD

We can add columns to a table that already has data.

ALTER TABLE Empleados
ADD Telefono VARCHAR(20) NULL;
Copied!

Be careful with NOT NULL: If you add a NOT NULL column to a table that already has rows, SQL Server will give you an error (because the old rows would not have a value for that new column). Solution: Add it as NULL or specify a default value (DEFAULT).

Modifying Existing Columns ALTER COLUMN

Imagine Nombre NVARCHAR(50) is too small and we need 100 characters.

ALTER TABLE Empleados
ALTER COLUMN Nombre NVARCHAR(100) NOT NULL;
Copied!

We can also use this to change the data type (for example, from INT to BIGINT), but be careful: if the existing data cannot be converted to the new type, the operation will fail.

Deleting Columns DROP COLUMN

If a column is no longer needed, we can physically delete it.

ALTER TABLE Empleados
DROP COLUMN FechaNacimiento;
Copied!

This operation permanently deletes the data in that column. There is no “Ctrl+Z” (unless you use transactions).

Deleting Tables DROP TABLE

If we want to delete a complete table (its structure and all its data), we use DROP TABLE.

DROP TABLE Empleados;
Copied!

As with databases, it’s good practice to check if it exists to avoid errors in our deployment scripts:

DROP TABLE IF EXISTS Empleados;
Copied!

DROP vs TRUNCATE?

Sometimes we only want to delete the data but leave the table empty and ready to use again. For that, do not use DROP.

Use TRUNCATE TABLE Empleados. It is much faster and keeps the structure.

Naming Best Practices

Maintain a consistent naming convention.

  • Use singular or plural names, but don’t mix them (Cliente vs Clientes). I prefer singular for tables and columns.
  • Use PascalCase (FechaNacimiento) instead of underscores (fecha_nacimiento), it’s more standard in the Microsoft ecosystem.
  • Avoid spaces and strange characters to not have to use brackets [] constantly.

On this page