In this article, we will learn how to create a table in SQL Server. Creating a table is one of the fundamental tasks in database management. We will cover the following topics:

The CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in SQL Server. The syntax for creating a table is as follows:

CREATE TABLE [IF NOT EXISTS] table_name
(
    column1 datatype(length) [constraint],
    column2 datatype(length) [constraint],
    ...
    table_constraint
)

In the above syntax:

• table_name is the name of the table you want to create. It must be unique within the database.
• IF NOT EXISTS is an optional clause that allows you to create the table only if it does not already exist.
• column1, column2, ... are the names of the columns in the table.
• datatype(length) specifies the data type and optional length of each column.
• constraint is an optional constraint that can be applied to a column or the table as a whole.
• table_constraint is an optional constraint that applies to the entire table.

Column Constraints

SQL Server supports various column constraints that can be applied to individual columns. Some commonly used column constraints are:

• PRIMARY KEY: This constraint ensures that each row in the table has a unique identifier. A table can have only one primary key.
• FOREIGN KEY: This constraint establishes a relationship between two tables. It ensures that values in a column of one table match the values in the primary key column of another table.
• NOT NULL: This constraint ensures that a column cannot contain NULL values.
• UNIQUE: This constraint ensures that each value in a column is unique.
• CHECK: This constraint allows you to specify a condition that must be met for the values in a column.

Example of CREATE TABLE Statement

Let’s create a simple table called Customers with a few columns:

CREATE TABLE Customers
(
    CustomerID int PRIMARY KEY,
    FirstName varchar(50) NOT NULL,
    LastName varchar(50) NOT NULL,
    Email varchar(100) UNIQUE,
    Age int CHECK (Age >= 18)
)

In the above example, we have created a table with four columns:

• CustomerID is the primary key column.
• FirstName and LastName are columns that cannot contain NULL values.
• Email is a column with a unique constraint, ensuring that each email address is unique.
• Age is a column with a check constraint, ensuring that the age is greater than or equal to 18.

Summary

In this article, we have learned how to create a table in SQL Server. We covered the syntax of the CREATE TABLE statement and the various column constraints that can be applied. Creating tables is an essential skill for managing databases, and understanding the concepts discussed here will help you get started with SQL Server.

Stay tuned for more articles on SQL Server and database management!