CREATE TABLE in SQL

The CREATE TABLE statement is used to define a new table in a relational database. A table provides the structure in which related records are stored. When creating a table, we decide what information the table will contain, what type of value each column can store, which column identifies a record, and what rules should be applied to the data.

For example, a college application may need to store student information such as student ID, name, email address, course, admission date, and account status. Instead of putting all of this information into an unstructured collection of values, SQL allows us to define a table where every field has a clear purpose and data type.

A well-designed table is more than a collection of columns. Its structure should reflect the data being stored and the relationships that exist between different parts of the application. This is why CREATE TABLE is an important foundation for learning SQL and relational database design.

What Does CREATE TABLE Do?

When a CREATE TABLE statement is successfully executed, the database creates the table definition. At this stage, the table may contain no records, but the database knows which columns exist, what values they can contain, and which constraints apply to them.

For example, the following statement defines a simple student table:

CREATE TABLE Student (
    StudentID INT,
    StudentName VARCHAR(100),
    Course VARCHAR(50)
);

The statement creates three columns: StudentID, StudentName, and Course. No student record is inserted by this command. It only establishes the structure in which student records can later be stored.

Basic Syntax of CREATE TABLE

CREATE TABLE table_name (
    column_name data_type,
    column_name data_type,
    column_name data_type
);

Each column definition normally contains a column name followed by its data type. Constraints can then be added when the database requires additional rules.

Part	Meaning
CREATE TABLE	SQL statement used to define a new table.
table_name	Name assigned to the table.
column_name	Name of an individual field.
data_type	Defines the kind of value that the column stores.
constraint	Optional rule that controls the values allowed in the column.

Understanding a Table Through a Simple Example

Suppose we want to store students in a college database. Before writing SQL, we can identify the information that is actually required:

A unique identifier for every student.
The student's name.
An email address.
The course in which the student is enrolled.
The student's admission date.

These requirements can be converted into the following table structure:

Column	Purpose	Possible Data Type
StudentID	Identifies the student	INT
StudentName	Stores the student's name	VARCHAR(100)
Email	Stores the student's email	VARCHAR(150)
Course	Stores the course name	VARCHAR(100)
AdmissionDate	Stores the admission date	DATE

This planning step is useful because it prevents us from choosing columns randomly. The table structure should be based on the information the application actually needs.

Choosing the Correct Data Type

A column's data type tells the database what kind of value should be stored there. Choosing a suitable type can make the table clearer and can also prevent inappropriate values from being stored.

Data Type	Typical Use
INT	Whole-number values such as IDs or quantities.
VARCHAR(n)	Variable-length text such as names, email addresses, and titles.
CHAR(n)	Short fixed-length values where a fixed size is appropriate.
DATE	Calendar dates.
DECIMAL(p,s)	Exact numeric values such as prices and financial amounts.
BOOLEAN	Logical true/false values where supported by the DBMS.

The exact data types and their behavior can vary between database systems such as MySQL, PostgreSQL, SQL Server, and Oracle. Therefore, when writing production SQL, the documentation of the selected DBMS should be consulted.

Creating a Table with a Primary Key

A table normally needs a way to distinguish one record from another. A PRIMARY KEY provides that identity.

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100),
    Course VARCHAR(100)
);

Here, StudentID is the primary key. Each student must therefore have a distinct StudentID, and the primary-key value cannot be NULL.

For example, these records could be stored:

StudentID    StudentName       Course
101          Ankit Sharma      B.Tech
102          Neha Verma        BCA
103          Ravi Kumar        MCA

The database can use StudentID to distinguish the three records even if two students happen to have similar names.

Adding NOT NULL

Some fields are required for every record. The NOT NULL constraint can be used when a column must receive a value.

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL,
    Course VARCHAR(100)
);

In this design, StudentName is mandatory. An attempt to insert a row without an appropriate StudentName will violate the constraint.

Using UNIQUE for Values Such as Email

A value may not be the primary identifier of a record but can still be required to remain unique. For example, an application may require each registered student's email address to be different.

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL,
    Email VARCHAR(150) UNIQUE
);

The UNIQUE constraint tells the database to reject duplicate values according to the rules of the particular DBMS.

Using DEFAULT Values

A DEFAULT value can be useful when a field has a common value that should be used when the application does not provide one.

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL,
    Status VARCHAR(20) DEFAULT 'Active'
);

If an INSERT statement does not supply a value for Status, the database can use the defined default.

This is useful for fields such as an account status, creation flag, or other application-defined value where an initial state is known.

Using CHECK to Validate Values

The CHECK constraint expresses a condition that values should satisfy.

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL,
    Age INT CHECK (Age >= 18)
);

The condition in this example states that the Age value must be at least 18. CHECK behavior and enforcement details can differ between database systems and versions, so the target DBMS should always be considered.

Creating a Table with Several Constraints

A practical table often uses several constraints together. For example:

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL,
    Email VARCHAR(150) UNIQUE,
    Age INT CHECK (Age >= 18),
    Course VARCHAR(100),
    AdmissionDate DATE,
    Status VARCHAR(20) DEFAULT 'Active'
);

This design gives each column a specific responsibility:

StudentID identifies the record.
StudentName is required.
Email is intended to be unique.
Age has a validation rule.
Course stores the student's course.
AdmissionDate stores the date of admission.
Status receives a default value when one is not supplied.

The important point is that each constraint has a reason. Constraints should be introduced to represent actual data requirements rather than simply adding rules because they are available in SQL.

Creating Related Tables with a Foreign Key

When information is divided into multiple tables, those tables often need relationships. Consider a college database containing departments and employees.

CREATE TABLE Department (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100) NOT NULL
);

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100) NOT NULL,
    DepartmentID INT,
    FOREIGN KEY (DepartmentID)
        REFERENCES Department(DepartmentID)
);

DepartmentID in Employee refers to DepartmentID in Department. The foreign key allows the database to enforce referential integrity according to the configured relationship rules.

This is preferable to storing the department name repeatedly in every employee record because the department itself has an independent identity and can be maintained in its own table.

Composite Primary Key

Sometimes a single column is not the natural identifier for a record. This is common in relationship tables.

Suppose a student can enroll in multiple courses and a course can contain multiple students. An enrollment table can represent the relationship:

CREATE TABLE Enrollment (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (StudentID, CourseID)
);

Here, the combination of StudentID and CourseID identifies an enrollment. The same student can appear for different courses, and the same course can contain different students, but the same student-course combination should not occur twice if the table represents one enrollment per course.

CREATE TABLE with a Complete College Example

The following example demonstrates how several tables can be designed together rather than treating CREATE TABLE as an isolated command.

Department Table

CREATE TABLE Department (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100) NOT NULL UNIQUE
);

Student Table

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL,
    Email VARCHAR(150) UNIQUE,
    DepartmentID INT,
    AdmissionDate DATE,
    FOREIGN KEY (DepartmentID)
        REFERENCES Department(DepartmentID)
);

Course Table

CREATE TABLE Course (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    Credits INT CHECK (Credits > 0)
);

Enrollment Table

CREATE TABLE Enrollment (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID)
        REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID)
        REFERENCES Course(CourseID)
);

This example shows an important database-design principle: tables represent different entities or relationships, while keys connect them. A student belongs to a department, a course exists independently, and Enrollment records the relationship between students and courses.

CREATE TABLE Followed by INSERT

Creating a table does not automatically insert records. After the structure has been created, data can be added using INSERT.

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL,
    Course VARCHAR(100)
);

INSERT INTO Student
    (StudentID, StudentName, Course)
VALUES
    (101, 'Ankit Sharma', 'B.Tech'),
    (102, 'Neha Verma', 'BCA');

The first statement defines the structure. The second statement adds actual student records. Keeping these two operations conceptually separate makes it easier to understand how SQL databases are built and populated.

Creating a Table Only When It Does Not Already Exist

Some database systems support an IF NOT EXISTS form:

CREATE TABLE IF NOT EXISTS Student (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL
);

The purpose is to avoid a table-exists error when the named table is already present. However, the exact syntax and behavior should be checked for the DBMS being used.

Inspecting the Table Structure

After creating a table, developers often need to verify its structure. The command depends on the database system.

MySQL Example

DESCRIBE Student;

This displays information such as column names, data types, NULL settings, keys, and default values in MySQL.

Another commonly used MySQL command is:

SHOW CREATE TABLE Student;

This can be useful when you want to see the CREATE TABLE definition maintained by MySQL.

Common Table Design Decisions

Creating a table is not only a syntax exercise. Several design decisions should be made before the final SQL is written.

Question	Design Decision
How will each record be identified?	Choose an appropriate primary key.
Which fields are mandatory?	Consider NOT NULL.
Which values must be unique?	Consider UNIQUE constraints.
What kind of values are stored?	Select suitable data types.
Does a value have an acceptable range?	Consider a CHECK constraint where supported.
Does the table depend on another entity?	Consider a FOREIGN KEY relationship.
Does a field have a sensible initial value?	Consider a DEFAULT.

Common Errors When Creating Tables

Beginners often encounter errors because the table definition does not match the database requirements. Some common examples are:

Using a data type that does not exist in the selected DBMS.
Giving two columns the same name when the DBMS does not allow it.
Referencing a table or column that has not been created correctly.
Defining a foreign key with incompatible referenced columns.
Forgetting a comma between column definitions.
Using a reserved SQL word as an identifier without appropriate handling.
Adding constraints that conflict with the actual application requirements.

For example, this statement contains a syntax problem because a comma is missing:

CREATE TABLE Student (
    StudentID INT PRIMARY KEY
    StudentName VARCHAR(100)
);

The corrected statement is:

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100)
);

CREATE TABLE vs INSERT INTO

CREATE TABLE and INSERT INTO perform different jobs and should not be confused.

CREATE TABLE	INSERT INTO
Creates the table structure.	Adds records to an existing table.
Defines columns and constraints.	Provides values for those columns.
Normally executed when designing the database.	Executed when adding data.

For example:

CREATE TABLE Course (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100)
);

INSERT INTO Course
    (CourseID, CourseName)
VALUES
    (1, 'Database Management System');

CREATE TABLE vs ALTER TABLE

These commands are also different.

CREATE TABLE	ALTER TABLE
Creates a new table.	Changes an existing table.
Defines the initial structure.	Can add, modify, or remove parts of an existing structure.

For example, if the Student table already exists and a phone-number column is later required, ALTER TABLE can be used instead of creating the entire table again.

ALTER TABLE Student
ADD Phone VARCHAR(20);

Important Points to Remember

CREATE TABLE defines a new table structure.
Every column should have an appropriate data type.
A primary key is normally used to identify records.
NOT NULL can make required fields mandatory.
UNIQUE can protect values that must not be duplicated.
FOREIGN KEY can connect related tables.
CHECK can express value-validation rules where supported.
DEFAULT can provide a value when one is not supplied.
A composite primary key can identify records using multiple columns.
The exact syntax and supported features depend on the DBMS.

Conclusion

The CREATE TABLE statement is the starting point for defining relational data structures in SQL. Its job is not simply to create rows and columns; it establishes the rules under which the data will be stored. Choosing meaningful columns, appropriate data types, keys, and constraints makes the resulting database easier to understand and maintain.

A good way to learn CREATE TABLE is to begin with a real requirement, identify the entities and their attributes, decide how records will be identified, and then translate those decisions into SQL. Once the table structure is ready, commands such as INSERT, SELECT, UPDATE, DELETE, and ALTER TABLE can be used to work with it.

The college example in this tutorial also demonstrates why relational databases normally use multiple related tables rather than placing every piece of information into one large table. Proper table design provides the foundation for relationships, normalization, data integrity, and more advanced SQL operations.

Quick Revision

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL,
    Email VARCHAR(150) UNIQUE,
    Course VARCHAR(100),
    AdmissionDate DATE,
    Status VARCHAR(20) DEFAULT 'Active'
);

The statement above demonstrates the central ideas of CREATE TABLE: defining columns, choosing data types, identifying a record, requiring important values, preventing unwanted duplicates, and providing a default value.

← Previous: Create Database Next: ALTER TABLE →