SQL Create Table Statement

Structured Query Language (SQL) is the standard language used for managing and manipulating relational databases. One of the fundamental operations that you will perform when working with SQL is creating tables to store data. In this article, we will explore the SQL CREATE TABLE statement, which is essential for defining the structure of a database by setting up tables.

SQL CREATE TABLE Syntax

The basic syntax for creating a table in SQL involves using the CREATE TABLE statement followed by the table name and its columns. The general structure looks as follows:

CREATE TABLE table_name (
    column_name1 data_type constraint,
    column_name2 data_type constraint,
    ...
);

Each column will have a name, a data type (such as INT, VARCHAR, DATE, etc.), and optional constraints that define rules for the data in that column.

SQL CREATE TABLE Example

Create a Table

Let’s create an example table called Employees with basic information about employees. The table will include columns for EmployeeID, Name, Position, and Salary.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Position VARCHAR(50),
    Salary DECIMAL(10,2)
);

Create a Table with Constraints

Next, let’s create a more advanced version of the same table that includes constraints. This will ensure that certain data rules are followed when entries are made into the Employees table.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Position VARCHAR(50),
    Salary DECIMAL(10,2) CHECK (Salary > 0)
);

In this example, the NOT NULL constraint ensures that the Name field must have a value (no empty entries are allowed), and the CHECK constraint ensures that Salary must be greater than zero.

Adding Constraints

Constraints are crucial in maintaining the integrity of your database. Below are several types of constraints that can be applied when creating tables:

NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot have a NULL value. If an attempt is made to insert a NULL value into a NOT NULL column, the database will reject the entry.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    Price DECIMAL(10,2) NOT NULL
);

UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different. This constraint can be used to enforce the uniqueness of a column or a set of columns.

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50) UNIQUE,
    Email VARCHAR(100) UNIQUE
);

PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table. A table can only have one primary key, which can consist of one or multiple columns.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    UserID INT,
    OrderDate DATE
);

FOREIGN KEY Constraint

A FOREIGN KEY constraint is used to link two tables together. It establishes a relationship between the foreign key column in one table and the primary key column in another table.

CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

CHECK Constraint

The CHECK constraint ensures that all values in a column satisfy a specific condition.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Age INT CHECK (Age >= 18)
);

Summary

Understanding the CREATE TABLE statement in SQL is fundamental to effective database management. By defining your table structures and constraints properly, you ensure data integrity, enforce rules and relationships, and set a solid foundation for your database.

FAQ

Q: What is a primary key?

A: A primary key is a unique identifier for each record in a database table.

Q: Can a table have multiple primary keys?

A: No, a table can only have one primary key, but that primary key can be made up of multiple columns (composite key).

Q: What happens if I try to violate a constraint?

A: The database system will reject the operation and return an error message indicating the constraint violation.

Q: What is a foreign key?

A: A foreign key is a field (or collection of fields) in one table that refers to the primary key in another table, establishing a link between the two tables.

Q: What does the NOT NULL constraint do?

A: The NOT NULL constraint ensures that a column cannot contain NULL values; each entry must have a valid data value.

askthedev.com Latest Articles