How to Create a Table in Azure SQL Database

Prerequisites

• An Azure account with an active subscription.
• An Azure SQL Database instance set up.
• Access to the Azure Portal or SQL Server Management Studio (SSMS) to execute SQL queries.

Connect to Your Azure SQL Database

To begin, you need to connect to your Azure SQL Database. You can use SQL Server Management Studio (SSMS), Azure Data Studio, or any other SQL client that supports Azure SQL Database connections.

-- Using SQL Server Management Studio (SSMS)
-- Replace <server_name>, <database_name>, <username>, and <password> with your details

Server name: <server_name>.database.windows.net
Database name: <database_name>
Authentication: SQL Server Authentication
Login: <username>
Password: <password>

Basic Table Creation

Once connected, you can start by creating a simple table. This table will store employee information such as ‘employee ID’, ‘name’, and ‘department’.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100),
    Department NVARCHAR(50)
);

Expected Output:

If the table is created successfully, you will see a message like this:

Commands completed successfully.

Creating a Table with Constraints

Adding constraints to your table helps enforce data integrity. For example, you can ensure that certain fields are not null, or you can add unique constraints.

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName NVARCHAR(100) NOT NULL UNIQUE,
    CreatedDate DATETIME DEFAULT GETDATE()
);

Expected Output:

Commands completed successfully.

In this example, the DepartmentName column cannot be null and must be unique. The CreatedDate column will automatically default to the current date and time when a new record is inserted.

Creating a Table with a Foreign Key

Foreign keys are used to establish relationships between tables. In this example, we’ll create a table that references the Departments table.

CREATE TABLE EmployeeDetails (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100) NOT NULL,
    DepartmentID INT,
    CONSTRAINT FK_Department FOREIGN KEY (DepartmentID)
        REFERENCES Departments(DepartmentID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Expected Output:

Commands completed successfully.

The ON DELETE CASCADE and ON UPDATE CASCADE options ensure that changes in the Departments table will cascade to the EmployeeDetails table, maintaining referential integrity.

Inserting Data into the Tables

With your tables created, you can insert some data to see how they work together.

-- Insert data into Departments table
INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES (1, 'HR'), (2, 'Engineering');

-- Insert data into EmployeeDetails table
INSERT INTO EmployeeDetails (EmployeeID, EmployeeName, DepartmentID)
VALUES (101, 'John Doe', 1), (102, 'Jane Smith', 2);

Expected Output:

(2 rows affected)
(2 rows affected)

Querying Data

You can now query the data to see the relationships between the Departments and EmployeeDetails tables.

SELECT 
    e.EmployeeID,
    e.EmployeeName,
    d.DepartmentName
FROM 
    EmployeeDetails e
INNER JOIN 
    Departments d ON e.DepartmentID = d.DepartmentID;

Expected Output:

Altering a Table

Modifying a table after creation is a common requirement. You might need to add a new column, change a column’s data type, or set default values.

-- Add a new column to the EmployeeDetails table
ALTER TABLE EmployeeDetails
ADD Email NVARCHAR(255);

-- Set a default value for the new column
ALTER TABLE EmployeeDetails
ADD CONSTRAINT DF_Email DEFAULT 'example@example.com' FOR Email;

Expected Output:

Commands completed successfully.

Dropping a Table

If you no longer need a table, you can drop it using the DROP TABLE statement. Be cautious with this operation as it permanently deletes the table and its data.

DROP TABLE EmployeeDetails;

Expected Output:

Commands completed successfully.

Conclusion

Creating and managing tables in Azure SQL Database is a straightforward process, but understanding the various options and constraints you can apply will help you design efficient and scalable databases. By using foreign keys, constraints, and appropriate data types, you ensure data integrity and optimize performance.

Additional articles you may enjoy:

• How to Use SQL Query Templates
• How to Subtract in SQL Server