WikiGalaxy

Personalize

Creating Tables in SQL

Introduction to SQL CREATE TABLE

Overview:

The SQL CREATE TABLE statement is used to create a new table in a database. Tables are essential structures that store data in rows and columns, similar to a spreadsheet.


CREATE TABLE Employees (
    EmployeeID int,
    FirstName varchar(255),
    LastName varchar(255),
    BirthDate date,
    HireDate date
);

Explanation:

This example creates an "Employees" table with columns for EmployeeID, FirstName, LastName, BirthDate, and HireDate. The data types specify what kind of data each column can hold.

Defining Primary Keys

Overview:

Primary keys uniquely identify each row in a table. They ensure that no two rows have the same identifier.


CREATE TABLE Departments (
    DepartmentID int PRIMARY KEY,
    DepartmentName varchar(255) NOT NULL
);

Explanation:

In this "Departments" table, the DepartmentID is set as the primary key, ensuring each department has a unique identifier.

Using Foreign Keys

Overview:

Foreign keys are used to link two tables together. They enforce referential integrity between tables.


CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    OrderDate date,
    CustomerID int,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Explanation:

The "Orders" table includes a CustomerID column, which is a foreign key referencing the CustomerID in the "Customers" table.

Adding Constraints

Overview:

Constraints are rules applied to columns in a table. They can ensure data accuracy and integrity.


CREATE TABLE Products (
    ProductID int PRIMARY KEY,
    ProductName varchar(255) NOT NULL,
    Price decimal(10, 2) CHECK (Price > 0)
);

Explanation:

In this "Products" table, the CHECK constraint ensures that the Price is always greater than zero.

Defining Default Values

Overview:

Default values are automatically assigned to a column if no value is specified during record insertion.


CREATE TABLE Customers (
    CustomerID int PRIMARY KEY,
    CustomerName varchar(255) NOT NULL,
    Country varchar(255) DEFAULT 'USA'
);

Explanation:

In this "Customers" table, the Country column has a default value of 'USA' if no country is specified.

Creating Unique Constraints

Overview:

Unique constraints ensure that all values in a column are different.


CREATE TABLE Users (
    UserID int PRIMARY KEY,
    Username varchar(255) UNIQUE,
    Email varchar(255)
);

Explanation:

In this "Users" table, the Username column has a unique constraint, ensuring no two users can have the same username.

Handling Null Values

Overview:

Columns can be set to allow NULL values, which means they can contain no data.


CREATE TABLE Projects (
    ProjectID int PRIMARY KEY,
    ProjectName varchar(255) NOT NULL,
    Description text NULL
);

Explanation:

In this "Projects" table, the Description column can contain NULL values, meaning it's optional to provide a description.

Combining Multiple Constraints

Overview:

SQL allows combining multiple constraints in a single table definition to enforce complex rules.


CREATE TABLE Employees (
    EmployeeID int PRIMARY KEY,
    Email varchar(255) UNIQUE NOT NULL,
    Salary decimal(10, 2) CHECK (Salary > 0),
    DepartmentID int,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

Explanation:

In this "Employees" table, multiple constraints are applied: Email must be unique and not null, Salary must be greater than zero, and DepartmentID is a foreign key referencing the Departments table.